postgres://internals

Replication

// the problem

One server is a single point of failure and a single source of read capacity. Replication fixes both: the WAL you met earlier isn't just for crash recovery — stream it to another machine, replay it there, and you have a live, read-only standby that can take over if the primary dies. The only question that really matters is: when the primary fails, how much of the newest data might be gone? That's the sync-vs-async trade-off.

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.

fig.01 · streaming replication
primary lsn
0
standby lsn
0
lag
0
mode
async

primary

accepts writes → WAL

no writes yet

standby

replays WAL · read-only

nothing replayed yet

async: the primary commits immediately; the standby trails by the lag — a failover loses the un-streamed tail

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 standby opens a replication connection, streams those records as they're written, and replays them into its own copy of the data files. The result is a byte-for-byte copy that trails the primary by the replication lag — usually milliseconds.

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).

// why it matters · this is the same synchronous_commit dial, extended

In the WAL lesson synchronous_commit=on meant “wait for the local WAL fsync.” With a synchronous standby it can also mean “wait for the standby to confirm” (remote_write/remote_apply). Same knob, now spanning two machines. Most systems run async and accept a tiny loss window; you go sync only when losing even one committed transaction is unacceptable.

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 has confirmed it — so a lagging replica can always catch up.

// gotcha · a forgotten slot fills your disk

The flip side: a slot for a standby that's gone forever pins WAL forever. The primary can't recycle it, and pg_wal grows until the disk fills — a classic outage. Monitor pg_replication_slots and drop dead ones.

Replication needs wal_level set high enough to log the necessary detail, and you can watch connected standbys and slots from the primary:

sql · live postgresrun me first
⌘/ctrl + enter

(This single in-browser instance has no standbys connected, so the counts are zero — but these are exactly the views you'd watch on a real primary.)

Logical replication: rows, not bytes

Physical replication copies the whole cluster byte-for-byte. Logical replication instead decodes the WAL into row-level changes and publishes them (CREATE PUBLICATION / CREATE SUBSCRIPTION). That lets you replicate selected tables, between different major versions, or into entirely different systems — the foundation of change-data-capture and zero-downtime upgrades. It needs wal_level = logical.

Your turn

// challengewrite it yourself

Show this server's WAL level — it must be at least 'replica' to feed standbys.

⌘/ctrl + enter
// challengewrite it yourself

How many standbys are currently streaming from this server? (Zero here — no replicas are connected.)

⌘/ctrl + enter

// what you now understand

  • 01Replication streams the primary's WAL to a standby, which replays it into a live, read-only (hot standby) copy — for high availability and read scaling.
  • 02Replication lag is how far the standby trails the primary; a standby can be promoted to primary on failure (failover).
  • 03Async replication commits immediately (fast) but a failover can lose the un-streamed tail; sync replication waits for a standby ack (zero loss) at the cost of commit latency.
  • 04Replication slots make the primary retain WAL a standby hasn't consumed — but a forgotten slot pins WAL forever and can fill the disk.
  • 05wal_level must be 'replica' for physical replication (or 'logical' for logical); pg_stat_replication and pg_replication_slots show the state.
  • 06Logical replication decodes WAL into row changes to replicate selected tables across versions/systems — the basis of CDC and zero-downtime upgrades.

// self-test

You run asynchronous replication. The primary's disk dies, and you promote the standby. What's the risk?

// self-test

A replica was decommissioned but its replication slot was never dropped. Weeks later the primary's disk is full. Why?

// go deeper

nextRow-Level Security