Replication

Replication maintains multiple copies of the same data. It can keep a service available after a machine fails, place reads nearer users, or move read work away from the writer. A copy is useful only when you know which changes it contains and which failures it can survive.

A leader/follower design gives one leader responsibility for ordering writes. Followers receive that order and apply it locally. This avoids independently resolving every concurrent write, but introduces three separate questions: when to acknowledge a write, which copy may answer a read, and who may become the next writer.

The downloadable lab contains a Python model and an actual PostgreSQL exercise. Extract it and run these commands from its directory:

python3 -m unittest discover -s . -p 'test_*.py' -v
python3 postgres_lab.py
# Five model tests; PostgreSQL assertions include lag, replay and restore.

Both need Python 3.11+. The PostgreSQL lab additionally needs Docker and a cached postgres:18.6 image; obtain the image beforehand with docker pull postgres:18.6. It creates two database clusters in one disposable container, using separate directories and Unix sockets, with no published ports or external network. It removes its container and volumes on completion or ordinary interruption. This setup observes database mechanisms, not independent-machine availability or disk-failure tolerance.

Copies follow a log

A database generally replicates an ordered record of changes rather than copying whole tables after every request. PostgreSQL physical replication streams write-ahead log, or WAL, from a primary to a standby bootstrapped from a base backup. Physical replication follows storage-level changes; logical replication can instead publish selected logical changes. Neither means each follower can independently accept arbitrary writes.

Distinguish log positions at four boundaries: sent, written at the receiver, flushed to durable storage, and replayed into queryable state. Network delivery, durability and visibility are different events. PostgreSQL represents WAL positions using an LSN, a log sequence number.

The lab creates events, builds a physical standby with pg_basebackup, then pauses standby replay. These are its central operations, run on the indicated server:

-- Standby: wait until pg_get_wal_replay_pause_state() reports 'paused'.
SELECT pg_wal_replay_pause();

-- Primary: autocommit completes before obtaining a conservative position.
INSERT INTO events VALUES (1, 'acknowledged on primary');
SELECT pg_current_wal_flush_lsn();  -- Save the result as session_position.

-- Standby: receipt may advance while replay remains paused.
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
SELECT count(*) FROM events WHERE id=1;  -- 0

The runner waits until receipt reaches the saved position, then asserts replay remains behind it and the row is absent. That proves a stale read without relying on a slow network or a guessed sleep duration.

For read-your-writes, carry the session's required position to the read path. On a compatible follower, wait until the following condition is true, then start a fresh query snapshot:

SELECT pg_last_wal_replay_lsn() >= :'session_position'::pg_lsn;
-- If true, a new statement can observe changes through this position.

The :'session_position' notation is a psql variable. The runner passes its validated LSN directly and polls with a monotonic deadline. With replay paused, its read gate times out. After pg_wal_replay_resume(), the gate succeeds and the row becomes visible. An existing repeatable-read snapshot may remain older; waiting does not refresh that snapshot.

If the gate cannot finish, route to an authoritative copy that has the required history, or return a freshness failure. Pinning post-write reads to the current leader is another option. A fixed delay merely guesses when replication will catch up. Position tokens also need cluster and timeline identity: after failover, a numerically larger LSN alone does not establish that the new history contains the user's write.

The fixture separately disconnects the receiver, writes another row, and measures retained WAL on the primary:

SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_distance_bytes
FROM pg_replication_slots;

A physical replication slot preserves WAL needed by its receiver. Paused replay can still receive and flush WAL, allowing the primary's slot position to advance; stopping receipt exposes a different backlog. The byte distance is a retention boundary, not the total size of WAL segment files. Slots need disk monitoring and retention limits; a sufficiently abandoned follower may require a new base backup. Lag in bytes also cannot be converted into recovery seconds without knowing replay capacity and workload.

What an acknowledgement promises

An asynchronous replica does not participate in the leader's commit acknowledgement. With normal local durability enabled, the primary can acknowledge after its own WAL is durable while a follower remains behind. Losing the primary's storage and promoting that follower can therefore lose acknowledged writes. Restarting the original process with its intact durable log is a different failure.

The fixture's Pair is an explicitly deterministic model: Python lists stand for durable logs, and explicit calls stand for delivery, replay and irrecoverable storage loss. It does not implement disks, elections or PostgreSQL. Compare the same failure under two acknowledgement rules:

from model import Pair

async_pair = Pair()
async_pair.commit("order:7", "paid")
async_pair.lose_leader_storage_and_promote()
assert async_pair.leader.read("order:7") is None

