Part 8 · 3 chapters · ~18 min

Replication

Physical streaming replication with WAL senders and receivers and hot standby, replication slots and the disk they fill, synchronous replication and quorum commit, hot standby conflicts, logical replication with publications and subscriptions, the logical versus physical decision, CDC with Debezium and wal2json, and failover with Patroni and consensus.

Streaming replication, slots and synchronous commit

code

-- on the primary: lag per standby, in bytes and time
SELECT application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes, replay_lag
FROM pg_stat_replication;

-- slots holding WAL back
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
ALTER SYSTEM SET max_slot_wal_keep_size = '50GB';     -- invalidate a slot rather than fill the disk

STREAMING REPLICATION

a WAL sender on the primary, a WAL receiver on each standby, and the slot that remembers

swipe the figure sideways, or tap expand for full screen

1/5

physical streaming

Each standby connects as a replication client; the primary starts a WAL sender process that streams WAL records as they are written. The standby is a byte-identical copy of the whole cluster.

WAL bytes streamed to every standbya physical copy of the entire cluster

Logical replication and CDC

code

-- publisher
ALTER SYSTEM SET wal_level = logical;   -- restart required
CREATE PUBLICATION ledger_pub FOR TABLE accounts, transfers;

-- subscriber (another cluster, can be a different major version)
CREATE SUBSCRIPTION ledger_sub CONNECTION 'host=primary dbname=app user=repl' PUBLICATION ledger_pub;

	physical	logical
unit	WAL bytes, whole cluster	row changes per table
versions	same major version, same architecture	across major versions (zero-downtime upgrades)
subscriber writable	no (read-only standby)	yes, other tables and indexes allowed
DDL	replicated	not replicated: apply schema changes on both sides
sequences, large objects	replicated	not replicated (sequence sync arrives in newer versions)
use for	HA and read replicas	upgrades, partial copies, CDC, consolidation

CDC: Debezium's Postgres connector creates a logical slot (pgoutput), takes a consistent snapshot, then streams every insert, update and delete to Kafka with before and after images (with REPLICA IDENTITY FULL for full before images). It is how Postgres becomes an event source without dual writes, and how the outbox pattern ships events (Scaling Databases part 8).

Failover: Patroni, repmgr and consensus

Postgres does not elect a new primary by itself. Patroni runs beside each node, keeps a leader lock in a consensus store (etcd, Consul or ZooKeeper), promotes a standby when the leader lock expires, and reconfigures the others to follow it, using pg_rewind for the old primary. A proxy layer (HAProxy checking Patroni's REST API, or PgBouncer reconfigured) sends clients to the current leader.

failover questions to answer before the incident

Synchronous or asynchronous standbys: how much data can a failover lose (RPO)?
Who fences the old primary so it cannot accept writes after promotion (split brain)?
How do clients find the new primary: DNS TTLs, a proxy, or multi-host connection strings (target_session_attrs=read-write)?
Is failover tested in game days (SRE part 10), with the time measured?