High Availability and Failover for a Primary-Replica Database

0) Problem Restatement

Amazon asked: you run a relational database with one primary (takes all writes) and replicas (copies that follow the primary). How do you make it highly available? Explain replication modes, how to detect that the primary failed, how to promote a replica safely (without two primaries, called "split brain"), how clients find the new primary, and how the old primary rejoins.

1) Replication Modes

• Asynchronous: the primary commits and replies right away, and replicas catch up shortly after. Fast, but if the primary dies, the last few transactions may be lost (those not yet on any replica).
• Synchronous: the primary waits until at least one replica has the change before replying. No data loss on failover, but writes are slower (a network round trip), and if the sync replica is down, writes block (unless another replica can take over the sync role).
• Semi-sync / quorum (a common compromise): wait for one of several replicas. Durable and still available if one replica is slow.

Say the targets: RPO (how much data loss is acceptable, e.g., 0 seconds) and RTO (how long recovery can take, e.g., under 30 seconds). They drive the choice.

2) Architecture

flowchart LR
    APP["Applications"] --> PX["Proxy / service discovery (VIP, DNS, HAProxy)"]
    PX --> P[("Primary - zone A")]
    P -->|"sync replication"| R1[("Replica - zone B")]
    P -->|"async replication"| R2[("Replica - zone C")]
    ORCH["Failover manager (Patroni + etcd)"] --> P
    ORCH --> R1
    ORCH --> R2
    ORCH -->|"update routing"| PX

3) Failover Step by Step

1. Detect: the failover manager checks health from multiple observers (to avoid a false alarm from one network glitch). The primary must hold a lease in a consensus store (etcd/ZooKeeper). If it can't renew the lease within, e.g., 10 seconds, it's considered failed.
2. Fence the old primary: make sure it can't accept writes anymore. It demotes itself when it loses its lease, and the manager can also cut its network or power (STONITH, "shoot the other node in the head"). This prevents split brain.
3. Pick the best replica: the most up to date (highest replication position), ideally the synchronous one (no data loss).
4. Promote it to primary. Other replicas re-point to it.
5. Redirect clients: update the proxy, the virtual IP or DNS (a low TTL), or clients query service discovery. Connections to the old primary fail and reconnect.
6. Old primary rejoins as a replica after it's repaired, rewinding any writes that never reached the others (e.g., pg_rewind).

4) Reads and Lag

• Replicas can serve reads to scale, but they may be slightly behind (replication lag). For "read your own writes", route a user's reads to the primary for a short time after they write, or wait until the replica has caught up to the write's position.
• Monitor lag, and alert when it's high (it also means more data at risk with async replication).

5) Testing and Operations

• Practice failovers regularly (game days), and measure the real RTO.
• Backups plus point-in-time recovery (base backups + WAL archive), because replication copies mistakes too (a bad DELETE replicates instantly).
• Cross-region replica for disaster recovery (asynchronous, with a known RPO).

6) Wrap-Up

Replicate from the primary to replicas in other zones, using synchronous or quorum replication for zero data loss (or asynchronous when a small RPO is acceptable). Let a consensus-backed failover manager detect failure via leases and multiple observers, fence the old primary to avoid split brain, promote the most up-to-date replica and redirect clients through a proxy or discovery. Handle replica lag for reads, keep PITR backups, and test failover regularly against RPO and RTO targets.