Postgres Backup & Recovery Strategy

A production backup strategy is defined by two numbers: RPO (how much data can you afford to lose?) and RTO (how long until you're back up?). For typical OLTP: RPO 1 minute, RTO 1 hour.

Achieve this with PITR — point-in-time recovery

• Base backup (daily or weekly) plus continuous WAL archiving
• Every transaction log segment shipped to S3
• Restore to any second since base backup

Replicas are not backups

• Accidental DELETE replicates instantly
• Schema corruption replicates
• Bad migration replicates
• You need point-in-time protection independent of replicas

RPO and RTO drive strategy

• Critical OLTP (payments, accounts): RPO 1 min, RTO 1 hr
• Standard OLTP: RPO 5 min, RTO 4 hrs
• Analytical/warehouse: RPO 1 day, RTO 1 to 2 days
• Every minute/hour tightened costs money; be deliberate

Backup types

Base backup (physical)

• pg_basebackup, pgBackRest, WAL-G, or storage snapshot
• Full or incremental
• Basis for PITR
• Daily full or weekly full + daily incremental

WAL archive

• Continuous stream of transaction logs
• Every .wal segment (16MB) archived to S3
• Enables PITR to any second
• Monitor pg_stat_archiver.last_archived_time; lag means data loss risk

Logical dump (pg_dump)

• SQL or custom format
• Portable across PG versions; good for migrations, selective restore
• Slower to restore; don't use as primary DR
• pg_dump -Fc custom format is usual

Managed snapshot

• RDS/Aurora automated snapshots
• PITR within retention window (up to 35 days)
• Aurora: continuous backup; restore to any second; fast clones

Physical replica

• Not a backup; part of HA
• Does NOT protect against logical errors
• Delayed replica (1-hour delay) gives window for logical-error recovery

Cross-region DR replica

• Read replica in different region; async
• Aurora Global Database: sub-second replication, fast promotion
• RPO: replication lag (seconds); RTO: promotion time (minutes)

On AWS

• RDS/Aurora give PITR automatically (35-day window)
• RDS: daily snapshot + 5-min WAL archive
• Aurora: continuous distributed backup; fast clones; Global Database for multi-region
• Still export pg_dump to S3 for long retention and cross-account

Self-hosted

• pgBackRest (modern production choice): reliable, parallelized, differential/incremental, S3/GCS/Azure, compression, encryption
• WAL-G (Golang-based): simpler config, fast, cross-DB
• Barman (older): Python, good docs

Typical strategy for mid-scale production

• Daily full base backup to S3 with 30-day retention
• Continuous WAL archive to S3
• Nightly pg_dump to S3 with 90-day retention (compliance/export)
• Streaming replica in same region (HA; automatic failover with Patroni)
• Cross-region replica (DR; manual promotion)
• Delayed replica (4-hour) for logical-error recovery
• Test restores quarterly
• Monitor: backup success, WAL lag, replica lag, disk growth

Restore drills — the step everyone skips

• Drill 1: cold restore to fresh instance; run integrity checks
• Drill 2: PITR to specific timestamp
• Drill 3: DR region failover; measure RTO end-to-end
• Drill 4: logical-error recovery (simulated bad DELETE)
• Runbook with exact commands, credentials, authority, comms, rollback, validation

Common restore gotchas

• Extensions missing on restore target (postgis, pgcrypto, pg_stat_statements)
• Roles missing; pg_dumpall -g for globals
• Large objects need -b flag
• Sequences out of sync after selective table restore
• Foreign keys may fail if restored out of order; pg_restore -j handles parallel
• Connection limits on smaller recovery instance

Monitoring

• pg_stat_archiver for last archive time
• pg_stat_replication for replica lag
• postgres_exporter to Prometheus
• Alert on backup failure, archive lag over 5 min, backup size anomaly

Trade-offs and real-world failures

Replica != backup (biggest mistake)

RDS automated snapshots capped at 35 days

• Copy to manual snapshots or pg_dump for long retention
• Cross-account requires explicit sharing

Aurora backtrack is not indefinite PITR

• Bounded window (up to 72h); good for quick undo

Backup storage cost

• Daily full + WAL for 1TB DB equals about 5-15TB over 30-day retention
• Glacier Deep Archive saves 5-10x for old backups

Restore throughput disk/network-bound

• 1TB from S3 can take 1-4 hours
• This is RTO floor; size recovery instance accordingly

Backup poisoning

• Silent fail for weeks means no backup
• Monitor completion, size anomalies, archive lag, last successful age

Cross-account / cross-region DR is essential

• Same-account backups useless if attacker/bug/AWS takes account

Ransomware

• S3 Object Lock (WORM) and MFA Delete protect
• Immutable backups essential for compliance (SOC 2, HIPAA)

Compliance retention

• 7 years for financial often; automate lifecycle to Glacier

GDPR

• Deletion from backups when backup ages out, or per-user encryption keys

Managed doesn't mean tested

• Drill restore from managed snapshots
• Automatic doesn't mean tested

The strategic answer

• Define RPO and RTO first
• PITR via base backup + continuous WAL archive
• Multiple backup types (physical, logical, replica, cross-region)
• Test restores quarterly
• Monitor backup freshness continuously
• Cross-account and cross-region for real DR
• Immutable backups for compliance and ransomware
• Aurora model simplifies ops; self-hosted with pgBackRest cheaper at scale
• A backup you can't restore is worth nothing