Chapter 5 — Replication and Failover

What a promotion loses, how you stop the old primary, and why going back is a second failover

Prerequisites: how replication works is covered in depth in SD 2 Chapter 4 — Why Machines Die (sync vs async, RPO/RTO, split brain) and SD 2 Chapter 5 — Leader-Follower Replication (LSNs, lag stages, promotion, timelines). Elections: SD 1 — Leader Election and Raft and Q41. Lag and read-your-writes: Q35. Overload and timeouts: Chapter 1 and Chapter 2 of this section.

How to read this chapter

The interviewer's favourite break is "the primary database just died". Most candidates say "we fail over to the replica" and stop. That sentence hides five decisions: who decides the primary is dead, which replica wins, how the old primary is stopped, how clients find the new one, and what happens to the writes the replica never got. Then there is the part almost nobody prepares: failback, moving back later, which is a second failover with its own risks.

Part 1 is a short summary of what the reader already covers in depth. Parts 2 to 7 are the thin parts: what an async failover loses, fencing, moving clients, reconciling lost writes, and failback. Every Postgres number in this chapter comes from a real run on Postgres 18: a primary and a streaming replica on one laptop, built with initdb and pg_basebackup, then crashed, promoted, split, rewound and switched back. The commands are shown so you can repeat them.

Part 1: What you must already be able to say

These are covered in depth elsewhere. In the room you need them as one or two sentences each.

• Sync vs async. Async confirms the commit when the WAL is on the primary's disk. A crash loses whatever had not reached the replica yet. Sync waits for a replica, so a confirmed write survives the primary, at the cost of a network round trip per commit and writes that stall when the replica does (SD 2 Ch 4).
• Lag. A replica is always some milliseconds to seconds behind. Under a bulk import it can be minutes behind (SD 2 Ch 5 breaks lag into send, write, flush and replay).
• Failure detection is guessing. A missed heartbeat means "I can't hear it", not "it's dead". The primary may be alive but partitioned, paused by GC, or overloaded.
• Split brain. If you promote while the old primary is alive, you can get two primaries and two histories.
• Majority rules. Consensus systems (Raft, etcd, ZooKeeper) only let a side with a majority act. A primary on the minority side cannot win an election and cannot commit.

The Raft sim shows the election part. Predict first, then watch which follower times out first and how long the cluster has no leader.