sync_pair = Pair()
sync_pair.commit("order:7", "paid", sync=True)
assert sync_pair.follower.read("order:7") is None  # Durable, not replayed.
sync_pair.lose_leader_storage_and_promote()
assert sync_pair.leader.read("order:7") == "paid"

Synchronous replication moves the success boundary. Waiting for another durable copy protects against losing one storage system, at the cost of remote latency and dependence on that copy. Waiting for replay adds query visibility. In PostgreSQL, configured synchronous standbys plus synchronous_commit=on wait for their durable flush; remote_apply waits for replay too. Without configured synchronous standbys, selecting remote_apply does not create a remote acknowledgement requirement.

The actual lab configures its named standby as synchronous, pauses replay, and starts this write:

SET synchronous_commit = 'remote_apply';
INSERT INTO events VALUES (2, 'waited for replay');
-- Another primary session observes wait_event='SyncRep'.

It asserts that the writer is waiting, resumes replay, and verifies that acknowledgement returns with the row visible to a fresh standby query. This tests an actual commit boundary; it does not simulate power loss. If the required standby cannot respond, the write may wait or the caller may time out. Timeout does not prove rollback. The model tests a locally recorded write whose remote acknowledgement fails; retrying needs the operation identity developed in Queues and Retries.

Quorum replication makes a set of replicas participate in agreement. A three-voter majority design needs two voters and can continue through one unavailable voter, assuming the protocol's other conditions hold. Consensus protocols such as Raft combine log ordering, elections, commitment rules and membership rules. Simply counting read and write responses is insufficient: the protocol must identify committed versions and exclude stale authority. Even after a majority commits, an arbitrary follower can still answer stale reads. Read routing needs its own guarantee. The partition tradeoff belongs to Distributed Systems and CAP.

Promotion needs a fence

Failover chooses a replacement writer, brings its durable history into service, and changes routing. A heartbeat timeout is evidence of missing communication, not proof that the old leader is dead. A paused or partitioned old process may return with outstanding writes. Updating a load balancer alone does not stop its existing connections or background work.

Fencing prevents the obsolete writer from affecting a protected resource. A reliable authority can issue increasing epochs; the resource must persist the accepted epoch and reject operations from an earlier owner. The model's SQLite resource installs the new epoch before accepting replacement-leader writes:

resource.install_epoch(41)
resource.write(41, "old leader")
resource.install_epoch(42)
resource.write(42, "replacement leader")
resource.write(41, "late old-leader write")  # Raises PermissionError.

Enforcement happens inside the resource's atomic mutation, rather than in a leader's belief about itself:

changed = db.execute(
    "UPDATE protected SET value=? WHERE id=1 AND epoch=?",
    (value, supplied_epoch),
).rowcount
if changed != 1:
    raise PermissionError("fenced at protected resource")

The tests reopen SQLite and prove the fence persists. They also reject an uninstalled future epoch and attempts to move the epoch backward. Epoch issuance and authorization are assumed, not supplied by this model; arbitrary callers must not install epochs. Every relevant write path must enforce the rule. Other fencing designs revoke storage access or isolate the old machine. Database-internal replication authority does not automatically fence unrelated external side effects.

Before promoting, also establish which acknowledged history the candidate must contain. A sync policy protects only the copies it actually required; promoting some other stale replica can discard that protection. An old leader generally needs reconciliation or rebuilding before rejoining the chosen history. Adding more replicas is separate from Sharding, which distributes different portions of the dataset.

Recovery needs history

Replication faithfully copies mistakes too. The lab captures a logical pg_dump of its three rows, deletes them on the primary, waits for replay, and checks that the standby also contains zero rows. It then restores the saved SQL into a separate database:

pg_dump --table=events --no-owner --no-privileges postgres > before-delete.sql
createdb restored
psql -v ON_ERROR_STOP=1 -d restored < before-delete.sql
# The lab asserts restored.events has 3 rows; the live follower still has 0.

These commands illustrate the restore sequence; the runner supplies its private socket connection settings. Its dump exists in the host runner's memory for the test. A production backup needs durable retention in an appropriate separate failure domain, protected access, and regular restore verification.

The tested operation is a logical snapshot restore. PostgreSQL point-in-time recovery instead needs a suitable physical base backup and an unbroken sequence of archived WAL through the desired target, stopping before the destructive transaction when appropriate. A replication slot is not that independent archive, and a logical dump cannot be rolled forward by physical WAL.

Set a recovery point objective, or RPO, for the amount of data loss the service can accept, and a recovery time objective, or RTO, for acceptable interruption. Asynchronous failover may lose unshipped writes; a retained backup may recover older data but take longer to restore. Measure detection, candidate validation, replay, fencing, routing and application checks as part of recovery time. Separate tested targets for machine failure and accidental deletion: the same replica does not solve both.