Database administration · Case study
PostgreSQL DBA Toolkit
A primary, replica and WAL archive used to prove recovery and failover work.
At a glance
- Primary + WAL archive
- Streaming replica
- PITR: 3/3 checks
- Failover: 4/4 checks
The problem
A backup or standby configuration is only useful if a restore and promotion can be demonstrated. I built repeatable drills around those outcomes.
My role
Independent lab project. I configured PostgreSQL 16 primary/replica replication, wrote diagnostics and runbooks, and scripted recovery and failover tests.
Key decision
The PITR drill writes before and after a target time, drops a 500,000-row table, then restores to the target. The failover drill checks replication, read-only standby behaviour, promotion and writes on the new primary.
Outcome
The lab contains one million orders. Recovery passed 3 of 3 assertions and failover passed 4 of 4; the dropped table was recovered with all 500,000 rows.
Limits
These results come from a controlled Docker lab, not a production incident or an availability guarantee. The old primary still needs rewind or a fresh base backup before rejoining.
See the work
The repository contains the source, setup instructions and the evidence behind these results.