Docker Postgres Backup/Restore Guide (with examples)

Backing up a Postgres database running in Docker uses the same pg_dump and pg_restore commands as anywhere else. The difference is that they live inside the container, so every command has to be routed through docker exec, and the dump file lands in a place the host cannot always see.

This guide covers the Docker Postgres backup and restore commands for both directions, with the two mistakes that account for most failed restores: copying the dump to the wrong volume, and using pg_restore on a file that needs psql.

Before you begin

Two container facts drive everything below.

First, you run commands inside a container from the host with docker exec:

docker exec <container_name> <your_command>

Second, paths inside that command are resolved inside the container, not on your host. So a pg_restore reading /backups/dump.sql is reading the container's /backups, which only exists if a volume is mounted there. That is why the restore section below spends time on docker inspect and docker cp: the file has to physically reach the container before any restore command can see it.

Redirection is the exception. In docker exec ... pg_dump ... > postgres-backup.sql, the > is interpreted by your shell, so the dump lands on the host. That asymmetry is worth holding onto: dumps come out to the host, restores need the file copied in.

Back up a Docker PostgreSQL database

As long as you are on a Linux machine with Docker installed, this is the procedure to back up a database.

The Docker backup command for a local or remote PostgreSQL database is:

docker exec -i postgres /usr/bin/pg_dump \
 -U <postgresql_user> <postgresql_database> > postgres-backup.sql

Note: you can set the database host by adding -h <postgresql_host> to the dump command.

Back up all Docker PostgreSQL databases

You can use pg_dumpall to back up all Docker Postgres databases at once, here is the command:

docker exec -i postgres /usr/bin/pg_dumpall \
 -U <postgresql_user> > postgres-backup.sql

You can find more examples of pg_dumpall.

Back up and compress a Docker PostgreSQL database with gzip

The command shifts when you need to use compression. The command will backup a remote or local PostgreSQL database. In Docker with gzip compression, the command is:

docker exec -i postgres /usr/bin/pg_dump \
 -U <postgresql_user> <postgresql_database> | gzip -9 > postgres-backup.sql.gz

Example when using a PostgreSQL password

You can include the PostgreSQL password as an environment variable. The command then looks like this:

docker exec -i -e PGPASSWORD=<postgresql_password> postgres /usr/bin/pg_dump \
 -U <postgresql_user> <postgresql_database> | gzip -9 > postgres-backup.sql.gz

Back up PostgreSQL inside docker container

You can also back up PostgreSQL databases that are in containers. To do this, you need to create a compressed file with gzip and docker.

The command looks like this:

docker exec <postgresql_container> /bin/bash \
 -c "/usr/bin/pg_dump -U <postgresql_user> <postgresql_database>" \
 | gzip -9 > postgres-backup.sql.gz

Perform the same command while using the PostgreSQL password environment variable. The command looks like this:

docker exec <postgresql_container> /bin/bash \
 -c "export PGPASSWORD=<postgresql_password> \
     && /usr/bin/pg_dump -U <postgresql_user> <postgresql_database>" \
 | gzip -9 > postgres-backup.sql.gz

How to restore data using pg_restore (detailed)

If you just need to skip all the details, you can directly go to Postgres Restore Database Command on Docker. Otherwise, continue and you will understand some key aspects like:

• How to find the name of the container
• How to determine how much room is free for the restore

Find the name and id of the Docker container hosting along with the Postgres instance. You can do this by running the docker ps command to locate this information.

The command and retrieved info will look something like this:

docker ps

Example output:

CONTAINER ID   …             NAMES
abc985ddffcf   …             my_postgres_1

Then, with the info retrieved, the next step is to find the volumes available in the Docker container. This information is critical to determining how much room is free to use for the restore.

You will need to use the docker inspect command. The basic command looks like this:

docker inspect -f '{{ json .Mounts }}' <container_id> | python3 -m json.tool

