Failover Drill Skill

## Failover Drill Skill - phases
1. Pre-check: topology, lag, backups current
2. Baseline: record primary, replicas, connection endpoints
3. Failover: controlled switchover or provider failover
4. Verify: writes on new primary, apps reconnected
5. Rollback drill: optional failback to original topology
6. Document: RTO minutes, issues, runbook diffs

# Patroni pre-check
patronictl -c /etc/patroni/patroni.yml list
psql -h haproxy.internal -c "SELECT inet_server_addr(), pg_is_in_recovery();"

# 1. Pre-check replication lag
psql -c "SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;"
 
# 2. Switchover to replica2
patronictl -c /etc/patroni/patroni.yml switchover --master replica1 --candidate replica2 --force
 
# 3. Verify new primary
psql -h haproxy.internal -c "SELECT pg_is_in_recovery();"  # expect f
psql

# Failover test instance only - NOT prod without change window
aws rds reboot-db-instance \
  --db-instance-identifier myapp-staging-failover-test \
  --force-failover
 
# Watch events
aws rds describe-events --source-identifier myapp-staging-failover-test --duration 30

-- After app reconnect
SELECT now(), inet_server_addr();
INSERT INTO drill_log (note) VALUES ('post-failover write OK');

SELECT pg_is_in_recovery();
SELECT * FROM pg_stat_replication;  -- on primary: replicas connected
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

- [ ] PgBouncer/pooler detects backend change (or brief reconnect storm acceptable)
- [ ] ORM connection pool recycles within configured timeout
- [ ] Read-only analytics routes to replica endpoint still valid
- [ ] Background jobs resume without manual pod restart