How to Set Up PostgreSQL Backups
PostgreSQL backup — 3 levels: logical (pg_dump, portable), physical (pg_basebackup, fast restore), continuous WAL archiving (point-in-time recovery). For prod: daily pg_basebackup + WAL archiving to S3 + 30-day retention. Test restore monthly — untested backup = no backup.
Below: step-by-step, working examples, common pitfalls, FAQ.
Step-by-Step Setup
- Daily snapshot:
pg_dump -Fc -Z9 -f backup.dump dbname(compressed custom format) - Physical:
pg_basebackup -D /backup/base -Ft -z -P -U replicator - Enable WAL archiving in postgresql.conf:
archive_mode = on; archive_command = 'aws s3 cp %p s3://bucket/wal/%f' - Automate via cron:
0 3 * * * /usr/bin/pg_dump -Fc -f /backup/daily-\$(date +\%Y\%m\%d).dump dbname - Upload to S3: AWS CLI or s3cmd for retention + offsite
- Test restore monthly:
pg_restore -d new_db /backup/daily-20260401.dump - For PITR:
pg_basebackup+ apply WAL segments up to target timestamp
Working Examples
| Scenario | Config |
|---|---|
| Simple daily pg_dump script | #!/bin/bash
DATE=$(date +%Y%m%d)
pg_dump -Fc -Z9 -f /backup/mydb-$DATE.dump mydb
aws s3 cp /backup/mydb-$DATE.dump s3://mybucket/postgres/
find /backup -name 'mydb-*.dump' -mtime +7 -delete |
| postgresql.conf (archiving) | wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 300 |
| Restore from pg_dump | createdb newdb
pg_restore -d newdb -j 4 /backup/mydb-20260401.dump |
| pg_basebackup | pg_basebackup -h localhost -U replicator -D /backup/base_$(date +%Y%m%d) -Ft -z -P |
| PITR restore | # restore_command in recovery.conf
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-04-17 12:00:00' |
Common Pitfalls
- pg_dump for large DBs (100+ GB) — very slow. Use pg_basebackup
- No WAL archiving = data loss possible between snapshots
- Backup not tested — first raw fail at a critical moment
- Hardcoded password in script — security risk. Use .pgpass or IAM role
- S3 without versioning — race on concurrent uploads drops the last
Learn more
Frequently Asked Questions
pg_dump vs pg_basebackup?
pg_dump — logical, per-database, portable (restore on another PG version). pg_basebackup — physical, whole cluster, fast, but restore only on same PG version.
Incremental backups?
PG has no built-in incremental. Use pg_basebackup + WAL archiving (effectively incremental via WAL). Or pgBackRest.
Retention?
For compliance 30-90 days is typical. For DR 7-14 days is enough. S3 lifecycle rules auto-cleanup.
Backup verification?
<code>pg_restore --list</code> shows content. Real test: periodic full restore to staging + smoke tests.