Lab 24: Promote a replica, create a split brain, then rejoin the old primary and lose its writes

Objective

Promotion is easy — 151 milliseconds in this lab run. Everything after it is the hard part.

By the end of this lab you will have promoted a standby, deliberately written to both servers so that each holds transactions the other has never seen, and then rejoined the old primary with pg_rewind — after which you will count exactly how many of its committed transactions ceased to exist.

You will also meet a pg_rewind that simply could not run, twice, for two different reasons. Both are conditions you will meet in production and neither is documented anywhere you will think to look during an incident.

Architecture

flowchart TD
    A["shared history\n100 rows, timeline 2"] --> P["promote the standby"]
    P --> T3["standby becomes primary\ntimeline 3"]
    A --> O["old primary keeps running\ntimeline 2"]
    T3 --> N["writes 250 rows\nNEW-PRIMARY-only"]
    O --> W["writes 60 rows\nOLD-PRIMARY-only"]
    N --> S["SPLIT BRAIN\nneither sees the other"]
    W --> S
    S --> R["pg_rewind old -> new"]
    R --> F["old primary is a standby again\nits 60 rows are GONE"]

Requirements

A primary with a streaming standby. The lab uses rbpg-sb and a standby built as in Lab 21.
Superuser access to both, and the ability to stop either.
On the target of a rewind, either data_checksums on or wal_log_hints on. PostgreSQL 18 enables checksums by default, so this is usually already satisfied.

Scenario

The primary became unreachable, you promoted the standby, and the application recovered. Twenty minutes later the old primary comes back — still believing it is the primary, still holding twenty minutes of writes that reached it before the network partition.

You need to make it a standby of the new primary. The question the lab answers is what that costs.

Tasks

Task 1 — Establish shared history

LAB="$HOME/rbpg-lab-24"
mkdir -p "$LAB"
PRIM_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' rbpg-sb)

docker exec -u postgres rbpg-sb psql -X -c "SELECT pg_create_physical_replication_slot('lab24_slot');"
docker exec -u postgres rbpg-sb psql -X -c "CHECKPOINT;"

docker exec -u postgres -e PGPASSWORD=replpass rbpg-lab21 \
  pg_basebackup -h "$PRIM_IP" -U repl -D /var/lib/postgresql/sb -Fp -Xstream -c fast -R -S lab24_slot
docker exec rbpg-lab21 bash -c "chmod 0700 /var/lib/postgresql/sb
  echo \"cluster_name = 'lab24-standby'\" >> /var/lib/postgresql/sb/postgresql.auto.conf"
docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb -l /tmp/sb.log start
sleep 5

docker exec -i -u postgres rbpg-sb psql -X -d lab21 <<'SQL'
DROP TABLE IF EXISTS ha;
CREATE TABLE ha(id serial primary key, who text);
INSERT INTO ha(who) SELECT 'shared-history' FROM generate_series(1,100);
SQL
docker exec -u postgres rbpg-sb psql -X -c "CHECKPOINT;"
sleep 3
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT application_name, state FROM pg_stat_replication WHERE application_name='lab24-standby';"

Configuration changea standby streaming, with 100 rows both servers agree on— Captured from the lab run of 2026-08-28.

$ build and start the standby, create shared data, checkpoint

 application_name |   state   
------------------+-----------
lab24-standby    | streaming
(1 row)

The CHECKPOINT before the promotion is not decoration. Task 6 shows what happens without it, and it is the difference between a rejoin that takes a minute and a rebuild that takes hours.

Task 2 — Promote

docker exec -u postgres rbpg-lab21 psql -X -c "SELECT pg_promote(wait => true, wait_seconds => 60);"
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT pg_is_in_recovery();"
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT timeline_id FROM pg_control_checkpoint();"
docker exec rbpg-lab21 grep -E "received promote|redo done|selected new timeline|recovery complete" /tmp/sb.log \
  | tee "$LAB/promotion.txt"

Service impact possiblepromotion in 151 milliseconds, onto timeline 3— Captured from an earlier promotion in the lab run of 2026-08-28.

$ pg_promote with wait, then check recovery state, timeline and log

pg_promote returned after 151 ms

