Backup Best Practices
Automate restores, not just dumps - untested backups are fiction. These rules keep PostgreSQL backups recoverable under real incident pressure.
How to Use This List
- Apply when designing backup jobs and retention.
- Use monthly verify run as a pass/fail checklist.
- Pair with compliance retention requirements.
- Review after any failed or slow restore drill.
A - Strategy
- Use logical and physical backups together. Logical for objects; physical for PITR and full cluster.
- Follow 3-2-1: three copies, two media, one off-site. Independent of replication.
- Never treat standby replica as backup. Replication replays deletes and corruption.
- Dump globals (
pg_dumpall -g) on schedule. Roles and tablespaces are not in per-DB dump alone. - Encrypt backups at rest and in transit. S3 SSE-KMS, TLS, or gpg for files.
B - Dump Operations
- Prefer
-Fcor-Fdover plain SQL for production size. Enables parallel restore. - Run large
pg_dumpoff-peak or from standby. Reduce primary load. - Tier-0 tables get dedicated object-level dumps. Faster than full cluster for oops recovery.
- Monitor dump job duration and output size. Sudden shrink means failure or empty schema.
- Backup role uses least privilege with read access to all needed data. Document RLS bypass policy.
C - Physical and WAL
- Enable
archive_modeor pgBackRest for PITR-capable backups. Replication alone is insufficient. - Monitor
pg_stat_archiver.failed_count. Archive failures silently widen RPO. - Retain WAL per compliance window plus safety margin. Align with legal holds.
- Test physical restore quarterly. Logical-only verify misses WAL gaps.
- Coordinate Patroni pause only when backup tool requires it. Document in runbook.
D - Verification
- Automate monthly restore to scratch PostgreSQL 18.4 instance. Not just
ls -lon files. - Log
RESTORE_RTO_SECONDSevery verify run. Prove leadership RTO claims. - Compare row counts or checksums after restore. Catch truncated dumps.
- Alert on verify failure as severity-1 data risk. Treat as production incident.
- Pull verify copy from off-site replica. Local disk backup can lie.
E - Operations and Access
- Restrict backup bucket IAM to backup and verify roles only. No app servers.
- Version retention: daily 7, weekly 4, monthly 12 minimum for tier-0. Tune to RPO tier.
- Document who can trigger restore and to which environments. Prevent prod overwrite.
- Runbook links backup ID to restore steps. On-call does not grep wiki during sev.
- Post-incident: if backup used, re-baseline and verify next copy. Confirm chain intact.
FAQs
Minimum backup for small prod?
Daily pg_dump -Fc, weekly verify restore, archive_mode if PITR required.
pg_dump vs pgBackRest only?
pgBackRest for large PITR; keep periodic pg_dump for table-level recovery speed.
Backup during failover?
Pause non-critical dumps during Patroni failover; physical tools often tolerate leader change if configured for standby backup.
Cloud RDS backups?
Enable automated backups and test restore to new instance quarterly; still export logical for object restore if needed.
Legal hold interaction?
Extend retention for held datasets; see Data Retention & Legal Holds.
Related
- Backup Basics - logical vs physical
- Backup Verification - automate prove
- PITR Basics - WAL chain
- DR Best Practices - off-site posture
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.