Export and import PostgreSQL database with `pg_dump`

With pg_dump, exporting and importing a PostgreSQL database is simple. It lets you move data between different environments, so development and testing are faster.

The Challenge

In my homelab, I manage several testing and development environments across different virtual machines. Moving the same PostgreSQL database between them used to be a hassle. I needed a way to export the database quickly and restore it reliably on another VM or container.

Export and Import

In the examples below, I export a database from PostgreSQL and import it into a Docker container. The same approach works without Docker.

Export

First, I create a directory for your dumps and switch into it:

mkdir -p ~/pg_dumps
cd ~/pg_dumps

Then export the database you want. This streams the dump through `gzip` and writes the compressed file to disk:

docker exec -t <container-id> pg_dump -U <db-user> -d <db-name> | gzip > <db-name>-export.sql.gz

If everything went well, the export file will be in the current directory.

Import

To import, drop the old database (if it already exists), then recreate it:

docker exec -it <container-id> psql -U <db-user> -d postgres -c "DROP DATABASE IF EXISTS <db-name>;"
docker exec -it <container-id> psql -U <db-user> -d postgres -c "CREATE DATABASE <db-name>;"

Finally, restore the dump:

gunzip -c ~/pg_dumps/<db-name>-export.sql.gz | docker exec -i <container-id> psql -U <db-user> -d <db-name>