2

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?

Improve this question
5
  • possible duplicate of How to deal with persistent storage (e.g. databases) in docker Commented Jul 28, 2014 at 19:42
  • See also docs.docker.com/userguide/dockervolumes AND docs.docker.com/examples/postgresql_service Commented Jul 28, 2014 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. Commented Jul 28, 2014 at 20:09
  • Ahhh wrong duplicate so: stackoverflow.com/questions/6523019/… and another stackoverflow.com/questions/6405127/… Commented Jul 28, 2014 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? Commented Jul 29, 2014 at 1:55

1 Answer 1

Reset to default
3

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.