Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-split-brain~50 min

Two databases accepted writes for fifty minutes, and resolving it discarded a hundred and twenty committed orders

Reported symptoms

  • ●A network partition at 02:40 isolated the primary from the monitoring network but not from the application network
  • ●The failover automation promoted the standby at 02:42 after failing to reach the primary
  • ●The old primary never stopped and continued accepting writes from the application instances that could still reach it
  • ●Both clusters answered pg_is_in_recovery() with false for the next fifty minutes
  • ●Customers reported orders that had disappeared, and other customers reported orders that had never been confirmed
  • ●Reporting totals from the two servers disagree by a hundred and twenty orders
  • ●Neither server logged anything unusual - each believed it was the only primary

Evidence

  • · The old primary shows pg_is_in_recovery() false, zero rows in pg_stat_replication, and 620 rows in the affected table
  • · The new primary shows pg_is_in_recovery() false, zero rows in pg_stat_replication, and 800 rows in the same table
  • · Five hundred rows are identical on both servers; beyond that the tails diverge completely
  • · The old primary holds 120 rows written after the promotion; the new primary holds 300 rows written after the promotion
  • · The new primary is on timeline 2 and the old primary is still on timeline 1
  • · pg_wal/00000002.history on the new primary reads 1 0/43CCB2E0 no recovery target specified
  • · pg_rewind refused to run against the old primary until it had been cleanly shut down
  • · After pg_rewind and rejoin, the old primary held 800 rows and the 120 OLD-PRIMARY rows were gone
Diagnosis and resolutionclick to reveal

Root cause

Two clusters accepted writes at the same time because nothing stopped the first one. Promotion in PostgreSQL is a **local decision**. When `pg_promote()` runs, the standby selects a new timeline and begins accepting writes. Nothing consults the old primary. Nothing informs it. The old primary has no way to learn that a promotion has occurred, and there is no PostgreSQL setting that would have prevented this. Preventing it is the job of the layer above — fencing — and PostgreSQL does not provide one. The failover automation here promoted on the basis of a failed health check and never verified that the old primary had actually stopped. The partition affected the monitoring network and not the application network, so from the application's point of view both servers were reachable and both were accepting writes. What follows is not recoverable in the way people hope. The two clusters share 500 rows of common history and then diverge: 120 rows exist only on the old primary, 300 only on the new. There is no merge. Row identities collide, sequences have issued the same values on both sides, and no tool reconciles two divergent PostgreSQL timelines — because the WAL that describes them is mutually incompatible from the divergence point onward. Resolving a split brain therefore means **choosing a survivor and discarding the other side's work**. `pg_rewind` implements exactly that: it rewinds the diverged node to the last common checkpoint and replays forward along the winning timeline. Measured on a deliberately created split brain, the old primary ended with 800 rows and the 120 rows written to it after the promotion were gone — committed, acknowledged to clients, and discarded. That is not a defect in `pg_rewind`. Rejoining a diverged node means discarding its divergence. Whichever direction you resolve it in, one side's committed work is lost. This is the cost of split brain, and it is why fencing matters more than recovery.

Remediation

Stop the writes first, and stop them at the application layer. Every second that both servers remain writable adds work to whichever side will be discarded. This is more urgent than deciding which server wins: - Withdraw the route, the DNS entry or the virtual IP that reaches the losing server. - Or reconfigure the pooler to reject connections to it. - Or, if nothing else is available, shut down the loser's PostgreSQL service. Then establish the extent of the divergence before touching either server. This is evidence-gathering, and once `pg_rewind` runs it cannot be repeated: ```sql -- on each server SELECT pg_is_in_recovery(), timeline_id FROM pg_control_checkpoint(); SELECT count(*) FROM pg_stat_replication; ``` ```bash cat /var/lib/postgresql/18/main/pg_wal/*.history ``` Take a full copy of the losing server's data directory, or at minimum a `pg_dump` of the affected tables, **before** rewinding. Once `pg_rewind` completes, the divergent rows exist nowhere. A dump is the only route by which any of that work can be reconciled by hand afterwards, and reconciling by hand is frequently the right answer for a hundred and twenty orders. Choose the survivor deliberately, and say why. Usually the new primary wins because the application has been writing to it for longer and its divergence is larger — 300 rows against 120 here. That is an argument, not a rule, and somebody should make it explicitly. Rejoin the loser with `pg_rewind`. It requires a **cleanly shut down** target and will refuse otherwise: ```bash pg_ctl -D /var/lib/postgresql/18/main -m fast stop pg_rewind --target-pgdata=/var/lib/postgresql/18/main \ --source-server='host=new-primary user=repl' -P ``` It reports where the servers diverged and where it is rewinding from: ```text pg_rewind: servers diverged at WAL location 0/43CCB2E0 on timeline 1 pg_rewind: rewinding from last common checkpoint at 0/43C807B8 on timeline 1 ``` `pg_rewind` needs the source to have `full_page_writes` on and either a superuser connection or a role with the four required file-access function grants. It also needs the WAL from the divergence point to still exist; if segments have been recycled, `pg_rewind` fails and you must rebuild the node from a base backup instead. Then reconcile by hand from the dump you took. A hundred and twenty orders is a spreadsheet and an afternoon, and it is the only part of this incident where the lost work can be recovered at all.

