Drill 10 — The Primary Database Dies

The three states. A transaction that never reached COMMIT (1043) is rolled back: nothing to do. A transaction whose COMMIT was sent but never answered (1042) is unknown: it may have committed on the primary and may or may not have reached the replica. The dangerous group is 1041: the charge succeeded, the order is pending, and the customer saw a 500. Little's law says about 120 checkouts are in that window at any moment.

Why the recovery time isn't 20 s. Detection (3 × 5 s) plus promotion is about 20 s. DNS adds up to 30 s for new connections. But the pods' existing pooled connections point at a host that no longer answers. No RST comes back, so a query on them waits for TCP to give up, which on Linux defaults takes about 15 minutes of retransmissions. The database failed over in 20 s; the application could take 15 minutes.

Detect. Connection errors and query timeouts on every pod; the watchdog's failed pings; checkout success rate below 99%; pending orders older than 2 minutes climbing.

Contain. Make the app fail over as fast as the database: query_timeout and statement_timeout of 5 s on the pg client, TCP keepalives on, connectionTimeoutMillis: 2000 on the pool, and evict a connection from the pool on any network error. Then a dead primary costs each request at most 5 s, and new connections go to the new primary.

Degrade. Checkout returns "we couldn't confirm your payment yet, we'll email you" instead of a bare 500 when transaction 2 fails after a successful charge. The order stays pending for reconciliation. Don't tell the customer to try again.

Recover. A reconciliation job runs every minute: for every order pending for more than 2 minutes, ask the provider for the charge by idempotency key. Charged → mark paid, send the email. Not charged → cancel and release the stock. The provider's webhook does the same thing faster; the job is the safety net for missed webhooks.

Prevent. Model the checkout as a state machine with every state stored before the next side effect: pending → charging → paid (or failed). Write charging before calling the provider, so a crash in between always leaves a row that says "go and ask the provider". Use an idempotency key on the charge that doesn't depend on the database (see the follow-up and Break 2).

An idempotency key created before the first attempt: the checkout page gets a checkout_session_id when it loads, and both attempts send it. The server does INSERT INTO orders (..., idempotency_key) ... ON CONFLICT (idempotency_key) DO NOTHING RETURNING id; if the row already exists, it returns that order's current state instead of starting again. The same key goes to the payment provider, and most payment APIs return the original charge for a repeated key instead of charging again (Stripe, for example, keeps keys for at least 24 hours). The second attempt shows "paid". Details in Chapter 2 and Q26.

Note why the key must come from the client session, not from the order's sequence id: Break 2 shows that a sequence can hand out the same number twice after a failover.

What you lost. With async replication the primary says "OK" as soon as the commit is on its own disk and ships the WAL afterwards. Everything in the last 2 s of WAL existed only on the dead primary: 2 s × 300/s = 600 checkouts, nearly all already charged, about $36,000 taken for orders the database no longer has. Some of their confirmation emails may already have been sent by the outbox relay. In the sim the default 300 ms lag lost 3 confirmed orders; 2 s lost about 14 of the 37 it confirmed.

Which lag is loss. write_lag and flush_lag measure WAL that hasn't reached the replica's disk: that is the data you lose. replay_lag measures WAL that the replica has received but not yet applied. That WAL is not lost: on promotion, Postgres finishes replaying everything it has received before it accepts writes. I checked this on Postgres 18: with replay paused on the replica, three rows written on the primary were missing from replica reads, but after an immediate shutdown of the primary and pg_promote() on the replica, all three were there. Replay lag costs you recovery time, not data. A reporting query on a replica can hold replay back for up to max_standby_streaming_delay (30 s by default), which is one reason the reporting replica shouldn't be a failover candidate.

Detect. On the primary, pg_stat_replication: alert when flush_lag passes 500 ms for a minute. At failover time, record the old primary's last known LSN from monitoring and the promoted replica's received LSN: the difference is the size of the hole.

Contain. Promote the most caught-up replica, never a fixed one (the sim does this). If the old primary's disk survives, start it isolated, with no application access, and copy out the rows written after the fork point before anyone reuses that machine.

Degrade. Checkout stays up on the new primary. The 600 customers' orders are gone from the database but their charges exist at the provider.

Recover. List the provider's charges created in the window (crash time minus 5 s to crash time), match them to orders by checkout_session_id, and for each charge with no order, either recreate the order from the provider's metadata and the salvaged rows, or refund it. This only works if every charge carries the session id as metadata.

