# PostgreSQL Failover Drill

Version: 1.0.0  
Last reviewed: 2026-07-10  
License: MIT

Implementation guide: https://steadyops.best/articles/postgresql-at-scale/

## Preconditions

- [ ] Scope, owner, incident commander, and business validator are named.
- [ ] RPO/RTO and stop conditions are approved.
- [ ] Current primary, replicas, lag, and timeline are recorded.
- [ ] Backup and WAL archive status is known.
- [ ] Application retry and connection-pool behavior is understood.
- [ ] Monitoring covers role, lag, errors, connections, locks, and a customer transaction.

## Baseline

```bash
patronictl list
curl -fsS http://127.0.0.1:8008/cluster
psql -X -v ON_ERROR_STOP=1 -f postgresql-health-check.sql
```

Record the current primary and timeline, replica replay lag, application error rate, p95/p99 latency, HAProxy backend state, PgBouncer pools, backup freshness, and archive freshness.

## Failure injection

Choose one controlled scenario:

1. Stop Patroni/PostgreSQL on the primary.
2. Isolate the primary network path.
3. Stop the primary VM or node through the approved platform procedure.

Do not simulate destructive data loss in production without a separate approved recovery exercise.

## Observe and decide

- Which replica is eligible?
- Does lag meet RPO?
- Does the distributed configuration store have quorum?
- Is the old primary unable to accept writes?
- Which component changes client routing?
- What is the stop condition?

## Validate promotion

```bash
patronictl list
psql -X -v ON_ERROR_STOP=1 -c "select pg_is_in_recovery();"
```

## Validate routing and clients

- [ ] HAProxy marks the new primary writable.
- [ ] PgBouncer opens new server connections.
- [ ] Application connections recover without a restart storm.
- [ ] Retries do not duplicate business operations.
- [ ] Writes succeed through the normal application path.
- [ ] Read-only paths still use acceptable replicas.

## Recover redundancy

- [ ] Old primary is reinitialized or safely rejoined.
- [ ] Replication returns to expected lag.
- [ ] Backup and WAL archive continue on the new topology.
- [ ] Monitoring and alerts reflect the new role.
- [ ] No stale DNS, connection pool, or service-discovery state remains.

## Evidence

Record detection, election, promotion, routing recovery, application recovery, business validation, measured data loss, total recovery time, and every follow-up action.