Loading PostgreSQL Database Backup Into Docker/Initial Docker Data

Question

I am migrating an application into Docker. One of the issues that I am bumping into is what is the correct way to load the initial data into PostgreSQL running in Docker? My typical method of restoring a database backup file are not working. I have tried the following ways:

gunzip -c mydbbackup.sql.gz | psql -h <docker_host> -p <docker_port> -U <dbuser> -d <db> -W

That does not work, because PostgreSQL is prompting for a password, and I cannot enter a password because it is reading data from STDOUT. I cannot use the $PGPASSWORD environment variable, because the any environment variable I set in my host is not set in my container.

I also tried a similar command above, except using the -f flag, and specify the path to a sql backup file. This does not work because my file is not on my container. I could copy the file to my container with the ADD statement in my Dockerfile, but this does not seem right.

So, I ask the community. What is the preferred method on loading PostgreSQL database backups into Docker containers?

possible duplicate of [How to deal with persistent storage (e.g. databases) in docker](http://stackoverflow.com/questions/18496940/how-to-deal-with-persistent-storage-e-g-databases-in-docker) — Mark O'Connor, Jul 28 '14 at 19:42
See also https://docs.docker.com/userguide/dockervolumes/ AND http://docs.docker.com/examples/postgresql_service/ — Mark O'Connor, Jul 28 '14 at 19:43
I have read the articles that you referenced. The scenerio I am asking about is: I have a PostgreSQL database in a container and a separate data container. Both containers are running, the database is emtpy. I have a backup file located on my host. How do I go about restoring the file into the empty database? My question above described what I have tried and the problems I have run into. I could bake the backup file into the data container, but that does not seem right. — Sagebrush GIS, Jul 28 '14 at 20:09
Ahhh wrong duplicate so: http://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password and another http://stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively — Mark O'Connor, Jul 28 '14 at 22:18
The links refer to using a $PGPASSWORD environment variable or a ./pgpass file. Is that a secure solution to bake either an environment variable or a text file into a container with a plain text password in it? — Sagebrush GIS, Jul 29 '14 at 01:55

score 3 · Accepted Answer · answered Jul 29 '14 at 10:22

I cannot use the $PGPASSWORD environment variable, because the any environment variable I set in my host is not set in my container.

I don't use docker, but your container looks like a remote host in the command shown, with psql running locally. So PGPASSWORD never has to to be set on the remote host, only locally.

If the problems boils down to adding a password to this command:

gunzip -c mydbbackup.sql.gz |
  psql -h <docker_host> -p <docker_port> -U <dbuser> -d <db> -W

you may submit it using several methods (in all cases, don't use the -W option to psql)

hardcoded in the invocation:

 gunzip -c mydbbackup.sql.gz |
  PGPASSWORD=something psql -h <docker_host> -p <docker_port> -U <dbuser> -d <db>

typed on the keyboard

 echo -n "Enter password:"
 read -s PGPASSWORD
 export PGPASSWORD
 gunzip -c mydbbackup.sql.gz |
   psql -h <docker_host> -p <docker_port> -U <dbuser> -d <db>

Note about the -W or --password option to psql.

The point of this option is to ask for a password to be typed first thing, even if the context makes it unnecessary.

It's frequently misunderstood as the equivalent of the -poption of mysql. This is a mistake: while -p is required on password-protected connections, -W is never required and actually goes in the way when scripting.

       -W, --password
           Force psql to prompt for a password before connecting to a
           database.

           This option is never essential, since psql will automatically
           prompt for a password if the server demands password
           authentication. However, psql will waste a connection attempt
           finding out that the server wants a password. In some cases it is
           worth typing -W to avoid the extra connection attempt.

Loading PostgreSQL Database Backup Into Docker/Initial Docker Data

1 Answers1