Streaming Replication

A physical replica is the same bytes as the primary, kept that way by continuously replaying the primary's write-ahead log. It starts life as a base backup, it stays current over a network connection between a walsender process on one side and a walreceiver on the other, and it never stops recovering — a standby is a server that is permanently in the middle of crash recovery and has simply not been told to finish.

Cartwheel already runs one. pg-replica-a has been serving the analytics dashboard for a year, which is where the dashboard's occasional habit of missing an order the customer's own page is already showing comes from. This chapter adds pg-replica-b as the failover target, and the first thing to be clear about is that both machines are built by the same mechanism used for two different jobs, with two different sets of caveats attached.

Building a Standby

The build is three commands and no cleverness. Create a replication slot on the primary, run pg_basebackup on the new host with -R, start the server. The slot is created first so that the WAL generated between the start of the copy and the moment the standby actually connects is guaranteed to still exist when it asks for it.

-- on pg-primary
SELECT pg_create_physical_replication_slot('replica_b');

$ pg_basebackup -h pg-primary -U replicator -S replica_b -R \
                -D /var/lib/postgresql/18/main --progress
    746586112/746586112 kB (100%), 1/1 tablespace

$ ls -l /var/lib/postgresql/18/main/standby.signal
-rw------- 1 postgres postgres 0 standby.signal   -- empty on purpose

$ tail -2 /var/lib/postgresql/18/main/postgresql.auto.conf
primary_conninfo = 'host=pg-primary port=5432 user=replicator application_name=replica_b'
primary_slot_name = 'replica_b'

Read -R as two side effects. It creates standby.signal, a zero-byte file whose mere presence tells the server at startup to come up in standby mode rather than as a normal primary. And it appends the connection settings to postgresql.auto.conf — primary_conninfo, plus primary_slot_name when a slot was named with -S. The WAL method defaults to -X stream, which opens a second replication connection and streams the log in parallel with the file copy, so the backup is self-contained the moment it finishes. That second connection is worth remembering when you count against max_wal_senders, which defaults to 10: one base backup occupies two of those slots while it runs.

Physical Means Identical

Byte-for-byte has consequences that no amount of configuration can soften. The replica runs the same major version as the primary, on the same architecture and the same block size, because it is replaying records that describe changes to specific bytes of specific pages. It carries every database in the cluster, not a chosen one, and inside cartwheel it carries all nine tables plus product_categories and delivery_events_archive, every index, every sequence and every catalogue row. There is no way to replicate orders and skip delivery_events here; that is a different mechanism with different rules, and it is two topics away.

The same faithfulness runs in the direction nobody wants. A migration that deletes 90,000 orders rows at 14:12 is replayed on both replicas within about a second of the commit, because the replica's entire job is to reproduce what happened. Chapter 12 put it plainly and it is worth carrying forward: a replica is not a backup. The archive still holds the state before 14:12; both replicas hold the state after it.

Slots Against wal_keep_size

The primary recycles WAL segments at checkpoints, and it has no innate obligation to keep any of them for a machine that is not currently asking. If pg-replica-b is down for a maintenance window long enough for the segments it needs to be recycled, it cannot resume; it needs a fresh base backup, which on 700 GB is hours rather than minutes. There are two ways to prevent that and they fail in opposite directions.

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
  FROM pg_replication_slots;

 slot_name | active | wal_status |  retained
-----------+--------+------------+-----------
 replica_a | t      | reserved   | 48 MB
 replica_b | f      | extended   | 21 GB      -- decommissioned, slot never dropped

A replication slot records a restart_lsn: the oldest WAL position its consumer might still need. The primary will not recycle past that point, so the replica can always catch up, and a slot with nobody on the other end will therefore hold WAL until pg_wal fills and the cluster stops accepting writes. wal_status is the column that says which situation you are in: reserved while the retained WAL is inside max_wal_size, extended once it is past that and being held by the slot, then unreserved and finally lost, at which point the slot's consumer needs rebuilding. max_slot_wal_keep_size defaults to -1, which is "no limit", and setting it to a real number converts an outage into a rebuild. Since 18 there is a second guard: idle_replication_slot_timeout, which invalidates a slot that has had no connection for longer than the configured duration, evaluated at checkpoint time and reported afterwards as invalidation_reason = idle_timeout. Its default is 0, meaning off.

