# Database Failover vs Failback — Runbook Excerpt (Decision Tree)Assumptions- PostgreSQL primary + one or more streaming replicas; SSH access to hosts and monitoring agent (Prometheus) available.- Replication uses streaming WAL; controlled failover via repmgr or Patroni.- RTO requirement: 5 minutes for critical workload.## Quick Checks (run in order)1. Check primary write availability- Command:bash
# PostgreSQL: returns 0 when accept connections
pg_isready -h primary-db.example.com -p 5432
# or quick write test
psql -h primary-db.example.com -c "CREATE TEMP TABLE t_failover_test(id int); DROP TABLE t_failover_test;"
2. Check replication lag (on replica)- Commands:bash
# PostgreSQL replica-side lag (seconds)
psql -h replica-db.example.com -c "SELECT EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) AS replay_lag_s;"
# Or via monitoring: curl http://prometheus/targets
## Thresholds- Replication lag warning: > 5s- Replication lag critical: >= 30s- Primary write failure: any non-zero exit or write test error- Replica health: node reachable, postgres running, replay_lag_s numeric## Decision Tree (high-level)1. Is primary accepting writes?- Yes -> No failover. Monitor replication lag. - If lag >= 30s -> Investigate network/IO; do NOT failover unless primary is degraded.- No -> Proceed to (2).2. Is at least one replica healthy and lag < 30s?- Yes -> Promote the healthiest replica (lowest lag). - Example promote (repmgr):bash
repmgr standby promote -f /etc/repmgr.conf --log-level INFO
- OR Patroni:bash
curl -XPOST http://replica:8008/leader
- No -> If all replicas show high lag or unhealthy: - Assess data loss tolerance. If RTO higher priority than RPO and application permits, perform forced failover promoting best-effort replica and document data loss. - Otherwise escalate to DBA and restore primary from backups.## Failback Decision1. Has original primary recovered (services up, db consistent)?- Run health checks above on original primary.- If recovered and lag is zero (or caught up), plan controlled failback during maintenance window: - Demote current primary to replica (use repmgr or Patroni), then reconfigure and promote original primary if desired. - Example demote (repmgr):bash
repmgr standby promote --force # adjust per tooling
2. If original primary inconsistent or lagging -> keep current primary; rebuild original as replica.## Notes / Post-action- Always record timestamps, node IDs, WAL positions, and RPO implications.- Notify stakeholders and update runbook with root cause.