skip to content

In PostgreSQL, how do you set up a cascading standby, and what changes for synchronous replication and monitoring when you do?

level: middleimportance: nice to knowfreq 24%

answer

  1. a standby that also sends
  2. point primary_conninfo at a standby
  3. never synchronous
  4. invisible to the primary's view

basics

~10 s

Point a downstream standby's primary_conninfo at another standby that accepts replication connections. Cascaded standbys are always asynchronous, appear only in their upstream's pg_stat_replication, and their hot_standby_feedback is passed on upstream to the primary.

solid answer

~40 s

A cascading standby is a streaming standby that also runs WAL senders: give it `max_wal_senders` above zero (default `10`), `hot_standby` on and a `pg_hba.conf` line with database `replication`, then set the downstream standby's `primary_conninfo` to the cascading standby instead of the primary. Nothing changes on the primary. It cuts connections and WAL-sending work on the primary and lets WAL cross a slow site link once. What changes: cascading replication is asynchronous, so `synchronous_standby_names` has no effect on it; the primary's `pg_stat_replication` shows only directly connected standbys, so you query each sender; `hot_standby_feedback` still reaches the primary; and lag adds up hop by hop. If the cascading standby is promoted, downstream standbys follow it when `recovery_target_timeline` is `latest`, the default.

code

ini · 7 lines
ini
# reporting-eu (downstream) postgresql.conf; data dir has standby.signal
primary_conninfo = 'host=standby-eu-relay port=5432 user=replicator application_name=reporting_eu'

# standby-eu-relay (cascading standby) postgresql.conf
max_wal_senders = 10       # restart to change
hot_standby = on
wal_keep_size = 4GB        # protects its own downstream

go deeper

for a junior

Recall that a standby can feed other standbys: the downstream's primary_conninfo simply names a standby instead of the primary.

for a middle

Explain what the relay needs (max_wal_senders, hot_standby, a replication pg_hba line), and what changes: always asynchronous, invisible to the primary's pg_stat_replication, feedback passed upstream.

for a senior

Show the operational side: monitor every sender, check sender_host, size wal_keep_size on the relay, repoint downstreams when the relay fails, and keep failover candidates directly attached.

for a principal

Design the topology: which replicas hang off relays across sites, which stay direct for synchronous acknowledgement and failover, and how much stacked lag each consumer can accept.

## What a cascading standby is In PostgreSQL streaming replication, a standby normally streams WAL straight from the primary. A **cascading standby** is a standby that also **sends** WAL: other standbys connect to it instead of to the primary, and it relays what it has. Servers closer to the primary are **upstream**, those further away **downstream**. Each standby has exactly one upstream and every chain ends at a single primary; PostgreSQL does not limit the depth or fan-out. Typical reasons to cascade: - **Less load on the primary** — ten reporting replicas can hang off one relay instead of ten WAL senders on the primary. - **Cross-site bandwidth** — ship WAL across a slow link once, to one standby in the remote data centre, and fan out locally there. ## Setting it up 1. On the **cascading standby**, allow replication connections: `max_wal_senders` above zero (default `10`, restart to change), `hot_standby` on, and a `pg_hba.conf` line with database `replication` for the downstream's address. 2. Build the **downstream standby** from a base backup as usual, with its own `standby.signal` file. 3. Set the downstream's **`primary_conninfo`** to the cascading standby's host instead of the primary's. Nothing is configured on the primary. A cascading standby relays not only WAL it streamed but also WAL it **restored from the archive**, so downstream standbys keep receiving WAL while the upstream link is broken, as long as the archive has it. ## What changes | Aspect | Standby fed by the primary | Cascaded standby | |---|---|---| | Synchronous replication | can be listed in `synchronous_standby_names` | **always asynchronous**; sync settings have no effect | | Row in the primary's `pg_stat_replication` | yes | **no** — only in its upstream's | | `hot_standby_feedback` | reaches the primary directly | **passed upstream** hop by hop until it reaches the primary | | WAL retention protecting it | the primary's `wal_keep_size` or a slot there | its upstream's `wal_keep_size` or a slot on the upstream | | Lag | one hop | adds up hop by hop | The feedback row matters for reporting replicas: a downstream standby with `hot_standby_feedback = on` still holds back cleanup on the primary, through every hop. ## Monitoring a chain - Query **`pg_stat_replication` on every sending server**. The primary shows only its direct standbys; a cascading standby shows its own downstream. - On a downstream standby, `pg_stat_wal_receiver.sender_host` names the server it streams from — the quickest way to confirm the topology is what you think it is. - For end-to-end freshness, compare the downstream's `pg_last_wal_replay_lsn()` with the primary's `pg_current_wal_lsn()` from the same monitoring run. ## Failure modes - **The relay dies.** Its downstream standbys stop receiving streamed WAL. Point their `primary_conninfo` at another server and reload; the WAL receiver restarts with the new string. - **The relay is promoted.** With `recovery_target_timeline = 'latest'`, the default, downstream standbys follow the new timeline and keep streaming from it. - **The relay falls behind.** Everything below it is at least as stale, so a cascaded standby is a weak failover candidate when freshness matters. ## What the relay has to keep for its downstream A cascading standby is a **sending server** in its own right, and the retention rules apply to it as well. It removes old WAL segments at its **restartpoints** (the standby's equivalent of a checkpoint), so a downstream standby that disconnects for longer than the relay's retained window gets the same `requested WAL segment ... has already been removed` error a slotless standby gets from a primary. The protections are the same three, configured **on the relay**: 1. `wal_keep_size` on the relay — the primary's value protects only standbys connected to the primary. 2. A physical replication slot created on the relay for the downstream, named in the downstream's `primary_slot_name`. 3. A WAL archive the downstream can read with `restore_command`. The relay's own settings floor applies too: like any hot standby, its `max_wal_senders` and the other shared-memory sizes must not be lower than the primary's. ## When to use it Cascade **read replicas and remote-site copies**. Keep **failover candidates and synchronous standbys** attached directly to the primary, where they can be synchronous and are visible in its `pg_stat_replication`. A short chain — one relay per remote site — usually captures the bandwidth benefit; every extra hop adds lag and one more server whose failure strands the standbys below it.

  • What happens to a downstream PostgreSQL standby when the cascading standby it streams from is promoted?
    With `recovery_target_timeline = 'latest'`, the default, the downstream standby follows the timeline switch and keeps streaming from the newly promoted server, which is now a primary. With a fixed timeline it would stop following. The old primary is then no longer upstream of anything in that branch.
  • How do you find the end-to-end lag of a two-hop PostgreSQL cascade?
    Read `pg_stat_replication` on the primary for the relay and on the relay for the downstream, since each shows only its direct standbys. For one number, compare the downstream's `pg_last_wal_replay_lsn()` with the primary's `pg_current_wal_lsn()` using `pg_wal_lsn_diff()`, sampled together.

A regional distributor collects each print run from the press and passes it on to local newsstands. The press deals with one distributor instead of every stand and keeps no list of the stands behind it; a stand is as late as the distributor plus its own delivery, and when the distributor stops, every stand behind it waits.

  • A cascaded standby can be listed in the primary's synchronous_standby_names.
  • The primary's pg_stat_replication shows every standby in the chain.
  • Feedback from a downstream standby stops at its upstream standby.
  • A cascading standby relays only streamed WAL, never WAL from the archive.
  • Cascading needs logical replication with a publication on the relay.