Prevent. Synchronous replication for the checkout database: synchronous_commit = on with synchronous_standby_names = 'ANY 1 (replica1, replica2)'. A commit returns only when at least one standby has flushed it, so an acknowledged order survives the loss of any one node: RPO = 0 for a single failure. The cost is one cross-zone round trip per commit, typically 1–2 ms inside a region, and you must decide what happens when both standbys are down: block writes (stay consistent) or fall back to async (stay available). Say which you'd choose for payments: block, and alert.

Sequences repeat after an async failover. The lost 2 s of WAL included sequence advances. The new primary only knows the sequence values that reached it, so it hands out order IDs the old primary already used: the 600 lost checkouts' IDs are issued again to new customers. Postgres pre-logs sequence values in batches of 32, which hides small gaps but not 600. If the provider's idempotency key was order-<id>, a new customer's charge with key order-1041 collides with the lost order's charge, and the provider returns the old customer's charge instead of charging the new one. Fix: idempotency keys and external references come from the client session or a UUID, never from a database sequence. If you need ordered IDs, put a node or epoch component in them.

What the sim shows. S1 accepted a write but only S1 and S2 had it, 2 of 5, so it never committed and no client was told it succeeded. The majority side elected a new leader in a higher term and committed its writes. On heal, S1 saw the higher term, stepped down, and its uncommitted entry was overwritten. Plain Postgres with a watchdog has none of this: a primary commits on its own disk and doesn't ask anyone. The old primary must be stopped from outside, or must stop itself.

What's happening in the baseline. Two primaries for the whole time nobody notices. The 10 AZ-a pods (a third of the traffic, 100 checkouts/s) write to the old one; their pooled connections never re-resolve DNS, so the DNS change doesn't reach them. Over the 40 s partition plus 3 minutes until a human notices, that's 100 × 220 ≈ 22,000 orders on a primary that will have to be thrown away (Break 4). Both sides hand out the same order IDs: I reproduced this with two local Postgres 18 servers after a promotion, and both returned id = 34 for their next insert. Stock is decremented independently on each side, so the last unit sells twice.

Detect. Alert when more than one node reports pg_is_in_recovery() = false. That alert must come from a monitor that can reach both sides, ideally from a third zone.

Contain. Fence the old primary at once: block its port with the security group or power it off through the cloud API ("STONITH", shoot the other node in the head). Then the AZ-a pods' connections fail and they reconnect to the real primary.

Degrade. For the minutes it takes, accept that a third of checkouts fail rather than land on the wrong primary. A failed checkout can be retried; an order on the wrong primary has to be reconciled by hand.

Recover. Break 4.

Prevent. Replace the watchdog with an HA manager that uses a consensus store (etcd or similar, with 3 members in 3 zones) and a leader lease:

• The primary's agent must renew the lease, say every 10 s with a 30 s TTL. If it can't reach a majority of etcd, it demotes its own Postgres to read-only before the lease runs out. Patroni works this way.
• A replica is promoted only after the old lease has expired, so two lease holders never overlap.
• Applications connect through a proxy (HAProxy or PgBouncer) that routes only to the node whose health endpoint says "I hold the lease". A node that comes back unfenced gets no traffic.
• On restart, the old primary starts as a replica, never as a primary.

This is the same rule as the sim: a leader that can't reach a majority stops acting as leader.

Yes, and it is the price of never having two primaries. Recovery time becomes roughly detection + lease expiry + promotion, about 30–45 s, and the availability budget of 22 minutes a month can afford that a few times. A shorter TTL (say 10 s) recovers faster but fails over on every network blip of 10 s, and each failover has its own risk (Break 4). Two more details interviewers like: the lease relies on the nodes' clocks running at roughly the same rate, so use monotonic timers and give the old leader a safety margin (demote at TTL minus a few seconds); and connection draining matters, because pods still have to notice the switch, so keep the 5 s query timeouts from Break 1.

Why it can't just rejoin. The old primary's WAL went further than the point where the new primary's timeline forked off: it has the split-brain writes (or, after Break 2, the lost 2 s). When I started a diverged Postgres 18 primary as a standby of the promoted one, it looped on "new timeline 2 forked off current database system timeline 1 before current recovery point" and never streamed.

pg_rewind throws the diverged writes away. It copies the changed blocks back from the new primary so the old node matches the fork point. In my test, a row inserted only on the old primary was gone after the rewind. So the order is fixed: extract first, rewind second. With the node isolated, run COPY (SELECT * FROM orders WHERE created_at >= :fork_time) TO ... for every table checkout writes, and hand those rows to reconciliation: re-insert them on the real primary with new IDs (the old ones collide), check each against the provider's charges, and contact customers whose stock was sold twice.