Verification

Exactly one server reports `pg_is_in_recovery()` as `false`. Check every node, including any you believe is stopped: ```sql SELECT pg_is_in_recovery(); ``` The rejoined node appears in `pg_stat_replication` on the survivor, streaming: ```sql SELECT application_name, state, sync_state, replay_lsn FROM pg_stat_replication; ``` Row counts match between the two nodes for the tables involved. After the measured rewind the old primary held 800 rows, identical to the new primary. The discarded work is enumerated and its fate is recorded. This is a reporting obligation, not a technical one: a hundred and twenty acknowledged orders were lost, and somebody outside the database team needs to know which. A write on the survivor appears on the rejoined node within seconds. **Fencing is tested.** Trigger a promotion in a rehearsal without stopping the old primary first, and confirm that the fencing mechanism makes the old primary unreachable or unwritable. If it does not, this incident is still possible and nothing about the recovery has changed that.

Prevention

**Fence before promoting. Always.** The promotion must not begin until the old primary is provably unable to accept writes — powered off, its network withdrawn, its storage detached, or its service stopped. "We could not reach it" is not fencing; it is the condition that makes fencing necessary. **Understand that PostgreSQL will not prevent this.** There is no setting. Promotion is local, the old primary is not informed, and neither node can detect the other. Any belief that the database will refuse a second primary is mistaken and dangerous. **Do not promote on a failed health check alone.** A monitoring network partition looks identical to a dead primary from the monitor's position and is a completely different situation. Require a second, independent signal, or require fencing to succeed before promotion proceeds. **Use a failover manager that fences**, and read what its fencing actually does. A manager that promotes and hopes is a split-brain generator with a good user interface. **Alert on more than one node reporting `pg_is_in_recovery() = false`.** It is a trivial check across an inventory of nodes and it is the only direct detection of this condition. Neither server logs anything unusual; each believes it is alone. **Take a copy before running `pg_rewind`.** It is the last moment at which the discarded work exists. **Rehearse split brain, deliberately, in a test environment.** Create one, resolve it, count what was lost, and write the number down. A team that has done this once will fence properly. **Design the application to tolerate the loss.** Idempotent writes with client-supplied identifiers, and an audit trail outside the database, turn "a hundred and twenty orders vanished" into "a hundred and twenty orders can be replayed".

Reported symptoms

A network partition at 02:40 isolated the primary from the monitoring network — but not from the application network.

The failover automation promoted the standby at 02:42 after failing to reach the primary. The old primary never stopped, and continued accepting writes from the application instances that could still reach it.

Both clusters answered pg_is_in_recovery() with false for the next fifty minutes.

Customers reported orders that had disappeared, and other customers reported orders that had never been confirmed. Reporting totals from the two servers disagree by a hundred and twenty orders.

Neither server logged anything unusual. Each believed it was the only primary.

Evidence provided

Read-only / Safetwo primaries, 500 rows in common, divergent tails
$ psql -c "SELECT origin, count(*) FROM ha GROUP BY origin;"   # on each server
--- old primary (timeline 1) ---      --- new primary (timeline 2) ---
 origin    | count                    origin    | count
-------------+-------                -------------+-------
OLD-PRIMARY |   120                 NEW-PRIMARY  |   300
primary     |   500                 primary      |   500
Read-only / Safeand neither knows the other exists
$ psql -c "SELECT pg_is_in_recovery();" -c "SELECT count(*) FROM pg_stat_replication;"
old primary: pg_is_in_recovery -> f    pg_stat_replication -> 0 rows
new primary: pg_is_in_recovery -> f    pg_stat_replication -> 0 rows