pg_is_in_recovery 
-------------------
f
(1 row)

timeline_id 
-------------
         2
(1 row)

2026-08-28 06:25:01.318 UTC [301] LOG:  received promote request
2026-08-28 06:25:01.318 UTC [301] LOG:  redo done at 0/7D033650
2026-08-28 06:25:01.325 UTC [301] LOG:  selected new timeline ID: 3
2026-08-28 06:25:01.346 UTC [301] LOG:  archive recovery complete
2026-08-28 06:25:01.352 UTC [295] LOG:  database system is ready to accept connections

Promotion is fast because there is nothing to do: the standby has already replayed the WAL. It writes a timeline history file, ends recovery, and opens for writes.

docker exec rbpg-lab21 bash -c "ls /var/lib/postgresql/sb/pg_wal/*.history; cat /var/lib/postgresql/sb/pg_wal/*.history"

Read-only / Safethe branch record, one line per promotion in this cluster's history— Captured from the lab run of 2026-08-28. This cluster had been promoted once before.

$ list and cat the timeline history files

/var/lib/postgresql/sb/pg_wal/00000002.history
/var/lib/postgresql/sb/pg_wal/00000003.history

1	0/43CCB2E0	no recovery target specified

1	0/43CCB2E0	no recovery target specified
2	0/7D033688	no recovery target specified

00000003.history contains the whole ancestry: timeline 1 ended at 0/43CCB2E0, timeline 2 ended at 0/7D033688, and timeline 3 continues from there. That second LSN is the divergence point, and pg_rewind needs it.

Task 3 — Split brain

docker exec -u postgres rbpg-sb psql -X -d lab21 -c \
  "INSERT INTO ha(who) SELECT 'OLD-PRIMARY-only' FROM generate_series(1,60);"
docker exec -u postgres rbpg-lab21 psql -X -d lab21 -c \
  "INSERT INTO ha(who) SELECT 'NEW-PRIMARY-only' FROM generate_series(1,250);"

docker exec -u postgres rbpg-sb    psql -X -d lab21 -c "SELECT who, count(*) FROM ha GROUP BY who ORDER BY who;"
docker exec -u postgres rbpg-lab21 psql -X -d lab21 -c "SELECT who, count(*) FROM ha GROUP BY who ORDER BY who;" \
  | tee "$LAB/split-brain.txt"
docker exec -u postgres rbpg-sb    psql -X -c "SELECT count(*) AS standbys FROM pg_stat_replication;"
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT count(*) AS standbys FROM pg_stat_replication;"

Data-loss risktwo servers, two histories, no connection between them— Captured from the lab run of 2026-08-28.

$ write different rows to each server, then compare

old primary (rbpg-sb):
     who        | count 
------------------+-------
OLD-PRIMARY-only |    60
shared-history   |   100

new primary (rbpg-lab21):
     who        | count 
------------------+-------
NEW-PRIMARY-only |   250
shared-history   |   100

standbys 
----------
      0

standbys 
----------
      0

Both servers accept writes. Both report zero standbys. Both are behaving correctly and neither is aware the other exists.

Task 4 — pg_rewind’s prerequisites, discovered the hard way

NEW_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' rbpg-lab21)
PGD=$(docker exec -u postgres rbpg-sb psql -X -tAc "SHOW data_directory;")

docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT name, setting FROM pg_settings WHERE name IN ('wal_log_hints','data_checksums');"

docker exec -u postgres rbpg-sb /usr/lib/postgresql/18/bin/pg_rewind \
  --target-pgdata="$PGD" \
  --source-server="host=$NEW_IP port=5432 user=repl password=replpass dbname=postgres" \
  --dry-run

Read-only / Safea running target, and then a permissions failure— Captured from the lab run of 2026-08-28. Note pg_rewind started the target to complete crash recovery first.

$ pg_rewind --dry-run against a running target

       name       | setting 
------------------+---------
data_checksums   | on
full_page_writes | on
wal_log_hints    | off

pg_rewind: executing "/usr/lib/postgresql/18/bin/postgres" for target server to complete crash recovery
pg_rewind: error: could not fetch remote file "global/pg_control": ERROR:  permission denied for function pg_read_binary_file