Using that command, you will be looking at the volume paths under the key destination.

docker inspect -f '{{ json .Mounts }}' abc985ddffcf | python3 -m json.tool

Example output:

[
   {
       "Type": "volume",
       "Name": "my_postgres_backup_local",
       "Source": "/var/lib/docker/volumes/my_postgres_backup_local/_data",
       "Destination": "/backups",
       "Driver": "local",
       "Mode": "rw",
       "RW": true,
       "Propagation": ""
   },
   {
       "Type": "volume",
       "Name": "my_postgres_data_local",
       "Source": "/var/lib/docker/volumes/my_postgres_data_local/_data",
       "Destination": "/var/lib/postgresql/data",
       "Driver": "local",
       "Mode": "rw",
       "RW": true,
       "Propagation": ""
   }
]

The volume paths here are /backups and /var/lib/postgresql/data. When you have the volume, you will then copy your dump into one of those paths. Run the docker cp command:

docker cp </path/to/dump/in/host> <container_name>:<path_to_volume>

By picking the /backups volume for the copy location, the command then becomes:

docker cp postgres-backup.sql my_postgres_1:/backups

The database owner will need to run the restore command using docker exec. This assumes that the Postgres database already exists. If it doesn't, you will have to create one before you can perform the restore.

Which restore command you need depends on how the dump was taken, and this is where most Docker restores go wrong:

• A plain .sql file, which is what the pg_dump commands earlier in this guide produce, is restored with psql.
• A custom, directory or tar archive, produced with pg_dump -F c, is restored with pg_restore.

Pointing pg_restore at a plain .sql file fails with input file appears to be a text format dump. Please use psql. The file is fine; the command is wrong.

So the restore command inside the container is either:

psql -U <database_owner> -d <database_name> -f <path_to_dump>

or, for an archive-format dump:

pg_restore -U <database_owner> -d <database_name> <path_to_dump>

These are the most generic commands that are available even when you don’t know the database owner. If you already know who the owner is, then you can move forward.

Find out the owner of a Postgres database on Docker

You can find the owner by retrieving a list of the databases along with their owners. This uses a psql -U postgres -l command.

You will use this command at the same time as the docker exec command.

The final command will give you a result that looks like this:

docker exec my_postgres_1 psql -U postgres -l

Example output:

List of databases

Name             | Owner
-----------------+----------
some_database    | postgres

Postgres Restore Database Command on Docker

You will be able to run the restore after retrieving the information.

Because the backup commands earlier in this guide produced a plain postgres-backup.sql, the restore goes through psql:

docker exec my_postgres_1 psql -U postgres -d some_database -f /backups/postgres-backup.sql

If you took the dump in custom format instead (pg_dump -F c), use pg_restore against the archive:

docker exec my_postgres_1 pg_restore -U postgres -d some_database /backups/postgres-backup.dump

Conclusion

You now have both directions covered: a dump that lands on the host, and a restore that copies the file back into a mounted volume and runs the command matching the dump's format.

Test the restore into a throwaway container before you need it. docker run --rm -d --name pg_test postgres:16 gives you a scratch database, and a dump that restores cleanly there is a dump you can trust.

Two things are worth doing next. Restoring outside a container follows different rules once you are dealing with ownership and existing objects, and how to restore a PostgreSQL backup covers those. And a dump sitting on the Docker host is still one machine away from being lost, so the step most people skip is sending PostgreSQL backups to S3.

A container makes one problem worse rather than better: a cron entry running docker exec fails silently the moment the container name changes or the container is not running, and nothing tells you. Beyond pg_dump and cron covers that blind spot and what closes it.

If you would rather not wire that up per container, SimpleBackups runs scheduled PostgreSQL backups against containerised databases and ships them to storage you control.

FAQ

---

This article is part of The complete guide to PostgreSQL backup, an honest, practical reference from the team that backs up PostgreSQL every day.