In PostgreSQL 18, what makes a server start as a streaming standby, and what can clients do while connected to it?
answer
- a file, not recovery.conf
- a libpq string on the standby
- hot_standby is on by default
- no transaction ID on a standby
basics
~20 sAn 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 sSince 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# 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: replicationgo deeper
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.
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.
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.
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.