Two things surfaced.

data_checksums = on satisfies the requirement that wal_log_hints or checksums be enabled — PostgreSQL 18’s default made this one free.

And --source-server needs more than a REPLICATION role. pg_rewind reads files from the source through SQL functions, and a plain replication user cannot execute them:

docker exec -i -u postgres rbpg-lab21 psql -X <<'SQL'
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO repl;
GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO repl;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO repl;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO repl;
SQL

Task 5 — And a rewind that cannot run at all

With the grants in place, the dry run got further and then stopped:

Service impact possiblepg_rewind found the divergence and could not walk back to it— Captured from the lab run of 2026-08-28, on a first attempt without a checkpoint before the promotion.

$ pg_rewind --dry-run with the grants in place

pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/7D033688 on timeline 2
pg_rewind: error: could not open file "/var/lib/postgresql/18/docker/pg_wal/00000002000000000000007C": No such file or directory
pg_rewind: error: could not find previous WAL record at 0/7C000158

pg_rewind needs to read the target’s WAL from the last common checkpoint forward to the divergence point, to learn which blocks the target changed. The last common checkpoint was at 0/7C000158, inside segment ...7C, and both servers had already recycled it.

docker exec rbpg-lab21 bash -c "ls /var/lib/postgresql/sb/pg_wal/ | grep 7C || echo 'not present on the source either'"

Read-only / Safethe segment is gone everywhere— Captured from the lab run of 2026-08-28.

$ look for the required segment on the source

not present on the source either

Two mitigations, and one outcome if you have neither

A checkpoint immediately before promotion moves the last common checkpoint right up to the divergence point, so the WAL pg_rewind needs is a few segments at most. A failover manager should issue one; an operator promoting by hand should too, when the old primary is reachable.

--restore-target-wal lets pg_rewind fetch missing segments using the target’s restore_command. If you have a WAL archive (Lab 19), this removes the problem entirely, and it is the strongest practical argument for archiving on a cluster that already has streaming replication.

With neither, the answer is a full rebuild: pg_basebackup from the new primary, which on a large database is hours rather than minutes. That is a real outcome, not a mistake, and the whole value of the two mitigations is avoiding it.

Task 6 — Do it properly

Redo the cycle with a CHECKPOINT immediately before the promotion, then rewind:

docker exec -u postgres rbpg-sb /usr/lib/postgresql/18/bin/pg_ctl -D "$PGD" stop -m fast
docker exec -u postgres rbpg-sb /usr/lib/postgresql/18/bin/pg_controldata -D "$PGD" \
  | grep -E "cluster state|TimeLineID"

docker exec -u postgres rbpg-sb /usr/lib/postgresql/18/bin/pg_rewind \
  --target-pgdata="$PGD" \
  --source-server="host=$NEW_IP port=5432 user=repl password=replpass dbname=postgres" \
  -P -R | tee "$LAB/rejoined.txt"

Destructive53 MB copied out of a 467 MB directory— Captured from the lab run of 2026-08-28, after a checkpoint preceded the promotion.

$ stop the target cleanly, then pg_rewind -P -R

waiting for server to shut down.... done
server stopped

Database cluster state:               shut down
Latest checkpoint's TimeLineID:       2

pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 53 MB (total source directory size is 467 MB)
  0/54852 kB (0%) copied
54852/54852 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!

53 MB out of 467 MB. That is the entire point of pg_rewind: it copies only the blocks that diverged, where pg_basebackup would copy all 467 MB.

Database cluster state: shut down is a prerequisite pg_rewind checks explicitly. A target stopped with -m immediate, or one that crashed, reports in production and is refused — pg_rewind will start it briefly to complete crash recovery first, which is what the message in Task 4 was doing.

Task 7 — Start it, and count what was lost

docker exec -u postgres rbpg-lab21 psql -X -c \
  "SELECT pg_create_physical_replication_slot('rejoined_slot');"
docker exec rbpg-sb bash -c "cat >> $PGD/postgresql.auto.conf <<EOF
primary_conninfo = 'host=$NEW_IP port=5432 user=repl password=replpass application_name=rejoined-old-primary'
primary_slot_name = 'rejoined_slot'
EOF"

