High Availability and Failover

1. Introduction

High Availability (HA) ensures your database remains operational despite hardware failures, network partitions, or software crashes. Failover is the process of automatically promoting a standby replica to primary when the current primary becomes unreachable. Tools like Patroni, repmgr, and pg_auto_failover automate this process using health checks, consensus protocols, and fencing mechanisms.

2. Why It Matters

Every minute of database downtime can cost thousands to millions of dollars. Manual failover takes 15-30 minutes; automated failover completes in 10-30 seconds. Without HA, a single server failure cascades into complete application unavailability. Proper HA design ensures RTO (Recovery Time Objective) of seconds and RPO (Recovery Point Objective) of zero transactions lost.

3. Real-World Analogy

HA is like having a backup generator for your home. When the main power (primary) fails, a transfer switch (failover tool) detects the outage, starts the generator (promotes the standby), and switches your house to generator power — all within seconds, without you flipping a single switch. Fencing is the equivalent of physically disconnecting the main power line to prevent a surge when both sources are active.

4. How It Works

• Health Monitoring: Patroni/repmgr periodically ping the primary. If unreachable after a configurable threshold (e.g., 30 seconds), failover is initiated.
• Consensus: Tools like Patroni use etcd or ZooKeeper for distributed consensus — preventing split-brain where both nodes believe they are primary.
• Promotion: The chosen standby runs pg_promote(), becoming the new read-write primary.
• Fencing: The old primary must be prevented from accepting writes when it comes back. Methods include STONITH (power off), WAL replay checks, and pg_rewind for re-syncing.
• DNS/VIP Update: The application connection string is updated to point to the new primary via DNS failover, floating VIP, or proxy reconfiguration.

5. Internal Architecture

Patroni architecture: Each PostgreSQL node runs a Patroni agent that registers with a Distributed Configuration Store (DCS) like etcd. The DCS holds a leader key with a TTL. The primary node renews this key periodically. If the key expires (primary down), Patroni agents on standbys race to acquire it. The winner promotes itself and updates the cluster topology. pg_auto_failover uses a monitor node instead of etcd, providing a simpler setup for 2-node clusters.

6. Visual Explanation

The diagram shows the failover sequence: primary fails health check, consensus protocol elects new leader, standby promotes, fencing isolates old primary, and traffic routes to new primary.

7. Practical Example

8. Common Mistakes

• No fencing: Old primary rejoins as a second writer (split-brain), corrupting data. Always implement STONITH or pg_rewind.
• Short TTL on leader key: Causes unnecessary failovers during brief network hiccups. Balance between fast failover and false positives.
• Not testing failover: Run chaos engineering exercises (kill primary, simulate network partition) regularly to validate HA behavior.
• Ignoring pg_rewind: When the old primary comes back, it can't simply rejoin as a standby. Use pg_rewind to synchronize its timeline.

9. Quick Quiz

10. Scenario-Based Challenge

Design an HA setup for a fintech application requiring RPO=0 (zero data loss) and RTO<30 seconds. Choose between Patroni with etcd and pg_auto_failover. Configure synchronous replication, define the failover workflow, and describe how the application reconnects to the new primary after failover.

11. Debugging Exercise

12. Interview Questions

• Q: What is split-brain and how is it prevented?
  A: Split-brain occurs when two nodes both believe they are the primary and accept writes, causing data divergence. It's prevented by consensus protocols (etcd/ZooKeeper), fencing (STONITH), and leader key TTLs.
• Q: What is pg_rewind used for?
  A: After failover, the old primary's timeline has diverged. pg_rewind rolls back the old primary's data directory to the common ancestor and replays the new primary's WAL, allowing it to rejoin as a standby without a full base backup.

13. Production Considerations

• wal_log_hints: Must be enabled for pg_rewind to work. Set at cluster initialization or in postgresql.conf.
• Multi-site HA: Use synchronous replication to a nearby standby + async to a distant one. Balance between RPO and latency.
• Application reconnection: Use connection poolers with health checks (PgBouncer, HAProxy) that detect primary changes and update routing automatically.
• Monitoring: Alert on Patroni state changes, replication lag spikes, and etcd cluster health. Log all failover events for post-mortem analysis.