8.4 Failover, Switchover & High-Availability Design

8.4 Failover, Switchover & High-Availability Design

[!IMPORTANT] High Availability Defined: True database High Availability (HA) requires more than just replicating data bytes—it requires a coordinated lifecycle of failure detection, leader election, node fencing, traffic redirection, and seamless cluster re-integration. Without rigorous orchestration, unplanned outages can lead to catastrophic split-brain scenarios where multiple nodes accept conflicting writes, permanently corrupting enterprise data integrity.

This section covers the operational protocols governing planned switchovers, emergency failovers, timeline history branching, fast recovery with pg_rewind, and automated HA architectures using distributed consensus.

---

Switchover vs. Failover: Operational Protocols

Database administrators must distinguish clearly between planned and unplanned role transitions:

1. Planned Switchover (Graceful Role Reversal)

A switchover is an intentional, scheduled operational procedure (e.g., for operating system upgrades, hardware replacement, or database patching) where the existing primary is demoted and a standby is promoted with zero data loss (RPO = 0).

• Standard Switchover Procedure:
  1. Stop or pause client write traffic at the application or connection pooler tier.
  2. Demote or cleanly shut down the active primary server (pg_ctl stop -m fast).
  3. Verify that all outstanding WAL generated by the primary has been fully received, flushed, and replayed on the target standby replica (replay_lsn matches primary's last shut down LSN).
  4. Promote the designated standby replica to become the new read-write primary.
  5. Reconfigure the old primary as a standby replicating from the newly promoted primary.
  6. Redirect application traffic to the new primary.

2. Unplanned Failover (Emergency Disaster Recovery)

A failover occurs under crisis conditions when the primary server suffers a sudden, unexpected failure (e.g., physical hardware crash, kernel panic, or sudden loss of power/network).

• Characteristics: The standby is promoted to primary immediately to restore write availability and minimize downtime (Recovery Time Objective, or RTO).
• Data Loss Risk: If the cluster was operating under asynchronous replication, any WAL records that were committed locally on the old primary but not yet received by the standby prior to the crash are lost (RPO > 0).

---

Standby Promotion: Mechanics and Tools

When a standby replica is promoted, it exits continuous recovery mode and transforms into an independent read-write primary database.

Promotion Methods

PostgreSQL provides two standard interfaces to initiate promotion:

# 1. Operating system CLI command
pg_ctl promote -D /var/lib/postgresql/data

-- 2. SQL callable function (available in PostgreSQL 12+)
SELECT pg_promote(wait => true, wait_seconds => 60);

What Happens Internally During Promotion

1. Finish Replay: The startup process finishes replaying all remaining WAL records currently available in its local pg_wal directory or archive.
2. Signal Removal: The server automatically unlinks and removes the standby.signal file from $PGDATA.
3. Checkpoint & Timeline Branch: The server writes an End-of-Recovery checkpoint record into the WAL, closes recovery, and increments its Timeline ID (e.g., from Timeline 1 to Timeline 2).
4. History File Generation: The new primary writes a timeline history file (e.g., 00000002.history) into pg_wal documenting the exact LSN at which it branched from its parent timeline.
5. Open for Writes: The server transitions to read-write mode, spawning normal background workers (such as the autovacuum launcher) and accepting write transactions.

---

The Split-Brain Catastrophe and Fencing (STONITH)

The single greatest danger in high-availability database architectures is split-brain.

+------------------------------------------------------------------------------------------+
|                                The Split-Brain Catastrophe                               |
+------------------------------------------------------------------------------------------+
|  Clients Writing (Partition A)                  Clients Writing (Partition B)            |
|               │                                                 │                        |
|               ▼                                                 ▼                        |
|  +-------------------------+     Network     +-------------------------------------+     |
|  | OLD PRIMARY             |    Partition    | PROMOTED STANDBY                    |     |
|  | (Status: Read-Write)    | < - - X - - - > | (Status: Read-Write)                |     |
|  | Timeline: 1             |                 | Timeline: 2                         |     |
|  | Accepting Writes!       |                 | Accepting Writes!                   |     |
|  +-------------------------+                 +-------------------------------------+     |
|               │                                                 │                        |
|               ▼                                                 ▼                        |
|     Divergent History A                               Divergent History B                |
|         (Order #1001 = Customer Alice)                    (Order #1001 = Customer Bob)   |
+------------------------------------------------------------------------------------------+

How Split-Brain Occurs

Suppose a transient network partition isolates the primary server from the rest of the network. A naive automated monitoring daemon, unable to reach the primary, concludes that the primary is dead and promotes a standby replica. However, the original primary was merely partitioned—it is still alive and running. If clients on the old network partition continue writing to the old primary while other clients write to the new primary, both servers diverge down completely different transactional timelines.

Because both instances allocate the same transaction IDs, row identifiers, and table pages to completely different business transactions, the data becomes irreconcilably corrupt and cannot be merged automatically.

Fencing and STONITH

To guarantee that split-brain is mathematically impossible, production HA systems enforce Node Fencing:

• STONITH ("Shoot The Other Node In The Head"): Before promoting any standby replica, the automated HA controller issues a physical hardware command (via IPMI, networked Power Distribution Unit/PDU, or cloud hypervisor API) to forcibly power off or isolate the suspected dead primary node.
• Demotion Verification: Promotion is strictly blocked until the controller confirms that the old primary is dead or completely severed from client traffic.

---

Automated Consensus-Based HA: Patroni

Manual failover is too slow for modern service-level agreements (SLAs) requiring sub-minute RTO. However, naive custom shell scripts that promote replicas without quorum consensus frequently cause split-brain.

Enterprise PostgreSQL architectures deploy consensus-based high-availability managers, of which Patroni is the industry standard:

+-----------------------------------------------------------------------------------------+
|                         Patroni High-Availability Architecture                          |
+-----------------------------------------------------------------------------------------+
|                  +---------------------------------------------------+                  |
|                  | Distributed Consensus Store (DCS): etcd / Consul  |                  |
|                  | - Leader Lock: /service/batman/leader (TTL: 10s)  |                  |
|                  +-------------------------+-------------------------+                  |
|                                            ^                                            |
|                          Heartbeat Lease   |   Heartbeat Lease                          |
|                                            v                                            |
|  +--------------------------------+                 +--------------------------------+  |
|  | NODE 1 (Leader)                |                 | NODE 2 (Standby)               |  |
|  | +----------------------------+ |                 | +----------------------------+ |  |
|  | | Patroni Daemon             | |                 | | Patroni Daemon             | |  |
|  | +--------------+-------------+ |                 | +--------------+-------------+ |  |
|  |                | controls      |                 |                | controls      |  |
|  |                v               |                 |                v               |  |
|  | +----------------------------+ |   WAL Stream    | +----------------------------+ |  |
|  | | PostgreSQL (Primary)       | | ==============> | | PostgreSQL (Standby)       | |  |
|  | +----------------------------+ |                 | +----------------------------+ |  |
|  +--------------------------------+                 +--------------------------------+  |
+-----------------------------------------------------------------------------------------+

How Patroni Works

1. Distributed Consensus Store (DCS): Patroni relies on an external consensus cluster (such as etcd, Consul, or ZooKeeper) that uses the Raft or Paxos algorithm to maintain distributed consensus.
2. Leader Lease: The Patroni daemon on the active primary acquires a dynamic leader key in the DCS with a short Time-To-Live (TTL, e.g., 10 seconds). The leader must continuously renew this lease.
3. Automatic Self-Demotion: If the primary node loses network connectivity to the DCS, it cannot renew its leader lease. Patroni immediately fences the node by forcibly demoting PostgreSQL to read-only or terminating it.
4. Quorum Failover: Once the leader key expires, the remaining Patroni nodes evaluate their replication lag (replay_lsn). The standby with the most advanced WAL progress acquires the leader lock and executes pg_promote().

---

Timeline Management and the .history File

PostgreSQL uses Timeline IDs to track branches in WAL history.

WAL Segment Naming Convention

Every 16MB WAL segment file name consists of 24 hexadecimal digits divided into three 8-digit sections:

000000020000001B00000042

• 00000002: Timeline ID (Timeline 2).
• 0000001B: Logical WAL file number.
• 00000042: Segment number within the logical file.

The Timeline History File (.history)

Whenever a standby promotes and increments its timeline from 1 to 2, it generates a history file named 00000002.history in pg_wal:

# Contents of 00000002.history
1    0/1B420000    no recovery target specified

This file informs downstream replicas and backup utilities that Timeline 2 branched from Timeline 1 at Log Sequence Number 0/1B420000. Other standbys in the cluster read this history file, discover the branch point, and automatically follow the new primary onto Timeline 2 without requiring manual intervention.

---

Node Re-integration with pg_rewind

After an emergency failover, the old primary server often recovers. However, because it may have written local transactions before crashing that were never sent to the standby, its local WAL timeline has diverged from the newly promoted primary's timeline.

Historically, the only way to re-join the old primary was to delete its entire multi-terabyte data directory and execute a full pg_basebackup—a process taking hours or days.

The pg_rewind Solution

The pg_rewind utility compares the divergent timelines and brings the old primary back into synchronization in minutes:

# Execute on the old primary while it is shut down
pg_rewind \
  --target-pgdata=/var/lib/postgresql/data \
  --source-server='host=new_primary_host port=5432 user=repuser dbname=postgres' \
  -P

Old Primary (Timeline 1)                 [Fork Point] ---> [Divergent Local Writes]
                                              │
New Primary (Timeline 2)                      └───> [New Writes on Promoted Primary]

pg_rewind Action: Overwrites divergent local blocks with matching blocks from Fork Point!

How pg_rewind Operates

1. Examines the .history files to identify the last common checkpoint (the fork point) where the old and new timelines separated.
2. Scans the WAL of the new primary starting from the fork point to locate every database page (8KB block) modified since the split.
3. Copies only those modified 8KB blocks from the new primary to the old primary, overwriting the divergent pages.
4. Prerequisites: Requires either wal_log_hints = on in postgresql.conf or data checksums enabled (initdb -k) during cluster initialization so that block modifications are recorded in WAL.
5. Once pg_rewind finishes, the administrator creates standby.signal, updates primary_conninfo, and starts the server as a functioning standby replica replicating from the new primary.

---

Client Connection Routing and Load Balancing

High-availability database clusters require routing client traffic to the appropriate nodes: write traffic must reach the current primary, while read-only traffic can be distributed across standby replicas.

1. Client-Side Multi-Host Routing (target_session_attrs)

Modern libpq client libraries (including psql, Python psycopg2/asyncpg, Java JDBC, and Go pgx) natively support multi-host connection strings:

postgresql://node1:5432,node2:5432,node3:5432/production?target_session_attrs=read-write

• When the application connects, the driver sequentially polls each host in the list and executes an internal check (SHOW transaction_read_only).
• If node1 fails and node2 is promoted, the driver automatically skips node1 and establishes the connection to node2 without requiring any load balancer changes!

2. Connection Pooling (PgBouncer) & Health Checks

In enterprise topologies, applications connect to PgBouncer connection poolers placed in front of Virtual IP (VIP) addresses or Layer 4/7 load balancers (such as HAProxy or AWS NLB).

• Health Check Query: The load balancer continuously polls each PostgreSQL instance:

  -- Returns FALSE on Primary (Read-Write), TRUE on Standby (Read-Only)
  SELECT pg_is_in_recovery();
  

• If pg_is_in_recovery() returns false, the load balancer directs write port 5432 traffic to that node. If it returns true, traffic on read-only port 5433 is routed to that node.

---

Exam Tips and Common Pitfalls

• Exam Trap: Standby Promotion Commands: In modern PostgreSQL, promotion is triggered via pg_ctl promote -D <path> or the SQL function SELECT pg_promote(). Both methods remove standby.signal and increment the timeline ID.
• Exam Trap: Purpose of pg_rewind: pg_rewind does not perform an initial cluster clone (use pg_basebackup for that). pg_rewind is used specifically to reconcile an old, divergent former primary so it can rejoin the new primary as a standby without re-cloning the entire database.
• Exam Trap: Split-Brain Mitigation: If an exam question asks how to prevent split-brain during automated failover, the answer is node fencing / STONITH and distributed consensus (DCS). Health checks alone cannot prevent split-brain during network partitions.