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.

Engineering8 min read
PostgresBackupsDisaster recoveryRestore drillsOperations
Share

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.

MechanismWhat it protectsDrill proves
Logical dump (pg_dump)Portable schema + dataDump restores cleanly; extensions handled
Physical base backupFull cluster files at point in timeBackup starts; Postgres promotes
WAL archiving / PITRChanges after base backupReplay to target timestamp
Replica / standbyNear-live copyPromotion and cutover
Volume snapshotBlock-level instant imageCrash consistency + WAL chain
Managed service PITRProvider-native timeline restoreRestore 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 typeCadenceScope
Automated restore smokeWeeklyLatest backup → SELECT 1, row count checksum
Full application restoreQuarterlyRestore + app smoke + read/write test
PITR to arbitrary timestampQuarterlyRestore to T-15min; verify known row state
Full region / provider failoverAnnuallyNew instance, DNS cutover, rollback path
Table-level logical restoreSemi-annualSingle 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 snapshot

Record 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:

SignalThreshold exampleWhy it matters
Base backup ageNo successful base in 26 hoursRPO blown before anyone notices
WAL archive gapMissing segment for > 15 minutesPITR dead at next incident
Backup job duration spike2× seven-day medianDisk full, lock contention, network throttle
Restore test failureWeekly smoke redArchive corruption or permission drift
Backup storage growth anomalyFlat size while writes growDump silently empty or wrong database
pg_backup_start / agent errorsAny failureSilent 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.

Related articles

Command Palette

Search for a command to run...