The new primary’s timeline history file:

1	0/43CCB2E0	no recovery target specified

Work the evidence before reading on

  1. Which PostgreSQL setting would have prevented two primaries?
  2. Can the 120 rows and the 300 rows be merged?
  3. pg_rewind will rejoin the old primary. What happens to its 120 rows?
  4. The partition affected monitoring and not the application network. Why does that detail matter more than any other?

Root cause

Nothing stopped the first primary

The automation promoted on a failed health check and never verified that the old primary had stopped. The partition affected the monitoring network and not the application network, so from the application’s point of view both servers were reachable and both were writable.

There is no merge

The two clusters share 500 rows of common history and then diverge. Row identities collide, sequences have issued the same values on both sides, and no tool reconciles two divergent PostgreSQL timelines — the WAL describing them is mutually incompatible from the branch point onward.

Resolution

Establish the extent of the divergence before touching either server. Once pg_rewind runs this cannot be repeated:

-- on each server
SELECT pg_is_in_recovery(), timeline_id FROM pg_control_checkpoint();
SELECT count(*) FROM pg_stat_replication;
cat /var/lib/postgresql/18/main/pg_wal/*.history

Take a full copy of the losing server’s data directory, or at minimum a pg_dump of the affected tables. Once pg_rewind completes, the divergent rows exist nowhere. A dump is the only route by which any of that work can be reconciled by hand — and for a hundred and twenty orders, reconciling by hand is frequently the right answer.

Choose the survivor deliberately and say why. Usually the new primary wins because the application wrote to it longer and its divergence is larger — 300 rows against 120 here. That is an argument, not a rule.

Rejoin the loser with pg_rewind, which requires a cleanly shut down target:

pg_ctl -D /var/lib/postgresql/18/main -m fast stop
pg_rewind --target-pgdata=/var/lib/postgresql/18/main \
          --source-server='host=new-primary user=repl' -P
Data-loss riskpg_rewind naming the branch point
$ pg_rewind --target-pgdata=... --source-server=... -P
pg_rewind: servers diverged at WAL location 0/43CCB2E0 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/43C807B8 on timeline 1

pg_rewind needs full_page_writes on at the source and either a superuser connection or a role holding the four required file-access function grants. It also needs the WAL from the divergence point to still exist — if segments have been recycled it fails, and the node must be rebuilt from a base backup instead.

Then reconcile by hand from the dump you took. That is the only part of this incident where the lost work can be recovered at all.

Verification

Exactly one server reports pg_is_in_recovery() as false. Check every node, including any you believe is stopped.

The rejoined node appears in pg_stat_replication on the survivor, streaming:

SELECT application_name, state, sync_state, replay_lsn FROM pg_stat_replication;

Row counts match. After the measured rewind, the old primary held 800 rows, identical to the new primary.

The discarded work is enumerated and its fate recorded. This is a reporting obligation, not a technical one: a hundred and twenty acknowledged orders were lost, and somebody outside the database team needs to know which.

A write on the survivor appears on the rejoined node within seconds.

Fencing is tested. Trigger a promotion in a rehearsal without stopping the old primary, and confirm the fencing mechanism makes it unreachable or unwritable. If it does not, this incident is still possible and nothing about the recovery has changed that.

Prevention

Fence before promoting. Always. The promotion must not begin until the old primary is provably unable to accept writes — powered off, network withdrawn, storage detached, service stopped. “We could not reach it” is not fencing; it is the condition that makes fencing necessary.

Understand that PostgreSQL will not prevent this. Any belief that the database will refuse a second primary is mistaken and dangerous.

Do not promote on a failed health check alone. A monitoring partition looks identical to a dead primary from the monitor’s position and is a completely different situation. Require a second independent signal, or require fencing to succeed first.

Use a failover manager that fences, and read what its fencing actually does. A manager that promotes and hopes is a split-brain generator with a good user interface.

Alert on more than one node reporting pg_is_in_recovery() = false. It is trivial across a node inventory, and it is the only direct detection — neither server logs anything unusual.

Take a copy before running pg_rewind. It is the last moment at which the discarded work exists.

Rehearse split brain in a test environment. Create one, resolve it, count what was lost, write the number down. A team that has done this once will fence properly.

Design the application to tolerate the loss. Idempotent writes with client-supplied identifiers, and an audit trail outside the database, turn “a hundred and twenty orders vanished” into “a hundred and twenty orders can be replayed”.