How to Dump and Restore PostgreSQL Database with Docker - Full Example

Going into production, you probably have a checklist of what needs to be done. One of the main items should be about your data, in our case PostgreSQL database.

If working locally without Docker or anything else, a dump and restore operation is pretty straightforward. In a real-life project, it might get harder to understand what and how to do so.

What you'll see here:

• How to dump a database inside a Docker container (locally with docker-compose)
• How to restore a PostgreSQL database

We assume you have already set up docker-compose with Postgres installed and a database, if not you can check my article for a NestJS starter with a docker-compose file prepared and ready for use.

Create our Dump file

Using locally, without Docker and the best-case scenario, we can write, as stated in the docs and it would be enough.

pg_dump dbname > outfile 

In reality, we might need to execute it in a docker environment, and add flags and different commands.

We need:

• Docker container name, that has Postgres installation.
• Root Postgres username
• Database name we're dumping

If we don't know the docker container name, we can use:

docker ps -a 

This will give us a list of our containers. Find the one with the image postgres:15 or similar. Copy the name of it as we're using it in the next command. If you are using the docker compose from the starter, the user you need to use is "postgres".

docker exec <docker-container-name> pg_dump -U <username> -F t <database-name> > dump_name 

This would generate a file at your terminal location, and if you open it into VSCode, it will look ugly, not understandable, and full of errors. Similar to below.

The first step is done, our Dump is already created for our database.

Restore our Dump file

To restore it we need access to the file inside the Docker container. One way is to copy it inside it with the following command, where the container name is the one you've found already before.

docker cp dump_name.tar <container-name>:/

You're looking for an output similar to:

Successfully copied 652kB to <container-name>:/

Connect to our docker container to be able to run the pg_restore command.

docker exec -it <container-name> sh

If the database is already created, run the next command.

pg_restore -U <username> -C -d <database-name> dump_name.tar

If the database is NOT created, run the following command using the initial Postgres database created.

pg_restore -U postgres -C -d postgres dump_name.tar

There are a a lot of different options, that could be used while restoring our database. In the PostgreSQL documentation for pg_restore, it's written, what each command does.

I'd suggest you automate the process of backing up your data. It's easily done with cron jobs and scripts. It'll be covered in future articles!

If something didn't get right by you, hit me up on Twitter/X or LinkedIn. At both places a follow is highly appreciated!

My newsletter will get you notified about any new articles coming out. You can subscribe below!