Replication

Write on the primary, stream the WAL to the standby, and watch the lag. Then pull the plug — promote the standby — in async vs sync mode and see what survives.

It's just WAL, shipped

Physical (streaming) replication reuses the machinery from the WAL lesson: every change is already a WAL record on the primary. A WAL sender process on the primary streams those records to a WAL receiver on the standby, which replays them into its own copy of the data files — a byte-for-byte clone that trails by the replication lag, usually milliseconds.

There are actually four positions to track, because a record moves through stages on the standby: it's sent, then written to the standby's WAL, then flushed (fsync'd), then replayed into the data files. pg_stat_replication on the primary shows all four per standby — sent_lsn, write_lsn, flush_lsn, replay_lsn — and lag is simply pg_current_wal_lsn() minus the standby's replay_lsn.

Standbys are hot standbys: fully queryable, read-only. So replication buys you two things at once — high availability (promote a standby on failure) and read scaling (send read traffic to replicas).

Async vs sync: the data-loss dial

The dial is when a commit is allowed to return:

• Asynchronous (the default): the primary commits and returns immediately, and the standby catches up a moment later. Fast, but if the primary dies before a commit has streamed, promoting the standby loses that commit. You saw the gap grow as lag in the explorer.
• Synchronous: a commit waits until at least one standby confirms it has the WAL record, then returns. Zero committed data lost on failover — at the cost of round-trip latency on every commit (and it stalls if the sync standby is down).

Replication slots keep the WAL around

A standby that falls behind (or disconnects) still needs the WAL it hasn't consumed yet. A replication slot makes the primary retain WAL until the standby confirms it — so a lagging replica can always catch up. Create one and inspect it right here; with no standby attached it shows active = false — exactly the shape of a forgotten slot:

SELECT slot_name FROM pg_create_physical_replication_slot('demo_standby');

SELECT slot_name, slot_type, active, wal_status
FROM pg_replication_slots;   -- active = false: nothing is consuming this slot

SELECT pg_drop_replication_slot('demo_standby');   -- always clean up dead slots

wal_level must be high enough to log the detail replication needs, and these are the views you'd watch on a real primary:

SHOW wal_level;   -- 'replica' feeds physical standbys; 'logical' also allows decoding
SELECT count(*) AS connected_standbys FROM pg_stat_replication;   -- 0 here: no replicas
SELECT count(*) AS slots FROM pg_replication_slots;

Logical replication: rows, not bytes

Physical replication copies the whole cluster byte-for-byte. Logical replication instead decodes the WAL into row-level changes (INSERT / UPDATE / DELETE). The publisher declares a publication; a subscriber elsewhere creates a subscription that applies those changes. Declare one live:

DROP PUBLICATION IF EXISTS orders_pub;
DROP TABLE IF EXISTS orders CASCADE;
CREATE TABLE orders (id int primary key, amount int);
CREATE PUBLICATION orders_pub FOR TABLE orders;
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete FROM pg_publication;

Because it ships rows instead of bytes, logical replication can target selected tables, replicate between different major versions, or feed an entirely different system — the foundation of change-data-capture and zero-downtime major-version upgrades. It needs wal_level = logical, and each published table needs a replica identity (its primary key by default) so the subscriber can match rows for UPDATE/DELETE.

Your turn

SHOW wal_level;

SELECT count(*) AS connected FROM pg_stat_replication;