iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

pg_dump / Restore

By · Reviewed & last updated · Part of PostgreSQL Tutorial

Postgres ships two complementary backup tools. pg_dump produces a logical backup (SQL or custom-format archive) — portable across versions, scoped to a single database. pg_basebackup makes a physical, file-level base backup of the whole cluster — the foundation for point-in-time recovery (PITR) when paired with archived WAL.

pg_dump, pg_basebackup, and PITR with WAL archiving

EXAMPLE
# 1) Logical backup of one database (custom format = parallelisable, restorable selectively)
pg_dump -Fc -j 4 -Z 6 \
  -h prod.db.internal -U postgres -d shop \
  -f shop-$(date +%F).dump

# Restore with parallel jobs
createdb -h staging shop_restore
pg_restore -h staging -U postgres -d shop_restore -j 4 shop-2026-06-11.dump

# 2) Plain SQL backup (text, easy to grep / inspect / load anywhere)
pg_dump -Fp -h prod -U postgres -d shop > shop.sql
gzip shop.sql

# 3) Tables-only or schema-only when you need just one or the other
pg_dump --schema-only --no-owner --no-acl -Fp -d shop > schema.sql
pg_dump --data-only -t orders -t order_items -Fc -d shop -f orders.dump

# 4) Globals (roles, tablespaces) — pg_dump does NOT include these
pg_dumpall --globals-only -h prod -U postgres > globals.sql

# 5) Physical backup of the whole cluster (use this for PITR)
pg_basebackup -h prod.db.internal -U replication \
  -D /backups/base-$(date +%F) \
  -Ft -z -X stream -P -R                  # tar + gzip + include WAL inline + write recovery.conf

# 6) WAL archiving for point-in-time recovery
# postgresql.conf
# wal_level = replica
# archive_mode = on
# archive_command = 'aws s3 cp %p s3://wal/%f --quiet'
# archive_timeout = 300

# 7) Restore to a specific moment (PITR)
# postgresql.auto.conf
# restore_command = 'aws s3 cp s3://wal/%f %p'
# recovery_target_time = '2026-06-10 22:45:00+10'
# recovery_target_action = 'promote'

# 8) Verify backups by restoring them on a regular cadence
#    A backup you have never restored is a backup that does not exist.
createdb -h staging restore_test
pg_restore -h staging -U postgres -d restore_test -j 4 shop-2026-06-11.dump
psql -h staging -d restore_test -c 'SELECT pg_database_size(current_database()), count(*) FROM orders;'

# 9) Encryption at rest before the bytes leave the host
gpg --symmetric --cipher-algo AES256 shop-2026-06-11.dump
aws s3 cp shop-2026-06-11.dump.gpg s3://backups/db/

Why it matters

Custom-format dumps (-Fc) plus parallel jobs (-j) are dramatically faster to take and restore than plain SQL. They also let you selectively restore one table without replaying the whole dump, which is the difference between a 5-minute incident recovery and a 5-hour one.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
# Backup
pg_dump -Fc myapp > myapp.dump
# Restore
pg_restore -d myapp myapp.dump
# SQL backup
pg_dump myapp > myapp.sql
Try it Yourself »

Discussion

Loading…