Before you begin

In this tutorial will 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 backup to a local or remote PostgreSQL server database using Docker:

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

Command to backup 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 command below but providing 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 PostgreSQL Server Docker container

Command to restore a database from plain SQL file:

$ 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 command below but setting 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]"