With 2 nodes, quorum-based failover cannot work safely. If the primary fails and the replica promotes, there is no third vote to break ties — any network partition that splits the nodes at the same time as the primary failure creates a split-brain scenario where both nodes believe they are primary. The only safe configuration with 2 nodes is manual failover or shared-nothing failover where one node can definitively prove it is the only one that can serve writes (via shared storage or fencing). The recommendation is to add a third node or use a witness/arbitrator node to enable quorum-based failover.

The old primary may still have active connections and continue accepting writes locally. When the network recovers and the old primary rejoins the cluster, it has writes that were never replicated to the new primary. You now have divergent data. The cluster must reconcile — either by reverting the old primary to a replica and having it replay the missing writes, or by accepting data loss. This is why fencing (STONITH) is critical: before the new primary promotes, the old primary must be isolated from accepting writes. Without fencing, the old primary can continue writing during a partition and cause split-brain.

Three tuning points: reduce the heartbeat interval (Patroni sends heartbeats between nodes), reduce the loop_wait parameter which controls the Patroni main loop, and reduce the retry_timeout for etcd operations. Standard Patroni settings have a loop_wait of 10 seconds and a heartbeat interval of 10 seconds, giving a minimum detection time around 30 seconds. Reducing loop_wait to 1-2 seconds gets detection time under 10 seconds, but at the cost of increased etcd write load. For truly sub-30-second failover, synchronous replication to at least one replica is the most reliable approach since the promoted replica is guaranteed to be current.

