skip to content

Streaming Standbys

A PostgreSQL physical standby replays the primary's WAL stream and can serve read-only queries. Interviewers probe lag, synchronous quorum and why standby queries get cancelled.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL 18, what makes a server start as a streaming standby, and what can clients do while connected to it?

level: juniorimportance: must knowfreq 72%

answer

  1. a file, not recovery.conf
  2. a libpq string on the standby
  3. hot_standby is on by default
  4. no transaction ID on a standby

basics

~20 s

An empty standby.signal file in the data directory plus primary_conninfo pointing at the primary make PostgreSQL start in standby mode and stream WAL; with hot_standby on (the default) clients may run read-only queries, never writes.

solid answer

~40 s

Since PostgreSQL 12 there is no `recovery.conf`: a server enters standby mode when it starts and finds a `standby.signal` file in its data directory. `primary_conninfo`, an ordinary libpq connection string in `postgresql.conf` or `postgresql.auto.conf`, tells its WAL receiver where to stream from; the primary needs a role with the `REPLICATION` attribute, a `pg_hba.conf` line whose database field is `replication`, and spare `max_wal_senders`. With `hot_standby = on`, the default, the standby accepts connections once replay reaches a consistent state and serves read-only work: `SELECT`, `COPY ... TO`, cursors, `SET`. Anything that needs a transaction ID or writes WAL fails with SQLSTATE `25006`: DML, DDL (temporary tables included), `SELECT ... FOR UPDATE`, `nextval()`, `LISTEN`/`NOTIFY`. `pg_is_in_recovery()` returns true there.

code

ini · 5 lines
ini
# postgresql.conf on the newsroom reporting standby;
# its data directory also holds an empty standby.signal file
primary_conninfo = 'host=newsroom-primary port=5432 user=replicator application_name=reporting'
hot_standby = on          # the default; restart-only
# the password lives in ~/.pgpass, database field: replication

go deeper

for a junior

Recall the two pieces: an empty standby.signal file and a primary_conninfo connection string on the standby. Know that a hot standby answers read-only queries only.

for a middle

Explain why the read-only rule exists: standby transactions get no transaction ID and write no WAL, so DML, DDL, temporary tables, row locks and nextval all fail with SQLSTATE 25006.

for a senior

Show the operational traps: max_connections and friends must not be lower than the primary's, primary_conninfo reloads while hot_standby needs a restart, and a forgotten standby.signal yields a silently writable copy.

for a principal

Frame standbys as a fleet: one template for primary_conninfo, pg_hba replication lines and parameter floors, so any standby can be rebuilt or promoted without hand-edited differences.

