0

I have been working on making a postgres docker image that uses trust authentication by default for local development. Im not completely new to docker but by no means am an expert.

I found Installing PostgreSQL within a docker container for allowing local unix socket connections and How to persist data in a dockerized postgres database using volumes which explains how to persist data in a volume, but I can't put the 2 together.

WHen I run $ docker run -p 5432:5432 -v pgdata:/var/run/postgresql pgtrusted I expect the named volume to persist and be accessible locally, but opening a new terminal and running psql results in the error message documented in the first question

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The error goes away when I designate the volume via $ docker run -p 5432:5432 -v /var/run/postgresql:/var/run/postgresql pgtrusted Which (as I understand it) anonomizes the volume. This creates the problem of not persisting any data (I created a user and a table, then stopped and restarted the container and the data was gone)

Any thoughts?

Vincent Buscarello
  • 395
  • 2
  • 7
  • 19
  • Named volume manage by docker file system so it can't access by host system directly and host volume mange by host system so it can be accessed by a host system. – Haresh Chhelana Jun 20 '18 at 06:01

1 Answers1

1

I use the standard postgresql docker images constantly and in all cases I use network sockets to connect to the running image.

Basically you have two options to persist your data in a docker container.

  1. mount a volume container to your database
  2. mount a host path to your container

In development scenarios I prefer the second solution because it's more transparent.

The official postgresql image site on docker hub describes how to implement that. https://hub.docker.com/_/postgres/

When you start the container pass a PGDATA env variable that points to the container internal path where you want place your database files. An example call looks like this.

docker run -p 5432:5432 -e PGDATA=/opt/postgresql -v \ 
  /FULL_PATH/ON_HOST/pgdata:/opt/postgresql pgtrusted

The chosen host directory needs to be empty and after your container is gone you still have the database file into it and can mount them to the next container.

If you use the official postgresql images it's also possible to use the internal psql console to access your data base. The following code grabs the container ID and execute a psql in it.

CONT_ID=`docker ps | grep "${CONTAINER_NAME}" | awk '{print $1}'`

if [ -z "$CONT_ID" ]; then
    echo "can't find running psql container, exit"
    exit 1
fi

docker exec -it "$CONT_ID" psql -U db_user -h localhost desired_db
OkieOth
  • 3,604
  • 1
  • 19
  • 29
  • awesome, trying that now. Can you explain the -v syntax? we are saying `mount volume here:volume to mount from container` correct? so the first path should exist on host and the second path exist in the container's fs? – Vincent Buscarello Jun 20 '18 at 21:44
  • Got it working but I still have to spec -h localhost. I dont know of any problems that would cause, is a local unix socket connection better practice for any reason? – Vincent Buscarello Jun 20 '18 at 22:00
  • @VincentBuscarello '-v' is the parameter to tell docker to mount local path as volume – OkieOth Jun 21 '18 at 06:10
  • 1
    @VincentBuscarello If you use a postgres container you can also use the container contained psql I have for that case a special shell script to open psql. See edited answer – OkieOth Jun 21 '18 at 06:12