PostgreSQL offers multiple backup approaches — physical vs logical, online vs offline, full vs incremental. Choosing the right strategy depends on your RTO, RPO, and database size.
Backup Types Overview
| Method | Tool | Size | Restore Speed | PITR? |
|---|---|---|---|---|
| Logical dump | pg_dump | Large | Slow | No |
| Physical base backup | pg_basebackup | Full | Fast | Yes (with WAL) |
| Incremental (WAL) | pgBackRest, WAL-G | Small deltas | Fast | Yes |
pg_dump — Logical Backups
bash
# Dump a single database (custom format — recommended)
pg_dump -Fc -Z 6 -d mydb -f mydb_$(date +%Y%m%d).dump
# Restore
pg_restore -d mydb_new -j 4 mydb_20250301.dump
# -j 4 uses 4 parallel restore workerspg_basebackup — Physical Backups with PITR
bash
pg_basebackup -h localhost -U replicator \
-D /backup/base/$(date +%Y%m%d) \
-Ft -z -P --checkpoint=fast --wal-method=streamWAL-G — Incremental Cloud Backups
WAL-G streams WAL to S3/GCS/Azure and supports point-in-time recovery:
bash
# Push full backup
WALG_S3_PREFIX=s3://my-bucket/pg-backup \
AWS_REGION=us-east-1 \
wal-g backup-push /var/lib/postgresql/15/main
# Archive WAL continuously (archive_command in postgresql.conf)
# archive_command = 'wal-g wal-push %p'
# PITR restore to a specific timestamp
wal-g backup-fetch /var/lib/postgresql/15/main LATEST
# Then set recovery_target_time in postgresql.confBackup Verification
Important: An untested backup is not a backup. Restore to a test instance at least monthly and verify data integrity with
pg_dump | wc -l and application-level smoke tests.
bash
# Quick sanity check after restore
psql -d mydb -c 'SELECT count(*) FROM critical_table;'
psql -d mydb -c 'SELECT max(created_at) FROM orders;'Key Takeaways
- Use WAL-G or pgBackRest for production — they support incremental backups and PITR
- pg_dump is fine for logical exports and schema-only backups
- Always test restores — a backup that cannot be restored is worthless
- Target RPO < 5 min: archive WAL continuously; RPO < 1 day: daily base backup is sufficient
JusDB Can Help
A robust backup strategy is foundational to database reliability. JusDB can design and implement your PostgreSQL backup and recovery plan.