## What a streaming standby is A **streaming standby** is a PostgreSQL server that started from a copy of the primary's data directory and then keeps applying the primary's **write-ahead log (WAL)** — the record of every change — as it arrives over a replication connection. A **WAL receiver** process on the standby connects to the primary, where a **WAL sender** process streams WAL to it as it is generated, without waiting for a segment file to fill. A separate **startup process** on the standby replays that WAL into the data files. When the standby also accepts client connections it is called a **hot standby**. ## What makes a server start as a standby Since PostgreSQL 12 there is no `recovery.conf` and no `standby_mode` setting. Three things are needed: 1. **A data directory copied from the primary** — a base backup. Taking it (for example with `pg_basebackup -R`, which also writes the next two items for you) is a backup topic of its own. 2. **An empty `standby.signal` file** in the data directory. At startup the server looks for it; if present, it enters standby mode and stays there until it is promoted. 3. **`primary_conninfo`** — a libpq connection string naming the server to stream from, set in `postgresql.conf` or `postgresql.auto.conf`. With neither it nor a `restore_command`, the server only warns and polls its own `pg_wal` directory. | Setting or file | Set on | Default | How a change takes effect | |---|---|---|---| | `standby.signal` | standby data directory | absent | read at server start | | `primary_conninfo` | standby | empty | reload; the WAL receiver restarts with the new string | | `hot_standby` | standby | `on` | server restart only | | `max_wal_senders` | primary (and any cascading standby) | `10` | server restart only | ## What the primary has to allow - A role with the **`REPLICATION` attribute** (or a superuser) for the standby to log in as. - A `pg_hba.conf` line whose database field is the keyword **`replication`** for the standby's address — an ordinary database name does not match replication connections. - Enough **`max_wal_senders`** for every standby plus any base backup running at the same time. - A password, given in `primary_conninfo` or, better, in `~/.pgpass` on the standby with `replication` as the database name. ## What clients can do on a hot standby With `hot_standby = on` the standby accepts connections once replay has reached a **consistent state**. Every transaction there is read-only: `transaction_read_only` is always true and cannot be switched off. Transactions on a standby are never assigned a transaction ID and cannot write WAL, and that one rule decides what works: - **Allowed:** `SELECT`, `COPY ... TO`, cursors (`DECLARE`, `FETCH`, `CLOSE`), `SHOW`/`SET`/`RESET`, `BEGIN`/`COMMIT`, savepoints, `PREPARE`/`EXECUTE`, and `LOCK TABLE` only in `ACCESS SHARE`, `ROW SHARE` or `ROW EXCLUSIVE` mode. - **Rejected:** `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `COPY ... FROM`, `TRUNCATE`; all DDL, **temporary tables included**; `SELECT ... FOR UPDATE`/`FOR SHARE`; `nextval()` and `setval()`; `LISTEN` and `NOTIFY`; the two-phase commit commands. A rejected write fails with SQLSTATE `25006` (`read_only_sql_transaction`). A session can check where it is with `SELECT pg_is_in_recovery()` or `SHOW in_hot_standby`. ## Settings that must keep up with the primary Some shared-memory sizes must be **at least as large on the standby as on the primary**: `max_connections`, `max_prepared_transactions`, `max_locks_per_transaction`, `max_wal_senders` and `max_worker_processes`. The WAL records the primary's values; if a hot standby replays a higher value than its own, it logs `hot standby is not possible because of insufficient parameter settings` and pauses recovery until it is reconfigured and restarted. Raise these on the standbys first; lower them on the primary first. ## Checking that it streams On the standby, `pg_stat_wal_receiver` shows a row with `status` `streaming`, the `sender_host` and the `flushed_lsn`. On the primary, `pg_stat_replication` shows one row per directly connected standby, whose `state` moves from `catchup` to `streaming` once the standby has caught up for the first time. A short checklist after building a standby: 1. `pg_is_in_recovery()` returns true on the standby. 2. `pg_stat_wal_receiver` shows `streaming` and the expected `sender_host`. 3. The primary's `pg_stat_replication` has a row with the standby's `application_name`. 4. A write attempted on the standby fails with `25006`. If `standby.signal` was forgotten, a restored base backup comes up as an independent, writable server instead of a standby — a silent divergence — so check `pg_is_in_recovery()` after every rebuild.

  • What happens if a PostgreSQL 18 data directory still contains a recovery.conf file?
    The server refuses to start: it fails with `using recovery command file "recovery.conf" is not supported`. Support was removed in PostgreSQL 12. Move each setting (`primary_conninfo`, `restore_command`) into `postgresql.conf` or `postgresql.auto.conf`, delete `recovery.conf`, and create `standby.signal` to replace the old `standby_mode = on`.
  • A PostgreSQL hot standby paused replay right after someone raised max_connections on the primary; why?
    Some shared-memory sizes — `max_connections`, `max_prepared_transactions`, `max_locks_per_transaction`, `max_wal_senders`, `max_worker_processes` — must be at least the primary's value on a hot standby. The change is WAL-logged; when the standby replays a higher value it warns that hot standby is not possible and pauses recovery. Raise the setting on the standby and restart it. Next time, raise standbys first.
  • Can the newsroom's reporting team create a temporary table on a PostgreSQL hot standby for an intermediate result?
    No. Creating any table, temporary ones included, means writing system catalogs, and a standby transaction gets no transaction ID and writes no WAL. The usual workarounds are a CTE or subquery in the same statement, or running that step on a writable server — the primary, or a logical replica with its own tables.

  • Standby mode is switched on with standby_mode = on in recovery.conf.
  • primary_conninfo is set on the primary and names each standby.
  • A hot standby can create temporary tables for scratch work.
  • SELECT ... FOR UPDATE works on a standby because it only reads.
  • Turning hot_standby on or off only needs a configuration reload.
open as a page

In PostgreSQL, how do the FIRST and ANY forms of synchronous_standby_names pick the standbys a commit waits for, and how does pg_stat_replication show it?

level: middleimportance: must knowfreq 55%

basics

~20 s

FIRST n (list) waits for the n highest-priority listed standbys that are streaming, later names standing by as replacements; ANY n (list) waits for any n listed standbys. pg_stat_replication.sync_state shows sync, potential, quorum or async.

open as a page

On a PostgreSQL primary, how do you turn pg_stat_replication's LSN columns into byte lag per stage, and what do write_lag, flush_lag and replay_lag measure instead?

level: middleimportance: should knowfreq 52%

basics

~20 s

Subtract the standby's sent_lsn, write_lsn, flush_lsn and replay_lsn from pg_current_wal_lsn() or from each other with pg_wal_lsn_diff() to get bytes behind per stage; the *_lag intervals time how long recent WAL took to be written, flushed and replayed.

open as a page

On a PostgreSQL streaming standby serving a newsroom's reporting team, long queries fail with 'canceling statement due to conflict with recovery'; why does this happen, and what are your options?

level: seniorimportance: should knowfreq 48%

basics

~20 s

Replay of the primary's WAL must not wait forever: when a record, such as vacuum cleanup or an ACCESS EXCLUSIVE lock, conflicts with a standby query, replay waits up to max_standby_streaming_delay (30 s) and then cancels the query with SQLSTATE 40001.

open as a page

A PostgreSQL standby without a replication slot stops streaming with 'requested WAL segment has already been removed'; what happened, and how do wal_keep_size and restore_command help?

level: seniorimportance: should knowfreq 36%

basics

~20 s

The primary recycled WAL past its last checkpoint before the slotless standby fetched it. wal_keep_size (default 0, in megabytes) keeps a minimum of past WAL in pg_wal; a restore_command reading a WAL archive lets the standby catch up from there.

open as a page

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%

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.

open as a page