skip to content

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%

answer

  1. priority versus quorum
  2. names are application_name values
  3. later names wait as replacements
  4. sync_state column, four values

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.

solid answer

~40 s

`synchronous_standby_names` is set on the primary and changed with a reload; empty, the default, means asynchronous replication. Its names are matched case-insensitively against each standby's `application_name`, set in the standby's `primary_conninfo` (default: `cluster_name`, else `walreceiver`). `FIRST n (s1, s2, s3)` is priority-based: commits wait for the first `n` listed standbys that are connected and in `streaming` state, and the rest are potential replacements. `ANY n (s1, s2, s3)` is quorum-based: commits wait for any `n` of them. A plain list means `FIRST 1`, and `*` matches any name. In `pg_stat_replication`, `sync_state` reads `sync` or `potential` under `FIRST`, `quorum` under `ANY`, `async` for unlisted standbys. Whether a commit waits at all is still `synchronous_commit`'s decision, and cascaded standbys can never be synchronous.

code

ini · 4 lines
ini
# postgresql.conf on the newsroom primary
synchronous_standby_names = 'ANY 1 (standby_a, standby_b)'
# each standby names itself in its own primary_conninfo:
#   primary_conninfo = '... application_name=standby_a'

go deeper

for a junior

Recall that synchronous_standby_names lives on the primary, lists standby application names, and is empty, meaning asynchronous, by default.

for a middle

Explain FIRST as priority with potential replacements and ANY as a quorum, how names come from primary_conninfo's application_name, and what each sync_state value means.

for a senior

Show operational judgement: list more candidates than required, release stuck commits by emptying the setting and reloading, keep reporting and cascaded standbys out, and know a cancelled wait is already committed locally.

for a principal

Weigh FIRST against ANY for the failover design: knowing which server holds every acknowledged commit versus taking the fastest acknowledgement, and how either interacts with the HA manager choosing the next primary.

## Where the setting lives `synchronous_standby_names` is set on the **primary** (and, so it is ready after a failover, usually on every standby that might become primary). It changes with a reload. Empty — the default — means replication is asynchronous and no commit waits for any standby. The names in it are **not host names**. Each one is matched, **case-insensitively**, against the `application_name` that a standby sends when its WAL receiver connects — for a physical standby, the value inside its `primary_conninfo`. If none is given there, the standby uses its `cluster_name`, and if that is empty too, the literal `walreceiver`. PostgreSQL does not enforce unique names; with duplicates, which standby gets priority is indeterminate. ## The three syntaxes | Form | Example | Meaning | |---|---|---| | `FIRST n (list)` | `FIRST 1 (standby_a, standby_b)` | priority: wait for the `n` highest-priority listed standbys that are streaming | | `ANY n (list)` | `ANY 2 (standby_a, standby_b, reporting)` | quorum: wait for at least `n` of the listed standbys | | plain list | `standby_a, standby_b` | the pre-9.6 form, identical to `FIRST 1 (...)` | `FIRST` is optional, so `2 (s1, s2, s3)` is a priority setting. `n` must be greater than zero, and the special entry `*` matches any standby name. ## FIRST: priority with replacements Suppose a newsroom's primary has two high-availability standbys, `standby_a` and `standby_b`, and a reporting standby named `reporting`. With `FIRST 1 (standby_a, standby_b)`: 1. `standby_a`, earliest in the list and streaming, is the **synchronous** standby; commits wait for it. 2. `standby_b` is a **potential** standby: asynchronous now, made synchronous at once if `standby_a` disconnects. 3. `reporting` is not listed, so it stays **async** whatever happens. The cost of `FIRST` is that commit latency is tied to one specific server: if `standby_a` is slow but connected, every commit pays for it even when `standby_b` would answer sooner. ## ANY: quorum With `ANY 1 (standby_a, standby_b)`, each commit proceeds as soon as **at least one** of the two has acknowledged, so the faster one answers. Every listed standby that is streaming stays a candidate all the time. Because the set that actually acknowledged changes from commit to commit, PostgreSQL does not label any of them `sync`: they show `quorum`, and `sync_priority` has no effect on the choice. ## Reading the result in pg_stat_replication On the primary, one row per directly connected standby shows the outcome: - `application_name` — the name that was matched. - `state` — only a standby in `streaming` state can count; one still in `catchup` cannot be synchronous yet. - `sync_priority` — the list position used by `FIRST`; `0` for an unlisted standby. - `sync_state` — `sync`, `potential`, `quorum` or `async`. The primary also logs the change, for example `standby "standby_a" is now a synchronous standby with priority 1`, or that a standby `is now a candidate for quorum synchronous standby` under `ANY`. ## What the setting does not decide - **Whether a given commit waits at all**, and for which stage (received, flushed or replayed), is `synchronous_commit`'s job — at `local` or `off` a commit never waits, whatever standbys are listed. Those levels are a separate subject. - **Cascaded standbys** — ones streaming from another standby — cannot be synchronous; the setting has no effect on cascading replication. - **What happens when the list cannot be satisfied.** Commits that need it wait indefinitely, still holding their locks. Emptying the setting with `ALTER SYSTEM SET synchronous_standby_names = ''` and `SELECT pg_reload_conf()` wakes every waiting backend. Cancelling one waiting commit returns a warning that the transaction **has already committed locally** but might not have been replicated — it is not rolled back. ## Choosing a form - List **more candidates than `n`**: `FIRST 1 (standby_a, standby_b)` and `ANY 1 (standby_a, standby_b)` both survive the loss of one standby, while `FIRST 1 (standby_a)` stalls commits the moment `standby_a` goes away. - Prefer **`ANY`** when the standbys are peers and you want the fastest acknowledgement. - Prefer **`FIRST`** when one standby is the designated failover target and you want to know which server holds every acknowledged commit. - Keep a **reporting standby out of the list**: under `synchronous_commit = remote_apply`, its replay — which long reporting queries can hold back — would become every commit's latency.

  • On a PostgreSQL primary, how do you release commits stuck waiting for a synchronous standby that will not come back?
    Set `synchronous_standby_names` to a list that can be satisfied, or to `''`, and reload (`ALTER SYSTEM ...` then `SELECT pg_reload_conf()`); emptying it wakes every waiter. Cancelling a single waiting commit only warns that the transaction has already committed locally but might not have been replicated — it is not undone. Prevent it by listing more candidates than the number required.
  • Why do PostgreSQL standbys sometimes all report the same application_name, and why does that matter here?
    Without `application_name` in `primary_conninfo`, a physical standby reports its `cluster_name`, and if that is empty, `walreceiver`. Several standbys then share one name, PostgreSQL enforces no uniqueness, and which of them counts as the synchronous one becomes indeterminate. Give each standby a distinct `application_name` in its `primary_conninfo`.
  • Can a PostgreSQL standby that streams from another standby be synchronous?
    No. Cascading replication is asynchronous; synchronous replication settings have no effect on it. The primary's `pg_stat_replication` does not even list a cascaded standby — only its upstream standby does. Standbys meant to acknowledge commits must connect directly to the primary.

  • The names in synchronous_standby_names are standby host names or IP addresses.
  • FIRST 2 (s1, s2, s3) makes every commit wait for all three standbys.
  • A standby still in catchup state already counts as synchronous.
  • Listing standbys makes every commit wait, even with synchronous_commit = local.
  • Under ANY, sync_priority decides which standbys are counted.
  • Changing synchronous_standby_names requires restarting the primary.