docker exec rbpg-sb ls "$PGD" | grep -E "standby.signal|backup_label"
docker exec -u postgres rbpg-sb /usr/lib/postgresql/18/bin/pg_ctl -D "$PGD" -l /tmp/sb.log start
sleep 8

docker exec -u postgres rbpg-sb    psql -X -c "SELECT pg_is_in_recovery() AS now_a_standby;"
docker exec -u postgres rbpg-lab21 psql -X -c \
  "SELECT application_name, state, sent_lsn, replay_lsn FROM pg_stat_replication;"
docker exec -u postgres rbpg-sb    psql -X -d lab21 -c \
  "SELECT who, count(*) FROM ha GROUP BY who ORDER BY who;" | tee -a "$LAB/rejoined.txt"

Data-loss riskthe old primary is a standby again, and its 60 rows do not exist— Captured from the lab run of 2026-08-28. Compare with Task 3.

$ start the rewound server as a standby and count its rows

backup_label
backup_label.old
standby.signal

now_a_standby 
---------------
t
(1 row)

 application_name   |   state   |  sent_lsn  | replay_lsn 
----------------------+-----------+------------+------------
rejoined-old-primary | streaming | 0/7F065AB0 | 0/7F065AB0
(1 row)

     who        | count 
------------------+-------
NEW-PRIMARY-only |   250
shared-history   |   100
(2 rows)

The old primary is streaming from the new one, fully caught up.

And OLD-PRIMARY-only is not in the table. Sixty committed transactions that a client was told had succeeded no longer exist anywhere.

What pg_rewind actually does, and why the loss is unavoidable

pg_rewind does not roll the target back. It makes the target’s data directory match the source at the divergence point, then lets normal recovery replay the source’s timeline forward.

Concretely: it reads the target’s WAL from the last common checkpoint to the divergence point, builds the set of blocks the target modified in that window, copies the current version of exactly those blocks from the source, and rewrites pg_control so the target starts as a standby.

The target’s divergent transactions are not moved elsewhere or logged. The blocks that held them are overwritten with the source’s version. Any row that existed only on the target is gone.

That is the only thing it can do. The two histories are genuinely incompatible — both may have used the same transaction ids for different transactions and written to the same blocks — so there is no merge, only a choice of which history to keep.

-R writes the standby configuration, exactly as pg_basebackup -R does, with the same caveat from Lab 21 about what it appends to.

Capture what you are about to discard, first

Before running pg_rewind, the old primary is still readable. Start it in isolation — a port nothing connects to, listen_addresses restricted — and extract what only it has:

-- On the old primary, before the rewind:
SELECT * FROM orders WHERE created_at > '<the promotion time>';
\copy (SELECT ...) TO '/safe/place/divergent.csv'

Whether those rows can be replayed onto the new primary is an application question, not a database one — they may collide with sequences and ids already used. But you cannot answer it after the rewind, because the data is gone.

The alternative to reading them off the running server is a filesystem snapshot of the old primary’s data directory before the rewind, which is faster to take and slower to use.

Validation

test -s "$LAB/promotion.txt"   && echo "OK promotion"
test -s "$LAB/split-brain.txt" && echo "OK split-brain"
test -s "$LAB/rejoined.txt"    && echo "OK rejoined"

grep -q "selected new timeline" "$LAB/promotion.txt" && echo "OK promotion captured"
grep -q "need to copy"          "$LAB/rejoined.txt"  && echo "OK rewind captured"

# The check that matters: is the old primary streaming, and is its
# divergent data gone?
docker exec -u postgres rbpg-lab21 psql -X -c \
  "SELECT application_name, state FROM pg_stat_replication;"
docker exec -u postgres rbpg-sb psql -X -d lab21 -c \
  "SELECT count(*) = 0 AS divergent_rows_gone FROM ha WHERE who = 'OLD-PRIMARY-only';"

Questions to answer without looking anything up:

pg_promote() returned and pg_control_checkpoint() still shows the old timeline. Is the promotion complete?
What stops a promoted standby and its old primary from both accepting writes?
pg_rewind reports “could not find previous WAL record”. What two things would have prevented that?
pg_rewind copied 53 MB of a 467 MB directory. What determined the 53?
What is the last opportunity to save the old primary’s divergent transactions, and what does it cost to miss it?

Expected Outcome

You have promoted a standby, produced a split brain, met two distinct pg_rewind prerequisites the hard way, rejoined the old primary, and counted the sixty committed transactions that discarded.

The failover checklist this produces:

# BEFORE promoting, if the old primary is reachable:
psql -h old-primary -c "CHECKPOINT;"     # makes the later rewind possible

# Promote:
psql -h standby -c "SELECT pg_promote(wait => true, wait_seconds => 60);"
psql -h standby -c "SELECT pg_is_in_recovery();"   # false = done

# BEFORE rewinding, capture what only the old primary has.

# Rejoin:
pg_ctl -D $PGDATA stop -m fast                       # clean shutdown required
pg_rewind --target-pgdata=$PGDATA \
          --source-server="host=new-primary user=repl ..." \
          --restore-target-wal -P -R                 # archive covers missing WAL
pg_ctl -D $PGDATA start
psql -h new-primary -c "SELECT application_name, state FROM pg_stat_replication;"

And the two preparations that turn a rebuild into a rejoin: grant the four functions on every server that might become a source, and keep a WAL archive even when you have streaming replication.

Troubleshooting

pg_promote returns false. The server is not in recovery, or the wait timed out. SELECT pg_is_in_recovery(); first; if it is already f, the promotion has happened.

pg_rewind refuses with target server must be shut down cleanly. It requires a clean shutdown of the node being rewound. pg_ctl stop -m fast and try again — an immediate stop or a crash is not sufficient.

pg_rewind refuses with target server needs to use either data checksums or "wal_log_hints = on". Neither is enabled on the old primary. On PostgreSQL 18 checksums are on by default at initdb, so this appears mainly on clusters created by an older version or with --no-data-checksums. It cannot be fixed after the fact without pg_checksums and a stopped cluster.

pg_rewind says the servers diverged at an LSN it cannot reach. The old primary needs a checkpoint after the divergence for the rewind to find a common point. Issuing CHECKPOINT; on the old primary before promoting the standby is what makes the later rewind possible.

The rewound node starts as a primary rather than a standby. pg_rewind does not write standby.signal or primary_conninfo unless you pass -R. Write them, or use -R, before starting it.

Rows are missing after the rejoin. They are, and that is the lesson: sixty committed transactions were discarded here. pg_rewind discards the diverged node’s own history — that is what rejoining a diverged node means. Capture what only the old primary has before you rewind.

Cleanup

docker exec -u postgres rbpg-lab21 psql -X -c "DROP DATABASE IF EXISTS lab21;"
docker rm -f rbpg-lab21

Note that this leaves rbpg-sb as a standby of a container that no longer exists. Restoring the fixture means promoting it again, which is Task 2 with a different subject — a reasonable extra exercise.

Production notes

Issue CHECKPOINT; on the old primary before promoting, whenever it is still reachable. It costs seconds and it is frequently the difference between a rewind and a full base backup.
Capture the divergence before you rewind. Once pg_rewind runs, the transactions that existed only on the old primary are gone, and a pg_dump of the affected tables is the only record.
Promotion is not reversible. The decision to promote must be made before it happens, not reconsidered after — the way back is a switchover once the estate is stable.
Nothing in PostgreSQL prevents two primaries. The old primary will happily accept writes after the standby is promoted, and fencing is the operator’s job, not the database’s.
pg_controldata on the promoted node reports the last checkpoint’s timeline, which legitimately lags the promotion. Do not read a stale timeline there as evidence the promotion failed.

What You Learned

Promotion creates a new timeline and is not reversible.
Two primaries is a state PostgreSQL allows. Nothing in the server prevents it; fencing is an operator responsibility.
pg_rewind has real prerequisites — a clean shutdown, and either data checksums or wal_log_hints — and both were met the hard way here.
A checkpoint on the old primary before promotion is what makes the rewind possible later.
pg_rewind discards the diverged node’s history. Sixty committed transactions went, which is what rejoining means.
pg_controldata reports the last checkpoint’s timeline, which is legitimately behind the running one.

Objectives

Prerequisites