Postgres backup restore drills
The backup job shows green for four hundred days. The ransomware event arrives. Nobody knows how long a full restore takes, whether WAL archives are intact, or which connection string the application should use. Postgres restore drills turn backup configuration into proven recovery — timed, documented, repeated.
Monitoring reported backup success every night at 03:00. The incident was Thursday 14:00: primary disk corruption on the production Postgres volume. The runbook said "restore from latest snapshot." The latest snapshot restored in fifty-two minutes — but WAL segments between snapshot and corruption were missing from object storage. Data loss window: nine hours. The backup was not wrong; it was never validated end-to-end. Postgres backup restore drills exist because backup configuration and backup recoverability are different properties — provable only by restoring into an isolated environment, starting the database, running application smoke tests, and measuring elapsed time against documented RPO and RTO targets.
Recovery Point Objective (RPO) defines acceptable data loss — how far back recovery must reach. Recovery Time Objective (RTO) defines acceptable downtime — how fast service must return. Teams quote both in slide decks. Drills are where quotes meet reality.
Backup types and what each drill must prove
Postgres durability stacks multiple mechanisms. Each needs its own drill variant.
| Mechanism | What it protects | Drill proves |
|---|---|---|
Logical dump (pg_dump) | Portable schema + data | Dump restores cleanly; extensions handled |
| Physical base backup | Full cluster files at point in time | Backup starts; Postgres promotes |
| WAL archiving / PITR | Changes after base backup | Replay to target timestamp |
| Replica / standby | Near-live copy | Promotion and cutover |
| Volume snapshot | Block-level instant image | Crash consistency + WAL chain |
| Managed service PITR | Provider-native timeline restore | Restore to new instance; connect app |
A team using only logical dumps cannot drill PITR — and will not survive "restore to ten minutes before accidental DELETE" requests. Match drill scope to production backup strategy.
Logical dumps excel at selective restore and cross-version migration. They are slow on large databases and not a substitute for continuous archiving when RPO is minutes.
Physical backup plus WAL archiving is the standard for production RPO measured in minutes. Drill flow:
1. Restore latest base backup to isolated host
2. Configure recovery.target.time or target LSN
3. Replay WAL from archive until target
4. Promote (pg_wal_replay_resume or end of recovery)
5. Verify data + run application checks
A backup strategy documented only in infrastructure code is untested until a human executes restore steps with a stopwatch.
Designing a restore drill program
Drills are operational exercises with pass/fail criteria — not optional disaster tourism.
Frequency tiers.
| Drill type | Cadence | Scope |
|---|---|---|
| Automated restore smoke | Weekly | Latest backup → SELECT 1, row count checksum |
| Full application restore | Quarterly | Restore + app smoke + read/write test |
| PITR to arbitrary timestamp | Quarterly | Restore to T-15min; verify known row state |
| Full region / provider failover | Annually | New instance, DNS cutover, rollback path |
| Table-level logical restore | Semi-annual | Single table from dump into staging |
Isolated target environment. Never drill on production hardware sharing storage with primary. Use dedicated restore host, ephemeral instance, or namespace with distinct credentials. Drills that risk production connection string mix-ups get cancelled — then never rescheduled.
Defined success criteria. Document before drill: max restore duration (RTO), max acceptable data gap (RPO), required verification queries, application health checks. Example: "Restore completes in under 45 minutes; orders count within 0.1% of production at drill start; payment webhook idempotency smoke passes."
Roles and runbook. Primary operator, verifier (independent row counts), comms owner if drill touches staging shared with QA. Runbook steps numbered — no improvisation under timer. The rollback strategy for deploys discipline applies to drill abort: if restore corrupts shared staging, documented revert path exists.
Post-drill report. Actual RTO, actual RPO, failures encountered, runbook gaps, ticket list. Store reports — auditors and postmortems ask for drill history.
Common restore failures drills surface
Missing or corrupt WAL segments. Base backup restores; recovery stalls requesting 000000010000000000000001. Cause: archive command failed silently, lifecycle policy deleted WAL early, wrong bucket path. Drill fix: alert on archive failures; monitor WAL gap; test restore monthly.
Extension and version mismatch. Restore host lacks pgvector or Postgres minor version differs. CREATE EXTENSION fails; application boot loops. Drill fix: pin extension list in runbook; restore images match production major version.
Ownership and permissions. Restore as superuser; application role missing GRANT on sequences after dump. Writes fail with opaque errors. Drill fix: restore script replays role grants; smoke test includes insert.
Connection string and secret sprawl. Restored instance reachable only from operator laptop VPN. Application still points at dead primary. Drill fix: document cutover checklist — parameter store update, connection pool drain, replica lag check.
Large database timeout. Restore exceeds maintenance window; disk fills on restore host. Drill fix: right-size restore infrastructure; stream from object storage; parallel restore where supported.
Logical dump partial restore. Team runs pg_restore without --single-transaction on related tables; FK violations. Drill fix: documented restore flags per dump type.
Each failure in drill is cheaper than the same discovery during ransomware response.
Point-in-time recovery drill script
PITR drills prove the WAL chain — the most common gap in "we have backups."
Pre-drill setup (in isolated env):
# Restore base backup into PGDATA
rm -rf /var/lib/postgresql/data/*
tar -xf base_backup_latest.tar -C /var/lib/postgresql/data
# recovery.conf or postgresql.auto.conf
restore_command = 'aws s3 cp s3://wal-archive/%f %p'
recovery_target_time = '2026-07-10 12:00:00 UTC'
recovery_target_action = 'promote'Post-promotion verification:
-- Known anchor row inserted before drill cutoff
SELECT id, updated_at FROM drill_anchors WHERE name = 'pitr_test';
-- Must exist if inserted before recovery_target_time
SELECT pg_is_in_recovery(); -- must be false after promote
SELECT count(*) FROM critical_business_table;
-- Compare to expected count at cutoff from pre-drill snapshotRecord wall-clock time from "start restore command" to "application health check green." Compare to RTO SLA. If over, drill fails — capacity or process problem, not badge of honor.
Integrating drills with migrations and deploy safety
Backup posture and schema migration discipline interact. A zero-downtime Postgres migration during expand phase is forward-compatible — restore drills should include schema at multiple migration versions if blue/green deploys lag.
Before destructive contract-phase migrations, run backup drill or verify PITR anchor within RPO. DROP COLUMN after contract is irreversible without restore — drill proves restore reaches pre-drop state.
Drill data hygiene: restored production data in staging carries PII. Mask or subset where possible; restrict network access; expire restored instances after drill. Compliance teams care about drill environments as much as production.
Replica promotion drills differ from backup restore — measure replication lag at promotion time, split-brain risk if primary not fully isolated, sequence and UUID continuity. Include one promotion drill annually if replicas are the failover path.
Automation vs manual drills
Automate weekly smoke. CI or scheduled job: restore latest backup to ephemeral instance, run checksum query, tear down. Alerts on failure. Cheap continuous signal.
Keep quarterly full drill manual. Humans discover runbook ambiguity machines miss — wrong subnet, expired IAM role, on-call unfamiliarity. Automate verification steps; not the organizational learning.
Chaos-adjacent exercises. Game day: simulate primary loss without warning on-call — only after monthly smokes pass. Surprises reveal communication gaps; run only with executive buy-in and abort criteria.
Store drill artifacts: restore logs, timing metrics, version of backup used. Trend RTO over quarters — growing database without growing restore infrastructure shows up in drill duration before incident.
Monitoring backups between drills
Drills are periodic proof; monitoring is continuous hygiene. Alert on conditions that predict drill failure before the calendar says quarterly:
| Signal | Threshold example | Why it matters |
|---|---|---|
| Base backup age | No successful base in 26 hours | RPO blown before anyone notices |
| WAL archive gap | Missing segment for > 15 minutes | PITR dead at next incident |
| Backup job duration spike | 2× seven-day median | Disk full, lock contention, network throttle |
| Restore test failure | Weekly smoke red | Archive corruption or permission drift |
| Backup storage growth anomaly | Flat size while writes grow | Dump silently empty or wrong database |
pg_backup_start / agent errors | Any failure | Silent green dashboard on partial success |
Backup monitoring belongs on the same paging tier as replication lag — not buried in infrastructure email digests. A backup job that succeeds by uploading an empty file is why checksum verification in weekly smoke matters.
Between drills, document backup configuration changes in the same changelog as application deploys. Extension added to production without updating restore image breaks the next drill the same way a migration without expand phase breaks deploy.
How should teams run Postgres restore drills?
Operational answers for database and platform owners.
What RPO and RTO should drills enforce?
RPO/RTO are business decisions — drills enforce documented numbers. SaaS billing might require RPO under fifteen minutes (WAL archiving) and RTO under one hour. Internal analytics might tolerate RPO twenty-four hours (daily dump). Drills fail when measured recovery exceeds stated objectives.
How is restore different from failover to a replica?
Replica promotion is faster when replication healthy — seconds to minutes lag. Promotion fails if replication broken or replica corrupted with primary. Backup restore is slower but independent failure domain. Drill both if both are in the strategy.
Should drills use production data?
Full fidelity finds real FK and extension issues; masking reduces compliance risk. Common pattern: production backup into isolated VPC with masked PII for application smoke; separate synthetic drill for CI weekly.
When should drills block schema migrations?
Before irreversible contract migrations on critical tables, verify PITR or snapshot within RPO covers the change window. Drill failure blocks migration until archive chain verified — same gate as migration lock review.
A common argument runs the other way
The opposing view holds that managed database services make drills redundant — the provider guarantees backups and one-click restore.
Managed services guarantee mechanisms, not organizational readiness. Provider restore still requires knowing target timestamp, connection cutover, application compatibility, and duration — which varies by database size and region. Provider dashboards show backup enabled; they do not run application smoke tests or measure whether on-call can execute cutover under stress. Drills validate the full path including application layer — provider click-ops is one step.
Key takeaways
- Backup success metrics are not recoverability — only restore drills prove RPO and RTO.
- Match drill type to backup stack: logical, physical + WAL, replica promotion, managed PITR.
- Quarterly full drills with timed success criteria, roles, and written reports.
- Weekly automated restore smoke catches WAL gaps and corrupt archives early.
- Drills surface extension mismatch, permissions, connection cutover, and capacity limits.
- Isolate restore targets; treat drill environments as sensitive data.
- Integrate drills with migration gates before irreversible schema contract phases.
Conclusion
Postgres backup restore drills convert durability assumptions into measured facts. The program is modest: weekly automated restore smoke, quarterly timed full restore with application verification, documented RPO/RTO pass criteria, and post-drill tickets for every runbook gap.
Teams with green backup monitors and no drill history are one corruption event away from discovering nine hours of data loss. Teams that drill know the stopwatch number, the WAL chain is intact, and on-call has executed the cutover checklist before the incident demands it.