Quorum requires that a majority of nodes must agree before promoting a new primary. With 3 nodes, you need 2 votes to promote. If the primary fails and one replica also loses connectivity, the isolated replica has only 1 vote — it cannot promote itself. The remaining 2-node group has 2 votes and can elect a new primary. This prevents split-brain because a single node cannot form a majority on its own. Minimum safe configuration: 3 nodes for odd-numbered quorum (3, 5, 7). With 2 nodes, there is no majority possible without the other node — any network partition creates two nodes each believing they are primary. Some setups use a witness/arbitrator node (like etcd's learner) to break ties for 2-node clusters, but a 3-node cluster is the recommended minimum for production automatic failover.

STONITH (Shoot The Other Node In The Head) is a fencing technique that physically isolates a failed node before promoting a new primary. When a replica decides to promote, STONITH first power-cycles or network-isolates the old primary to ensure it cannot continue writing. Without STONITH, the old primary may still be running and accepting writes during a network partition — when the partition heals, you have two primaries with divergent data (split-brain). STONITH ensures the old primary is definitively dead before the new primary accepts writes. Implementation options: IPMI power management, hardware out-of-band interfaces, or cloud provider APIs (AWS instance stop). STONITH adds operational complexity but is required for strong failover safety guarantees.

Clients cached the old IP with a 1-hour TTL. After failover, the DNS record points to the new primary's IP, but clients ignore the update for up to an hour. During this window, writes to the failed primary's IP fail with connection errors. If the old primary is still running (not properly shut down), it may accept writes that are never replicated to the new primary — data divergence. Prevention: set database DNS TTL to 60 seconds or less before deploying failover automation. Use a database-specific DNS endpoint (like RDS endpoint or a dedicated CNAME) that resolves to the current primary rather than hardcoding IPs. Some setups use a virtual IP (VIP) that floats to the new primary — the application connects to the VIP and the network handles the switch without DNS changes.

loop_wait is the main Patroni loop interval — default 10 seconds. Each loop iteration checks cluster health and sends heartbeats. heartbeat (Patroni 2.0+) is the interval between heartbeat signals, typically 2 seconds. The effective detection time is roughly (loop_wait * number_of_failed_heartbeats) + network_jitter. Standard settings give ~30 second detection. retry_timeout is the timeout for etcd/consul operations — if the distributed store does not respond within this time, Patroni treats it as a failure. To achieve sub-30-second MTTR: reduce loop_wait to 1-2 seconds (increases etcd load significantly), reduce heartbeat interval, ensure etcd/consul is fast and co-located. For truly fast failover, synchronous replication to at least one replica is more reliable than aggressive timeouts.

The monitoring system triggered failover based on a single failed health check or too-short timeout. A brief network blip caused health checks to fail for a few seconds, the failover automation interpreted this as primary death, and promoted a replica. Meanwhile the old primary continued running normally and was still accepting writes locally. When the network recovered and both primaries existed, you had a split-brain scenario. Prevention: require multiple consecutive failures before triggering failover (e.g., 3 consecutive failures over 30 seconds). Use health check intervals of 5-10 seconds with enough consecutive failures to survive brief blips. Some systems use quorum (multiple independent monitors must agree) rather than single-monitor decision. Also implement a pre-promotion verification step that checks the candidate replica's data freshness before allowing promotion.

RDS Multi-AZ uses synchronous replication to a standby in a different AZ. When the primary fails, Amazon's internal monitoring detects it, promotes the standby, and updates the DNS endpoint to point to the new primary. The application connects to the same endpoint (e.g., mydb.abc123.us-east-1.rds.amazonaws.com) — DNS propagates the new IP. During the 60-120 second failover window: connection attempts fail with connection errors; in-flight transactions fail; your application must handle reconnection with exponential backoff. The RDS endpoint masks the IP change but does not eliminate application disruption. Key application requirements: retry logic with backoff on connection failure, connection pool that can recover from failed connections, and no hardcoded IP addresses. Test failover explicitly — RDS allows you to trigger a test failover to verify your application's handling.

In raw mode (native PostgreSQL), pgpool-II does not handle replication — it just routes queries to a single PostgreSQL backend. Failover in raw mode means detecting backend failure and switching to a standby — pgpool can do this but you must configure watchdog for HA. In replication mode, pgpool maintains a real replication connection to multiple PostgreSQL servers and replicates writes across them. Failover in replication mode is more complex — pgpool can detect failed nodes but must also manage the replication state. For most automatic failover setups with PostgreSQL, Patroni + PgBouncer is preferred over pgpool-II because Patroni handles failover coordination with etcd/consul while pgpool focuses on connection pooling. pgpool-II works well for read load balancing but Patroni is the standard for HA.

A witness node (or arbitrator) participates in quorum but does not store data. With 2 data nodes + 1 witness: if the primary fails, the replica + witness have 2 votes (majority of 3), allowing promotion. If the witness fails, the 2 data nodes can still communicate and continue operating — the primary does not promote spuriously. This gives you safe automatic failover with 2 data nodes without requiring a 3rd full data node. The tradeoff: the witness is a single point of failure for the election itself (if both the witness and one data node fail simultaneously, you lose quorum). Also verify that your failover tool (Patroni, etc.) supports witness/arbitrator nodes and that the witness's network is reliable and in the same failure domain as the data nodes.

Automatic failover: MTTR 30-60 seconds, highest availability, but risk of false positives (spurious failover) and split-brain without proper quorum/fencing. Semi-automatic (operator-triggered): monitoring alerts an on-call engineer who manually approves and triggers failover — MTTR 2-5 minutes, lower false-positive risk because a human verifies before acting. Manual failover: engineer diagnoses, then runs procedures manually — MTTR 5-15 minutes, no spurious failover risk, but human response time adds delay. For business-critical databases with 24/7 availability requirements, automatic failover is appropriate if quorum and fencing are properly implemented. For less critical systems or those with skilled ops teams that can respond in under 5 minutes, semi-automatic may provide better safety-to-speed balance. Manual is appropriate for development and staging.

When the primary fails, all application instances that had connections to it receive connection errors nearly simultaneously. Each instance's connection pool or ORM sees the failure and independently starts retrying with exponential backoff — but without coordination, all instances retry at the same times (they failed at the same time). This creates a connection storm that can overwhelm the new primary or the connection pooler. Prevention: use PgBouncer or ProxySQL in front of the database — the pooler maintains its own connection pool to the database and handles reconnection internally, absorbing the burst rather than letting it hit the database directly. Configure pooler reconnection with jitter and exponential backoff. At the application level, implement circuit breakers that temporarily stop sending requests to the database during failover, allowing the pooler time to stabilize.

Patroni requires a distributed consensus store (etcd, Consul, ZooKeeper). For production: use 3 or 5 etcd nodes for HA — a 3-node etcd cluster tolerates 1 failure, 5-node tolerates 2. Place etcd nodes on separate hardware and network segments from the PostgreSQL primaries to avoid correlated failures. Etcd performance matters: use SSDs for etcd data directory, network locality between etcd nodes and Patroni nodes, and monitor etcd write latency (should be < 10ms). If etcd becomes slow, Patroni loops hang and failover detection slows down. For read-heavy workloads, etcd can handle thousands of reads per second, but ensure your etcd cluster is not overutilized — dedicated etcd clusters for critical Patroni deployments are recommended over sharing with other workloads.

MTTR = detection_time + decision_time + promotion_time + catchup_time. Detection_time = health_check_interval * consecutive_failures_required. With 5s checks and 3 failures required, minimum detection is 15s plus network jitter. Decision_time = leader_election_timeout (for quorum-based systems like Patroni with etcd). Default etcd election timeout is 10s, so decision_time is at least 10s. Promotion_time = time to rewrite PostgreSQL control file and update replication position. PostgreSQL promotion typically takes 2-5s. Catchup_time = if using synchronous replication, catchup_time is 0. If async, the new primary may need to replay remaining WAL, which depends on lag at failover time. Target MTTR with proper automation: 30-60 seconds. Manual failover typically takes 5-15 minutes. Formula-based: MTTR = (health_check_interval * failures) + election_timeout + promotion_latency + async_lag_at_failover.

Immediately after failover: verify the new primary is accepting writes and the old primary is isolated (offline or demoted). Then: (1) Configure the old primary to replicate from the new primary as a replica — this is usually automatic in Patroni. (2) Verify replication is catching up — check pg_stat_replication for the new replica. (3) If using a load balancer or connection pooler, verify traffic is routing to the new primary. (4) Monitor the new replica's lag — it may need to replay a backlog of WAL. (5) Once replication is current and stable, you are back to full redundancy (1 primary + N replicas). If the old primary had hardware failure, repair or replace it and add it back to the cluster as a new replica. Document the failover event and review whether the automation worked correctly or needs tuning.

Investigate: check the failover automation logs (Patroni, Consul, etc.) for the decision rationale. Look at the health check results around the failover time — were there multiple consecutive failures or just one? Check system metrics on the old primary: was it actually healthy or was it experiencing load/CPU/network issues that made it appear down? If the old primary was healthy and reachable, the health check was too sensitive. Metrics that warn of false positives: health check success rate over time (if success rate drops below 100%, you are near the threshold), replication lag (a lagging replica is a poor failover candidate), and CPU/memory/disk on the candidate replica (a resource-exhausted replica should not be promoted). Prevention: require more consecutive failures, use longer health check intervals, and implement pre-promotion checks that verify the candidate's resource health and replication position.

HAProxy uses health checks (TCP checks or custom scripts) to monitor each backend. If the primary fails checks, HAProxy marks it dead and stops routing traffic to it. If you configure a backup server (the replica becomes the backup), HAProxy promotes the backup to active once the primary is marked dead. For PostgreSQL, the typical health check is pg_isready via TCP. The failover is not automatic on the database side — HAProxy only detects and routes away from failed backends. You still need Patroni or similar to promote the replica at the database level. HAProxy then detects the new primary's IP (which Patroni or your failover script updates) and resumes routing. For smoother transitions, use a virtual IP that floats to the new primary — HAProxy just checks the VIP and the network handles the IP movement.

Hardware failures and software bugs require different recovery approaches. Hardware failure (power supply, disk, memory) typically leaves the database in a clean state — the last committed transaction is well-defined. Software bugs (corrupt indexes, half-applied transactions, crashed writer process) can leave the database in an inconsistent state where promotion to primary is dangerous because the replica may carry forward corrupted data. Design for both: implement checksum verification on replication data to detect corruption before promotion. Use post-promotion verification that runs consistency checks (PostgreSQL's pg_checksums, MySQL's CHECK TABLE) before accepting writes on the new primary. For software bugs causing inconsistency, prefer a full data comparison between candidate replica and primary before promotion rather than blindly promoting the first replica that responds. Consider separating failure classes — hardware failure triggers immediate promotion, software inconsistency triggers alert-and-review before automatic promotion. Maintain backups that are independent of the replication chain so you can restore to a known-good state regardless of which replica you promote.

Long-running transactions that started on the primary before failover may fail when the replica promotes — the transaction's session is interrupted, and if it attempts to commit, the new primary may not have the transaction in its state. The new primary also has a different timeline: transactions that were in-flight on the old primary but not yet committed may be lost. Strategies: keep transaction durations short — long-running transactions are more likely to span a failover event. Use application-level transaction retry logic that catches connection errors and re-executes failed transactions (idempotent operations). Avoid multi-statement transactions that hold locks across the failover window. For critical transactions, consider using synchronous replication so the replica is current at promotion time. Monitor in-flight transaction count as part of your failover metrics — a sudden spike in long-running transactions before failover increases the chance of transaction failures during promotion.