March 10, 2020

How to dump and restore PostgreSQL databases

Options for dumping data:

# Binary mode
pg_dump -Fc -U $POSTGRES_USER $POSTGRES_DB > dump-`date '+%Y_%m_%d__%H_%M_%S'`.psqlc

# SQL mode
pg_dump -U $POSTGRES_USER $POSTGRES_DB > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

# Docker and SQL mode
docker exec -it -u postgres <container_name> sh -c 'pg_dump -U $POSTGRES_USER $POSTGRES_DB' > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

# Kubernetes and SQL mode (vulnerable to networking failures)
kubectl -n <namespace> exec deploy/postgres -- sh -c 'pg_dump -U $POSTGRES_USER $POSTGRES_DB' > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

Options for restoring data:

# Binary mode
pg_restore -O -U $POSTGRES_USER -c -x -n public -d $POSTGRES_DB dump.psqlc

# SQL mode
psql -d $POSTGRES_DB -a -f /backups/dump.sql

If you’re using Docker, you can also place dump files on /docker-entrypoint-initdb.d and those will be imported on the first run. Accepted files types are *.sql, *.sql.gz, and *.sh.

<laury.dev/>