Backup Best Practices

Q: Minimum backup for small prod?

Daily pg_dump -Fc, weekly verify restore, archive_mode if PITR required.

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 -Fc or -Fd over plain SQL for production size. Enables parallel restore.
Run large pg_dump off-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_mode or 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 -l on files.
Log RESTORE_RTO_SECONDS every 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.

Backup Basics - logical vs physical
Backup Verification - automate prove
PITR Basics - WAL chain
DR Best Practices - off-site posture

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Tutorials & Other Resources (Official List) - Curated list of recommended tutorials and external resources.
PostgreSQL Mailing Lists / Community - Official mailing lists for help, development, and announcements.
Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
Just Use Postgres! - Denis Magda.

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+.

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 -Fc or -Fd over plain SQL for production size. Enables parallel restore.
Run large pg_dump off-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_mode or 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 -l on files.
Log RESTORE_RTO_SECONDS every 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.

Backup Basics - logical vs physical
Backup Verification - automate prove
PITR Basics - WAL chain
DR Best Practices - off-site posture

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Tutorials & Other Resources (Official List) - Curated list of recommended tutorials and external resources.
PostgreSQL Mailing Lists / Community - Official mailing lists for help, development, and announcements.
Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.
Just Use Postgres! - Denis Magda.

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+.

Backup Best Practices

Search Documentation

Backup Best Practices