wal_keep_size is the bounded alternative and defaults to 0, so out of the box the primary keeps nothing extra at all. Setting it to, say, 16 GB means a replica may be absent for however long 16 GB of WAL takes to generate, and after that it falls off and needs a rebuild, while the primary's disk is never held hostage. Cartwheel uses slots on both replicas, because a rebuild of the failover target takes the hours the failover was meant to save, and pairs them with an alert on retained bytes that fires while wal_status still reads reserved.

Hot Standby and Its Conflict

hot_standby is on by default, which means the replica accepts connections and answers read-only queries while it replays. That is what makes pg-replica-a useful to the analytics team at all. What it refuses is broader than "no INSERT": no DDL of any kind, no SELECT … FOR UPDATE or FOR SHARE, no nextval() or setval(), no LISTEN or NOTIFY, no two-phase commit, and no temporary tables. Every one of those needs to write WAL, and a standby has no timeline of its own to write on.

Replay and reading compete, and Postgres resolves the competition by killing the reader. Five situations produce a recovery conflict: an ACCESS EXCLUSIVE lock taken on the primary, a dropped tablespace the standby is using for temporary files, a dropped database with sessions still attached, a vacuum cleanup record removing rows a standby snapshot can still see, and a cleanup record touching a page a standby query is reading. When replay hits one, it waits for up to max_standby_streaming_delay, 30 seconds by default, and then cancels the query with canceling statement due to conflict with recovery. Two details make this sharper than it looks. The delay is measured from when the WAL was received, not from when the conflict was noticed, so a standby that is already behind offers a query far less than the full 30 seconds. And -1, which means wait forever, moves the tradeoff rather than removing it: replay stopping is replay lag, and the failover target is then behind by the length of the analytics team's longest report. The counts and their reasons land in pg_stat_database_conflicts.

Cascading and Topology

A standby can run its own walsender and feed further standbys, which is how a cross-region copy is normally attached: one link over the expensive network instead of three. The constraints are specific. Cascading is asynchronous only, so a downstream node can never be a synchronous standby. The primary knows nothing about anything below its direct children, so pg_stat_replication on pg-primary will not show them. Feedback from a downstream node does propagate upward. And if the intermediate node is promoted, its downstream standbys keep following it, because recovery_target_timeline defaults to latest and they will pick up the new timeline.

Cartwheel keeps both replicas attached directly to pg-primary, and the reason is the failover plan rather than the bandwidth. pg-replica-a carries the analytics workload and is tuned for it; pg-replica-b is kept clean and is the promotion target. Hanging b off a would have made the failover target's freshness depend on the health of a machine that runs hour-long reports, and pg-replica-a is currently 41 minutes behind on apply.

Monitoring the Pair

The primary's view is pg_stat_replication, one row per connected walsender. state moves through startup, catchup and streaming; anything sitting in catchup for long is a replica that fell behind far enough to be reading from disk rather than from the stream. Four LSN columns say how far the WAL got: sent_lsn for what left the primary, then write_lsn, flush_lsn and replay_lsn for what the standby has written, made durable and actually applied.

SELECT application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind,
       write_lag, flush_lag, replay_lag
  FROM pg_stat_replication;

 application_name |  state   | sync_state | behind  | write_lag | flush_lag | replay_lag
------------------+----------+------------+---------+-----------+-----------+------------
 replica_a        | streaming| async      | 512 MB  | 00:00:00.4| 00:00:00.5| 00:41:12.6
 replica_b        | streaming| async      | 88 kB   | 00:00:00.3| 00:00:00.4| 00:00:00.5

Those two rows are the whole argument for reading the right column. Both replicas are connected, both are streaming, and any monitoring check that alerts on connection state shows two green lights. But replica_a has received and flushed everything within half a second and is 41 minutes behind on applying it, because a long analytics query is blocking replay. The three lag columns are intervals, not byte counts, and they separate the three costs precisely: write_lag is the network, flush_lag adds the standby's disk, and replay_lag adds the apply. From the standby's own side, pg_stat_wal_receiver gives status, flushed_lsn, received_tli, slot_name and the sender_host it believes it is following, and now() - pg_last_xact_replay_timestamp() converts the position into the answer a human actually wants: how old is the newest data on this machine. On replica_a that query returns forty-one minutes while every other column on the row reads normal.