Three practical details. pg_rewind needs wal_log_hints = on or data checksums, set before the incident. It needs the old node's WAL back to the last checkpoint before the fork: in my first attempt that WAL had been recycled and it failed with "could not find previous WAL record", so keep enough WAL or a WAL archive it can read. And it copies the source's configuration files over the target's (my rewound node came up on the new primary's port), so check the config before starting it.

Detect / contain. Before you start: the old node is fenced, the alert from Break 3 is quiet, and the reconciliation export is saved somewhere other than the node itself.

Degrade. The switchover needs a short write pause: checkout returns "try again in a moment" (or queues the request client-side) for about 20 s. At 4 am the rate is about 20 checkouts/s, so roughly 400 customers see the message, instead of 6,000 at peak.

Recover. Rewind, stream until lag is zero, pause writes, wait for replay LSN = primary LSN, promote the old node, repoint the proxy, demote the AZ-b node to a replica, resume writes. Then watch for an hour.

Prevent. The real lesson is to not need failback at all: make every failover candidate the same size as the primary, so promotion leaves you on a machine you can stay on. Then failback becomes optional and can wait for a quiet week. And practise this whole sequence on a staging copy every quarter, timed; an unpractised failback is an unplanned outage you schedule yourself.

The WAL it needs was recycled. Two options. If you archive WAL (you should, for point-in-time recovery, Chapter 7), point pg_rewind at the archive with --restore-target-wal so it fetches the missing segments through restore_command. If not, rebuild the old node from scratch with pg_basebackup from the new primary. For a 2 TB database at about 500 MB/s that is roughly 70 minutes, plus catch-up. Either way, the extract from the old node must happen before you rebuild it, because a base backup overwrites it completely. And it's 2 pm: the rebuild can run now, but the switchover still waits for 4 am.

What the sim shows. With a majority quorum, Client 2 on the minority side got an error for both its read and its write, and nobody ever read the old balance after the new one was confirmed. With "any replica", Client 2's write succeeded and its read returned the stale value 0. For checkout the CP behaviour is the right one: the side without a majority must refuse writes, or you get two primaries across regions.

Where the data is. In the baseline, nowhere outside the region. Add a cross-region async replica and ship WAL and base backups to storage in another region. Not synchronous: a round trip between regions is about 70 ms, and a commit holds its row locks until it returns. A hot stock row (the Black Friday deal SKU) can then take at most about 1,000 / 72 ≈ 14 updates per second, against 200 purchases per second on that SKU. Async costs you the cross-region lag instead: at a typical 0.5 s, about 0.5 × 300 = 150 checkouts at risk, which you reconcile with the provider exactly as in Break 2.

Detect. Probes from outside the region (another region, a third-party monitor). The internal monitoring is in the dead region.

Contain. A human decides. Region failover is a business decision with a runbook, not an automatic action, because a false positive (a broken monitoring path, not a dead region) creates split brain between regions. Make the decision mechanical anyway: the runbook says "fail over if the region has been unreachable from two independent vantage points for 10 minutes". Before promoting, fence the old region at the edge: DNS and the load balancer stop sending it traffic, and the old primary's credentials are revoked, so that if it comes back it can't take writes.

Degrade. While the decision is being made, checkout can run in an accept-and-confirm-later mode: the surviving region writes the order request to a durable queue and shows "order received, confirmation by email". That is an availability choice (AP) with a known risk: oversold stock. Limit it to products with more than 50 units in stock, and charge only when the order is confirmed.

Recover. Promote the cross-region replica (RTO: the 10-minute decision, plus a few minutes to promote and repoint), drain the queue, reconcile the at-risk window with the provider.

Prevent. Rehearse region failover twice a year in a game day. If the business needs a lower RTO, keep an etcd voter in a third region so the quorum can decide automatically which side holds the lease, with the same rule as Break 3.

No. Treat it exactly like Break 4, at a larger scale: keep the old region fenced, start its primary isolated, extract the rows after the fork point (about 0.6 × 300 = 180 checkouts) for reconciliation, then rebuild it as a replica of the new region with pg_rewind or a fresh base backup. Fail back only if there is a reason (cost, latency to most customers, the provider's allowlisted IPs) and only in a planned, rehearsed window. Two failovers in one day doubles your chance of a mistake for no gain.