Docker restore PostgreSQL

Before you begin

In this tutorial, we’ll learn how to restore a PostgreSQL database. A Linux machine, Docker and a database dump file will be required to follow this tutorial. You can have a look at this post to easily create a dump file from your PostgreSQL database.

Restore backup to a local or remote PostgreSQL server

Command to restore a local or remote server database using a Docker Postgres image:

$ cat backup.sql | docker run -i postgres /usr/bin/psql \
  -h [POSTGRESQL_HOST] -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]

Command to restore a local or remote PostgreSQL database using Docker with compression (using gzip):

$ gunzip < backup.sql.gz | docker run -i postgres /usr/bin/psql \
  -h [POSTGRESQL_HOST] -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]

Same previous command but providing a PostgreSQL password as environment variable:

$ gunzip < backup.sql.gz | docker run -i \
  -e PGPASSWORD=[POSTGRESQL_PASSWORD] postgres /usr/bin/psql \
  -h [POSTGRESQL_HOST] -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]

Restore backup into a Docker PostgreSQL Server

Command to restore a database from plain SQL file (Postgres Dump):

$ cat backup.sql | docker exec -i [POSTGRESQL_CONTAINER] \
  /usr/bin/psql -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]

Command to restore multiple database from plain SQL file:

$ cat backup_all.sql | docker exec -i [POSTGRESQL_CONTAINER] \
  /usr/bin/psql -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER]

Command to restore a database from compressed gz file:

$ gunzip < backup.sql.gz | docker exec -i [POSTGRESQL_CONTAINER] \
  /usr/bin/psql -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]

Same previous command but providing a PostgreSQL password environment variable to existing container:

$ gunzip < backup.sql.gz | docker exec -i [POSTGRESQL_CONTAINER] \
  /bin/bash -c "export PGPASSWORD=[POSTGRESQL_PASSWORD] \
  && /usr/bin/psql -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]"


Recommended books to expand your PostgreSQL knowledge:

Finally, you should definitely take a look at these books to fuel your Docker knowledge: