Platforms, Microservices, DevOps and APIs · Hands-on 9

Hands-on 9 — Database Cluster

One primary, two replicas, streaming replication — and every trade-off you can provoke on a laptop

One db container

The gateway runs 3 replicas behind Nginx, Redis caches the reads. The db container stops. What still works?

clients
→
Nginx
→
gateway × 3
gateway
gateway
gateway
→
account, order…scale at will
→
data
Redisonly what is cached
db stoppedsingle point of failure
  • Writes stop, and so does every read that misses the cache.
  • A cluster keeps copies of the data on several servers, so one can take over.

What a cluster gives — and what it does not

Goal ✅ Gives ❌ Does not give
Availability a server with the data, ready to be promoted zero downtime: failover takes seconds
Read scaling SELECTs spread across replicas write scaling: one primary takes every write
Durability each commit on another machine protection from a wrong DELETE (replicated too: that is what backups are for)
Replication · this hands-onevery node holds all the data; one primary writes
≠
Sharding · Citus, Vitessthe data split across independent primaries: scales the writes

The WAL (write-ahead log)

INSERT
→
the primary's WALappend-only, written before the page
stream →
each replica replays the WAL
pg-replica1byte-identical copy
pg-replica2byte-identical copy
  • Before changing a page, PostgreSQL appends the change to the WAL; after a crash, it replays the WAL to recover.
  • Streaming replication reuses the same log: identical copies, so a replica is read-only and runs the same major version.

Physical replication copies pages (high availability, read replicas); logical replication copies row changes per table (migrations, upgrades, integrations).

Asynchronous or synchronous?

When does the primary answer COMMIT ok?

Async (default) Sync
Commit returns WAL on the primary's disk a replica confirmed the WAL
Write latency low + 1 network round trip
Primary dies last commits may be lost nothing committed is lost
Replica dies nothing changes commits block

RPO (recovery point objective): how much committed data a crash may lose. Async: RPO > 0; sync: RPO = 0.

CAP theorem (consistency, availability, partition tolerance) in practice: sync prefers consistency, async prefers availability.

When does COMMIT ok come back?

Async
INSERT
→
WAL on the primary's disk
→
COMMIT ok
⋯
the replica gets the WALa crash before this loses the commit
Sync
INSERT
→
WAL on the primary's disk
→
a replica confirms+1 round trip; waits if it is down
→
COMMIT ok
  • Async: the client hears "ok" before any copy exists → RPO > 0
  • Sync: the client hears "ok" after a copy exists → RPO = 0, paid with latency and blocked commits.

Architecture

serviceaccount, order…
writes + reads ⇒
reads (optional) ⇢ replicas
pg-primary:5432 · read-write
WAL stream →
hot standbys · reads
pg-replica1:5433 · read-only
pg-replica2:5434 · read-only
  • All three run postgres:17; an entrypoint picks the role of each node.
  • The application decides where each query goes: here, in the connection string (target_session_attrs / targetServerType).
1 · Docker Composefiles, start, check
→
2 · Replicationexperiments 1–6
→
3 · Failoverplanned, lost write, consensus

Failover: by hand × automated

Step By hand (this hands-on) Automated
detect the failure docker compose ps health checks, heartbeats
choose and promote a replica SELECT pg_promote(); leader election
re-point the other replicas ALTER SYSTEM SET primary_conninfo the tool reconfigures them
redirect the clients multi-host connection string virtual IP, DNS, proxy (HAProxy, PgBouncer)
bring the old primary back pg_rewind the tool rewinds it

The hard step is the first one: deciding that the primary is really dead.

Split brain: the primary that only looks dead

one side of the partition
Client AINSERT 'x'
↓
pg-primaryalive, still accepts writes
✂ networkbreaks between them
the other side
Monitorhealth check unanswered → pg_promote()
↓
pg-replica1promoted
↑
Client BINSERT 'y'

Two primaries: 'x' only on one, 'y' only on the other, the data diverge. That is why automation needs consensus.

1 · The compose project

pgcluster/: compose.yaml + scripts/ (node-entrypoint.sh, init-primary.sh, rejoin-as-replica.sh)

  • 3 × postgres:17, one volume each, shared via YAML anchors
  • cluster_name: which node answered
  • PRIMARY_HOST set → the node is a replica
  • replicas have no depends_on: the entrypoint waits
  • client (profile tools): psql inside the network
# node-entrypoint.sh, empty data dir
pg_basebackup -h "$PRIMARY_HOST" \
  -U "$REPLICATION_USER" \
  -D "$PGDATA" -X stream -R
# -R → standby.signal
#      + primary_conninfo

init-primary.sh: role replicator, wal_keep_size, wal_log_hints, pg_hba rule — copied to every replica. Save the scripts with LF line endings (not CRLF, the Windows ones).

1 · How each node picks its role

the container starts
→
data directory already initialised?
yes →
start PostgreSQLprimary or standby, as it was
no →
PRIMARY_HOST set?
no →
initdb + init-primary.sh
→
new primary
yes →
wait for the primary
→
pg_basebackup -X stream -Rcopy of the primary
→
hot standbyread-only, receiving the WAL
  • A node that already has data comes back in the role it ended in: after a failover, pg-primary may be a replica.
  • That is why docker compose down -v (removes the volumes) is how you start over with the original roles.

1 · Start and check

docker compose up -d --wait     # returns when all three nodes are healthy
docker compose exec pg-primary psql -U store \
  -c "SELECT application_name, state, sync_state FROM pg_stat_replication;"
 application_name | state     | sync_state
------------------+-----------+------------
 pg-replica1      | streaming | async
 pg-replica2      | streaming | async
logs pg-replica1
waiting for pg-primary
→
cloning pg-primarypg_basebackup
→
entering standby mode
→
started streaming WAL
  • On a replica: pg_is_in_recovery() → t, pg_stat_wal_receiver → streaming from pg-primary.

1 · Troubleshooting

Symptom Cause and fix
$'\r': command not found scripts saved with CRLF: save as LF, recreate the cluster
replica loops on no pg_hba.conf entry for replication connection primary created before init-primary.sh existed: recreate
port is already allocated on 5432 the db of a previous hands-on is up: stop it or change the ports
replica stuck on waiting for pg-primary... the primary is not up: docker compose logs pg-primary
docker compose down -v && docker compose up -d --wait    # start over: one primary, two replicas

2 · Experiments 1–3: copies, read-only, lag

keyboard
mouse
monitor · INSERT on the primary
pg-primary
✓
✓
✓
pg-replica1 · replay paused
✓
✓
missing184 bytes behind
pg-replica2
✓
✓
✓
  • 1 · CREATE TABLE + INSERT on the primary → the rows appear on the replicas, through the WAL.
  • 2 · INSERT on a replica → cannot execute INSERT in a read-only transaction.
  • 3 · pg_wal_replay_pause() on pg-replica1: it counts 2 rows, pg-replica2 counts 3; pg_stat_replication shows the bytes behind.

2 · Read-your-writes: the stale read

user creates an order
→
INSERT on pg-primary
→
COMMIT ok
→
"list my orders"
→
SELECT on pg-replica1lagging
→
"where is my order?"
  • The WAL reaches the replica — too late for that read.
  • Lag grows under heavy load, long queries on the replica or a slow network.

Fixes: read from the primary right after a write (for that user, for a few seconds), or remote_apply (experiment 5) for the queries that must not see stale data.

2 · Experiment 4: routing by role

drivertarget_session_attrs=read-write
→
pg-primaryaccepts writes?
no or down →
pg-replica1accepts writes?
no or down →
pg-replica2accepts writes?
no →
errornone with the role
the first one that says "yes" keeps the connection
host=pg-primary,pg-replica1,pg-replica2 target_session_attrs=read-write
host=pg-primary,pg-replica1,pg-replica2 target_session_attrs=standby load_balance_hosts=random
jdbc:postgresql://pg-primary:5432,pg-replica1:5432,pg-replica2:5432/store?targetServerType=primary
  • The list names candidates, not the primary — it survives a failover.

2 · Experiment 5: synchronous commits

FIRST 1 ("pg-replica1")
INSERT headset
→
pg-replica1 stoppedthe only one that counts
→
hangsCtrl+C cancels only the wait
ANY 1 (r1, r2) · quorum
INSERT webcam
→
pg-replica2 confirms
→
COMMIT okat once
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.

Is the headset row committed? (Yes — on the primary; only the wait was cancelled.)

2 · How long the primary waits

COMMIT
→
received in memoryremote_write
→
written to diskon (default)
→
replayedremote_apply
→
COMMIT ok
synchronous_commit Commit returns when the sync replica has… Guarantees
remote_write received the WAL (in memory) survives a primary crash
on (default) written the WAL to disk survives a crash of both
remote_apply replayed the WAL a read on that replica sees the commit
  • ANY 1 → both replicas show sync_state = quorum; one may be down.
  • Back to async before the failover page: ALTER SYSTEM RESET synchronous_standby_names; + pg_reload_conf().

2 · Experiment 6: under load (optional)

docker compose run --rm --entrypoint pgbench client -h pg-primary -i -s 10
docker compose run --rm --entrypoint pgbench client -h pg-primary -c 8 -T 30 -P 5
docker compose run --rm --entrypoint pgbench client -S -n -c 8 -T 15 \
  "host=pg-replica1,pg-replica2 load_balance_hosts=random"
pgbench -c 8writes
→
pg-primarytakes every write
WAL · replay_lag grows →
pgbench -S · reads
pg-replica1part of the reads
pg-replica2part of the reads
  • Reads scale with the replicas; writes do not.
  • Repeat with ANY 1: the drop in write tps (transactions per second) is the price of RPO = 0.

3 · Planned failover

before
the primary fails · promote
after
pg-primary
primary
downdocker compose stop
replica of pg-replica1rejoin-as-replica.sh: pg_rewind + standby.signal
pg-replica1
replica
primary · timeline 2SELECT pg_promote();
primary
pg-replica2
follows pg-primary
no primary to follow
follows pg-replica1primary_conninfo + reload
clients
read-write → pg-primary
read-write: none
same string → pg-replica1
  • Before the rejoin, pg-primary must be stopped: two servers on one data directory corrupt it.
  • After a clean stop: no rewind required — the old primary wrote nothing the new one lacks.

Roles swapped, names stale: real clusters name nodes pg-1, pg-2… and ask pg_is_in_recovery().

3 · Unplanned failover: the lost write

1 · stop both replicas
→
2 · INSERT 'lost-write'INSERT 0 1
→
3 · kill the primary
→
4 · start the replicas, promote pg-replica1
→
5 · lost-write is gone
  • pg_rewind on the old primary: servers diverged… rewinding → lost-write is discarded for good.

That is the RPO of async replication. Sync would have saved it — by blocking the INSERT: the client would never have heard the order existed.

ids 1, 2, 34: sequences log values in batches of 32; never expect gapless serial.

The fork: where lost-write went

keyboard, mousetimeline 1, on every node
→
after the fork
lost-writeonly on the killed pg-primary, timeline 1
after-failover · id 34pg-replica1 promoted, timeline 2
→
pg_rewindcuts pg-primary back to the fork; it follows timeline 2
  • The client heard INSERT 0 1 for lost-write: the order existed, then vanished.
  • A planned stop has no red branch: no rewind required.

3 · Why not a failover script?

The primary stops answering the health check. Is it dead — or slow? Or cut off from the monitor but not from the clients?

Patroni: only the holder of the leader key in etcd (Raft) runs as primary. The key has a TTL (time to live):

pg-1 · primary
renews the key (TTL 30 s) →
etcd · Raft
→
"ok, you are the leader"
network pg-1 ↔ etcd breaks
→
pg-1 cannot renewdemotes itself
→
the key expires
→
pg-2 takes the keyand only then promotes
  • Never two primaries: the isolated one steps down before another steps up. Managed services (RDS Multi-AZ, Aurora, Cloud SQL) do the same for you.

3 · Connecting the platform

  account:
    environment:
      SPRING_DATASOURCE_URL: jdbc:postgresql://pg-primary:5432,pg-replica1:5432,pg-replica2:5432/store?targetServerType=primary
      SPRING_DATASOURCE_USERNAME: store
      SPRING_DATASOURCE_PASSWORD: devpass
after the failover
accountHikariCP drops the broken connections
→
JDBC drivertargetServerType=primary
→
pg-primarydown
→
pg-replica1the new primary
  • Reads on the replicas: a second DataSource (preferSecondary) + AbstractRoutingDataSource for readOnly — mind the stale reads.

Clean-up: docker compose down -v — without -v the cluster comes back with the roles it ended in.

Simulator: replication and synchronous commits

The lab's cluster in the browser: same nodes, same commands, same outputs. It starts where the lab starts: orders with keyboard and mouse, both replicas streaming.

Replication 1–4
pg-primary3 rows
pg-replica1 · replay paused2 rows, 184 bytes behind
pg-replica23 rows
Replication 5
FIRST 1 · pg-replica1 stoppedINSERT headset hangs: 1 commit hung
ANY 1webcam returns at once; both become quorum
  • The replica refuses the INSERT; read-write finds pg-primary and standby picks a random replica.
  • synchronous_commit = remote_apply + paused replay: the commit waits for the replica to replay.

Overview (opens on experiment 5) and top of Replication · simulator pg-cluster.

Simulator: failover and the lost write

Planned failover
read-write: nobodyprimary stopped
→
pg_promote()timeline 2
→
same string → pg-replica1
→
no rewind required1 primary, 0 lost
Lost write
INSERT 0 1lost-write
→
the primary dies
→
ids 1, 2, 34pg-replica1 promoted
→
pg_rewind discards1 write lost
free mode
pg-primary · primarytimeline 1, no pg_rewind
pg-replica1 · primarytimeline 2
pg-replica2follows pg-replica1
→
primaries up: 2

The simulator's counters: primaries up, rows on the primary, commits that hung, writes lost. Top of Failover and the lost-write section · simulator pg-cluster.

Takeaways

  1. Replication copies the WAL: replicas are read-only, byte-identical copies.
  2. It scales reads and gives availability; writes still go to one primary.
  3. Async: fast, but stale reads and lost writes on a crash (RPO > 0).
  4. Sync (ANY 1): RPO = 0, paid with latency and blocked writes.
  5. Route by role in the connection string; it survives a failover.
  6. Failover = promote, re-point, redirect, rewind — and deciding when needs consensus, or it ends in split brain.

Outline: (1) why a cluster and what it does not solve; (2) the WAL and streaming replication; (3) async × sync, CAP; (4) architecture and failover, by hand × automated, split brain; (5) Docker Compose: files, the role of each node, start, check; (6) Replication: experiments 1–6; (7) Failover: planned, lost write, consensus, connecting the platform; (8) the simulator.

The page's sequence diagram "Asynchronous or synchronous?" shows the same two paths step by step.

The same flow plays step by step on the Docker Compose page, in "How it works".

The page draws the same fork as a step-by-step diagram in "Unplanned failover: a lost write".