Dump and Restore

Handy commands for dumping and restoring Postgres databases.

INFO

Most of these commands are run using Docker as then we don't need to have Postgres installed locally.

If a different version of Postgres is needed, just change the version number in the Docker image tag.

Setup

Create a pgpass file in the current directory with the following content:

<server>:<port>:<database>:<username>:<password>

TIP

Use * as a wildcard for any of the fields except for the password.

Dumping a Database

docker run --network host --rm -v .:/tmp -v ./pgpass:/root/.pgpass -e PGPASSFILE=/root/.pgpass postgres:16 bash -c "chmod 600 /root/.pgpass && pg_dump -h <server> -p <port> -U <username> <database> -f /tmp/$(date +%Y%m%d_%H%M%S)_<database>_dump.sql -c --if-exists"

Dumping a Cluster

docker run --network host --rm -v .:/tmp -v ./pgpass:/root/.pgpass -e PGPASSFILE=/root/.pgpass postgres:16 bash -c "chmod 600 /root/.pgpass && pg_dumpall -h <server> -p <port> -U <username> -f /tmp/$(date +%Y%m%d_%H%M%S)_<server>_cluster_dump.sql -c --if-exists"

Restoring from a SQL Dump

docker run --network host --rm -v .:/tmp -v ./pgpass:/root/.pgpass -e PGPASSFILE=/root/.pgpass postgres:16 bash -c "chmod 600 /root/.pgpass && psql -h <server> -p <port> -U <username> <database> -f /tmp/dump.sql -X"