Postgres itself has no built-in consensus. It does not decide who is primary; a tool around it does (Patroni with etcd or Consul, a managed service's control plane, or a human). So for Postgres, "leader election" means "the orchestrator holds a lock in a consensus store, and the node it points to is the primary". Keep that in mind for Part 4.

Part 2: What an async failover loses

The number

With async replication, a failover loses every write the primary confirmed that the chosen replica had not received. The size of that window is the lag at the moment of the crash:

writes lost ≈ write rate × lag at the crash
500 confirmed writes/s × 2 s of lag = up to 1,000 confirmed orders gone

Lag is usually tens of milliseconds, which is why teams forget about it. It is large exactly when you are most likely to fail: during a write burst, a long migration, or a network problem between the nodes.

The run

I built a primary and a replica on Postgres 18 (ports 56432 and 56433) and created an orders table with a bigserial id.

Run 1, no artificial lag. A client streamed 21,326 single-row INSERT … RETURNING id statements. After 1.5 s I killed the primary with pg_ctl stop -m immediate (the same as a crash: no clean shutdown). The replica had all 21,326. On one laptop, the replica is a few microseconds behind, so nothing was in flight. That is luck, not a guarantee.

Run 2, a lagging replica. To simulate a slow or broken link, I cut the replica's WAL stream (ALTER SYSTEM SET primary_conninfo = '' on the replica, then reload). Five customers then placed orders. Each INSERT returned its id at once, because with async replication the commit does not wait for the replica:

21352|alice
21353|bob
21354|carol
21355|dan
21356|erin

Then I crashed the primary and promoted the replica:

SELECT pg_promote();          -- returned true in 122 ms
SELECT count(*), max(id) FROM orders;
-- 21326 | 21326              -- all five confirmed orders are gone

The new primary wrote a history file that records exactly where the histories split:

$ cat pg_wal/00000002.history
1	0/352FE60	no recovery target specified

Read it as: "timeline 2 branched off timeline 1 at WAL position 0/352FE60". Anything the old primary wrote after that position exists only on the old primary's disk. Keep that file in mind; Part 6 uses it.

The sim replays the same story with a slider for the lag and a switch for semi-sync. Predict, run the default, then move Replica 1 lag up and run again. Then switch Commit to semi-sync and compare the "Your runs" table.

The other side: sync replication stalls

Sync fixes the loss and adds a new failure. I set synchronous_standby_names = '*' on the primary, so every commit waits for a replica. With the replica up, a commit took 21 ms including psql start-up. Then I stopped the replica and ran one more insert with SET statement_timeout = 3000.

The commit hung for 136 seconds, until I cancelled it by hand. statement_timeout did not end it. pg_stat_activity showed it waiting on SyncRep. When I cancelled it, Postgres printed:

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
21435

The client got the row id back, with a warning that most drivers never show. Two lessons:

1. One sync replica means your write availability equals that replica's availability. Use a quorum of replicas, for example synchronous_standby_names = 'ANY 1 (r1, r2)', so any one of two can confirm. Then losing one replica does not stop writes.
2. A sync commit that times out has an unknown outcome, the same problem as any request timeout (Chapter 2). It may be committed on the primary and missing on the replica. Writes need idempotency keys so the retry is safe.

A common real design: a quorum sync standby in the same region for the money tables' RPO = 0, plus an async replica in another region for disaster recovery, accepting seconds of RPO if a whole region is lost.

Part 3: A failover, step by step

"Fail over to the replica" is six steps. Each one has a time cost, and the sum is your RTO.

Most managed Postgres services do all six for you and quote one to two minutes. That number is mostly steps 1 and 6.

Two details people miss:

• Promotion time depends on replay lag. The replica must apply all the WAL it has received before it takes writes. A replica that received WAL but is 5 minutes behind on replay (for example because a long query on it was holding replay back) needs those minutes before it is usable. Monitor replay lag on the failover candidate, not just on read replicas.
• Pick the replica, don't assume it. In a three-node setup, the orchestrator compares positions and promotes the furthest ahead. The replication-lag sim does this too: it promotes whichever replica has applied more.

Part 4: Fencing, the half that prevents split brain

What happens without it

After Run 2, I started the old primary again. Nothing told it about the promotion. It came up on timeline 1, read-write, with alice to erin still in its table, and accepted a new order:

21385|heidi        -- written to the OLD primary, after the failover

Meanwhile the new primary had handed out ids to new customers:

21352|frank        -- the same id as alice on the old primary
21353|grace        -- the same id as bob

Two primaries, two histories, and the same primary keys meaning different orders. This is split brain, and it was not a theoretical setup: it is what happens when a crashed primary reboots (a power cycle, a cloud host restart) and nothing stops it.

Could the client have protected itself? libpq-based clients (psql, and drivers built on libpq) accept several hosts with target_session_attrs=read-write, which skips servers that are in recovery or read-only. I tried it during the split:

host=localhost,localhost port=56432,56433 target_session_attrs=read-write   → connected to 56432 (old)
host=localhost,localhost port=56433,56432 target_session_attrs=read-write   → connected to 56433 (new)

Both servers were read-write, so the client took whichever was listed first. Client-side checks cannot detect split brain. Only fencing prevents it.

Ways to fence

Patroni, the common Postgres HA tool, uses the lease method: the primary holds a leader key with a TTL in etcd or Consul and demotes itself if it cannot renew it. It can also use a hardware or kernel watchdog, so a hung Patroni process gets the machine reset. Managed services fence inside their control plane.

Fencing tokens

A lease alone has a gap. The old leader can be paused (GC, VM migration) past its lease, wake up, and send one more write before it notices. A fencing token closes the gap at the resource:

-- every leader gets a token from the consensus store: 33, then 34, ...
-- the resource remembers the highest token it has seen and refuses older ones
UPDATE ledger_shard
SET    balance = balance - 4999, fence = 34
WHERE  id = 7 AND fence <= 34;      -- 0 rows if a newer leader (35) already wrote

I ran it: the leader with token 35 wrote first (UPDATE 1), then the stale leader's write with token 34 matched nothing (UPDATE 0). The stale write fails visibly instead of overwriting.

Raft's term is the same idea built into the protocol. Postgres's timeline is a coarser version: a node that kept writing on timeline 1 after the fork cannot stream from the new primary on timeline 2 (Part 6 shows the error). In the Raft sim's partition tab, watch the term: the old leader keeps accepting writes it can never commit, and steps down the moment it sees the higher term.

The rule to say in the room: "Promote only after the old primary is fenced, and have the storage reject a stale leader anyway."

Part 5: Moving the clients

The new primary is up. Your app servers are still holding connections to the old address.

DNS, VIPs and proxies

DNS changes do nothing for connections that are already open. A pool of 20 connections to the old IP keeps using them until each one errors. If the old primary is fenced by power-off, they error quickly. If it is alive but partitioned from the orchestrator and still reachable by the app, those pool connections keep writing to it. That is the split brain from Part 4, reached through the pool.

So the pool needs three settings: a maximum connection lifetime (for example 5–30 minutes, so connections re-resolve DNS regularly), discard on error (a connection that returned a connection error is closed, not reused), and a connect timeout of 1–3 s so a dead host fails fast (Chapter 1).

Draining for a planned move

For a switchover you control the timing, so you can drain instead of cut:

1. Stop new writes: put the app in read-only mode (Chapter 6) or, with PgBouncer, PAUSE so new queries wait in the proxy.
2. Let in-flight transactions finish (a few seconds; set a cap).
3. Confirm the replica has everything (pg_current_wal_lsn() on the primary equals pg_last_wal_replay_lsn() on the replica).
4. Switch, then resume.

The client sees a pause of a second or two, not errors.

Part 6: After an async failover, reconciling the lost writes

The orders alice to erin were confirmed to the customers. They may have received confirmation emails. Their cards may have been charged. Then they vanished from the database. "Some writes may be lost" is not the end of the answer; the interviewer wants to know what you do about them.

Step 1: save the old primary's data before anything overwrites it

The lost writes exist in exactly one place: the old primary's disk. The normal next step, rejoining it as a replica, destroys them. So first, before any rewind or rebuild, copy what only it has. Take a filesystem snapshot of its volume, or start it isolated (fenced, no app traffic) and dump the rows.

Step 2: find the divergent rows. Diff by content, not by key

I dumped orders from both nodes as CSV and compared. My first attempt compared by id and found only 4 of the 6 lost orders. Alice (21352) and bob (21353) were missed, because the new primary had reused those ids for frank and grace. The id existed on both sides, so a key-based diff said "already there".

Comparing whole rows found all 6:

$ comm -23 old.csv new.csv        # rows only on the old primary
21352,alice,4999,2026-10-19 23:17:05.734945+05:30
21353,bob,4999,2026-10-19 23:17:05.750539+05:30
21354,carol,4999,2026-10-19 23:17:05.76597+05:30
21355,dan,4999,2026-10-19 23:17:05.780223+05:30
21356,erin,4999,2026-10-19 23:17:05.794581+05:30
21385,heidi,2500,2026-10-19 23:17:29.867636+05:30

Why were the ids reused? A Postgres sequence writes its state to WAL in batches, ahead of use. The new primary only had the sequence state from before the WAL stream was cut, so it handed out values the old primary had already used. Sequential integer ids collide after an async failover. UUIDs (v4 or the time-ordered v7) or ids from a separate generator avoid the collision. Then a reconciliation is an insert, not a merge.

For a large table you don't diff everything. The history file gives the split point (0/352FE60). Only transactions the old primary committed after that point are candidates. In practice teams use the commit time at the split (from created_at/updated_at columns, or from the WAL with pg_waldump) to narrow the search to the last few seconds.

Step 3: decide row by row, then re-apply

I re-inserted the six on the new primary with new ids and a marker, so support could find them:

INSERT INTO orders (customer, amount_cents, created_at, recovered_from)
SELECT customer, amount_cents, created_at, 'tl1 id ' || id FROM lost ORDER BY id;
-- 21358 alice  tl1 id 21352 ... 21363 heidi  tl1 id 21385

For orders that is safe. For other data it isn't automatic:

Step 4: reconcile with the outside world

The database is not the only record. For each lost order, the payment provider may hold a capture, the email service a sent confirmation, and the warehouse a pick request. The external systems are now the evidence. Compare the payment provider's captures in the window against your orders and refund or re-create as needed. This is much easier if every external call carried an idempotency key that is also stored in your row (Q26): you can match by key.

GitHub's October 2018 incident is the well-known example. A 43-second network partition led to an automated promotion in another data centre, both sides then had writes the other lacked, and the site ran degraded for about a day while those writes were reconciled.

Step 5: rejoin the old primary

Naive rejoin fails. I stopped the old primary, added standby.signal and pointed primary_conninfo at the new primary. It started, but never replicated. Its log repeated:

LOG:  new timeline 2 forked off current database system timeline 1 before current recovery point 0/364C8A8

It had written WAL past the fork point, so it could not follow timeline 2. On the new primary, pg_stat_replication showed it stuck in state startup. A silent failure: the node is up, read-only, and falling further behind.

pg_rewind fixes it by copying back only the blocks that changed since the fork:

$ pg_rewind --target-pgdata=p1 --source-server="host=localhost port=56433 user=postgres" --write-recovery-conf
pg_rewind: servers diverged at WAL location 0/352FE60 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/3416760 on timeline 1
pg_rewind: need to copy 52 MB (total source directory size is 72 MB)
pg_rewind: Done!                                    # 697 ms

After it started, the old node streamed from the new primary, and alice to erin and heidi were gone from it. That is why Step 1 comes first.

Three things the run taught:

• pg_rewind needs data checksums or wal_log_hints = on on the old primary. Postgres 18's initdb turns checksums on by default; older clusters often have neither, and then the only way back is a full pg_basebackup copy, which takes hours for a large database.
• It needs the old primary shut down cleanly first.
• It copies configuration files from the source. My old primary came back with the new primary's port in postgresql.conf. Check config after a rewind.

Part 7: Failback is its own operation

After a failover the old node is rebuilt as a replica. Should you move the primary back?

Why it is risky

Failback is a second change of primary. Everything in Parts 3 to 5 happens again: clients reconnect, pools churn, DNS moves. The difference is that you choose when, so you can make it a planned switchover with zero data loss instead of a failover. The risks come from treating it as "just undo":

• The old node may not be what it was. It was rewound or rebuilt. Has it caught up? Is its buffer cache cold? A cold primary serves the first minutes from disk; p99 can be several times normal.
• The cause may still be there. If the node died from a bad disk, a full volume or a kernel bug, moving back brings it back.
• The old primary's lost writes (Part 6) must be reconciled before it is rewound. A hurried failback skips that.
• The runbook is less practised. Teams test failover. Failback, especially across regions, is often done for the first time during the incident's second day.

When to move back at all

Often, don't. If both nodes are the same size in the same region, the new primary is just the primary now. Fail back when there is a real reason: the original is in the region near your app servers (cross-region writes add 30–150 ms each), it is a bigger instance, or the disaster-recovery site costs more to run as primary.

The planned switchover, measured

I did the failback as a planned switchover, with a client inserting one order every 100 ms through target_session_attrs=read-write, so the client finds whichever node is writable:

The key difference from Part 6: because the primary was shut down cleanly, it sent its final WAL, including the shutdown checkpoint, to the replica before stopping. The replica had everything, the new timeline 3 forked at exactly that point (2 0/3562B28 in the history file), and the old primary rejoined as a replica with no pg_rewind.

The planned procedure:

1. Check the target replica is caught up and warm (run the hot queries on it, or give it read traffic first).
2. Lower DNS TTLs ahead of time if clients use DNS.
3. Pick a low-traffic window. Announce it.
4. Read-only mode on (or proxy PAUSE). In-flight transactions drain.
5. Confirm LSNs match. Stop the primary cleanly.
6. Promote the target. Repoint clients. Read-only off.
7. Start the old primary as a replica. Confirm it streams.
8. Watch error rate and p99 for 30 minutes before calling it done.

Expected cost: a write pause of a few seconds and zero lost writes.

Practising both

An untested failover is a guess about RTO. Make it a routine:

• Do switchovers regularly, for example monthly or as part of every minor version upgrade (upgrade the replica, switch over, upgrade the old primary). Then the "failover" path is exercised for real.
• Run a failover game day in staging, then in production at low traffic: crash the primary for real (stop -m immediate, or stop the VM). Measure each step from Part 3.
• Record these numbers each time: time to detect, time to promote, client error window, confirmed writes lost (compare client-side acknowledged ids against the database, as above), time to rebuild the old primary, and whether the alerts fired and pointed to the right runbook step.
• Practise failback across regions at least once, because that is where the surprises are: replication must be set up in the reverse direction, and nobody has done it in a year.

Chapter 8 covers game days and fault injection in general.

Across regions

Losing a whole region turns these choices into CAP choices. A cross-region async replica means RPO = the cross-region lag (often around a second) and a decision by a human or an orchestrator that can see both sides. In the sim's first tab, a majority quorum spans the cut and the minority side returns errors instead of stale data. Try it, then compare the AP tab, where both sides keep writing and one write is silently discarded on heal.

When the Interviewer Breaks It

Read the interviewer's line, say your answer out loud, then open the model answer.

Break 1: "The primary just died. Walk me through the next 60 seconds."

Break 2: "The replica you promoted was 2 seconds behind. What did you lose?"

Break 3: "The old primary comes back, and some app servers are still writing to it."

Break 4: "Just make replication synchronous, then."

Break 5: "A week later you want to move the primary back. How?"

The Causal Chains

Checklist: add this to any design with a replicated database

• State RPO and RTO per data class. Ledger and payments: RPO 0. Everything else: seconds.
• Sync replication to a quorum for RPO-0 data, async for the rest, an async cross-region replica for disaster recovery.
• An orchestrator that decides through a consensus store, never by a single node's view.
• Detection time chosen from real pause lengths (10–30 s), not "as fast as possible".
• Promote the most caught-up replica, by LSN. Alert on replay lag of the failover candidate.
• Fencing before promotion (STONITH, network fence or lease with watchdog), and fencing tokens where a stale leader could write.
• Client pools: connect timeout 1–3 s, discard on error, max connection lifetime.
• Idempotency keys on writes, so retries during and after failover are safe.
• Ids that don't collide after a failover (UUID v7) for tables you would reconcile.
• A lost-write runbook: snapshot the old primary first, diff by content, re-apply, reconcile with payment and email providers.
• pg_rewind prerequisites in place (checksums or wal_log_hints) so the old primary can rejoin in seconds, not hours.
• Failback written as a planned switchover with an abort step.
• Routine switchovers and a failover game day, with measured RTO and RPO.

What's next

Chapter 6 — Graceful Degradation: what users still get while the database is failing over, the cache is gone, or a dependency is slow. Read-only mode, which this chapter used for the switchover, is built there.