<laury.dev/>

#Snippets

Topics
>
server-side (27)
>
docker (14)
>
kubernetes (14)
>
development (8)
>
homelab (8)
>
raspberry-pi (4)
>
golang (3)
>
survival-guide (3)
>
macos (2)
>
mariadb (2)
>
mongodb (2)
>
postgresql (2)
March 10, 2020

#How to dump and restore PostgreSQL databases

postgresqlserver-sidedockerkubernetes

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.

Section break