skip to content

Standby Promotion & Timelines

Promoting a PostgreSQL standby starts a new timeline, and the old primary must be rewound before it can follow. Interviewers probe why core has no automatic failover and how clients reconnect.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, how do you promote a streaming standby to primary, and why does a standby never promote itself when the primary fails?

level: juniorimportance: must knowfreq 64%

answer

  1. two entry points, one signal file
  2. shell on the host versus SQL
  3. promote file plus SIGUSR1
  4. no failure detector in core

basics

~20 s

Run pg_ctl promote on the standby's host or call SELECT pg_promote() over SQL; both end recovery and open the server for writes. Core PostgreSQL ships no failure detector, so promotion is always an operator's or HA tool's decision.

solid answer

~50 s

PostgreSQL 18 has two ways. `pg_ctl promote -D <datadir>` runs on the standby's own host as the OS user that owns the data directory; it writes a `promote` signal file, sends the postmaster `SIGUSR1` and waits by default (`-W` skips the wait, `-t` sets the timeout, 60 seconds unless `PGCTLTIMEOUT` overrides it). `SELECT pg_promote()` does the same from a SQL session, waits up to `wait_seconds` (default 60) and returns `true` or `false`; it is superuser-only unless `EXECUTE` is granted. The standby replays the WAL it already has, starts a new timeline, removes `standby.signal` and accepts writes. The old `promote_trigger_file` setting is gone since 16. A standby never promotes itself because core has no failure detection and no fencing: deciding the primary is really dead, and keeping it from coming back, is the job of a person or an HA manager such as Patroni.

code

sql · 7 lines
sql
-- on the standby, as a superuser or a role granted EXECUTE on pg_promote
SELECT pg_is_in_recovery();                         -- true: still a standby
SELECT pg_promote(wait => true, wait_seconds => 30); -- true once writable
SELECT pg_is_in_recovery();                         -- false: now a primary

-- let an automation role promote without being superuser
GRANT EXECUTE ON FUNCTION pg_promote(boolean, integer) TO ops_failover;

go deeper

for a junior

Name both ways, pg_ctl promote on the host and SELECT pg_promote() in SQL, and say that a standby becomes writable only when someone promotes it.

for a middle

Explain the promote signal file and SIGUSR1, the wait and wait_seconds defaults, and that the server starts a new timeline and removes standby.signal.

for a senior

Show why core never auto-promotes: ambiguous failure detection and the need to fence the old primary first, and who takes that decision in a real deployment.

for a principal

Treat promotion as one step of a failover policy: who may trigger it, what must be proven before it, and how the decision is recorded and reviewed.

## What promotion is A PostgreSQL **standby** is a server in continuous recovery: it replays WAL it receives from a primary, by streaming or from an archive, and with `hot_standby = on` (the default) it answers read-only queries. **Promotion** ends that recovery for good. The server stops waiting for more WAL, starts a new **timeline**, and begins accepting writes as a primary. Nothing happens to the old primary: promotion is a purely local act on the standby you promote. ## Two ways to trigger it Since PostgreSQL 12 recovery is configured with ordinary parameters and signal files, and PostgreSQL 18 has exactly two supported entry points: | | `pg_ctl promote` | `pg_promote()` | |---|---|---| | Where it runs | a shell on the standby's own host, as the OS user that owns the data directory | any SQL session connected to the standby | | Who may run it | the OS user that owns the data directory and the postmaster | superusers, or roles granted `EXECUTE` on it | | Waiting | waits by default; `-W` returns at once, `-t` sets the timeout (60 seconds unless `PGCTLTIMEOUT` says otherwise) | `wait => true` by default, for up to `wait_seconds` (default 60) | | Result | exit status 0 when promoted, non-zero if it timed out | `true` if promoted in time, `false` otherwise | Both do the same thing underneath: they create a file named `promote` in the data directory and send the postmaster `SIGUSR1`. The startup process notices the file and ends recovery. `pg_promote(wait => false)` returns `true` as soon as the signal is sent, which only means promotion was **requested**; poll `pg_is_in_recovery()` to see it finish. Both refuse a server that is not a standby. `pg_ctl` reports that the server is not in standby mode; `pg_promote()` raises `recovery is not in progress` with SQLSTATE `55000` (`object_not_in_prerequisite_state`). ## What the server does when it promotes 1. It stops trying to reach the primary. WAL that is **already available** in `pg_wal` or the archive is still replayed, but nothing more is requested from the primary. 2. It picks a new timeline ID, one higher than the newest timeline it can find in `pg_wal` or the archive, and writes a history file for it. 3. It removes `standby.signal` (or `recovery.signal`), so a restart does not put it back into recovery. 4. Instead of a full checkpoint it writes a lightweight **end-of-recovery** WAL record, opens for writes, and requests a checkpoint afterwards, which keeps promotion fast. 5. If archiving is on, it starts archiving the WAL it generates from now on. You confirm the result with `SELECT pg_is_in_recovery();`, which changes from `true` to `false`. ## Why a standby never promotes itself This is the part interviewers probe. A standby that loses its connection to the primary does not conclude that the primary is dead. It keeps cycling through the archive, `pg_wal` and streaming until it is stopped or promoted. The manual states plainly that PostgreSQL does not provide the software to detect a failed primary and notify the standby. Two reasons make that a deliberate boundary rather than a missing feature: - **Detection is ambiguous.** A lost connection can mean a dead primary or a network partition. A standby that promoted on every partition would create two writable primaries. - **Promotion needs fencing.** Before a standby takes over, the old primary must be prevented from accepting writes, and core PostgreSQL has no way to do that to another machine. So promotion is always decided outside the standby: by an operator following a runbook, or by an HA manager such as Patroni that combines health checks, a consensus store and fencing. How those managers elect a leader is the Patroni tree's subject; split-brain and fencing theory belong to the replication concept leaves. ## Removed mechanisms still found in old runbooks - **`recovery.conf` and `standby_mode`** were replaced in 12 by `standby.signal` plus ordinary parameters. The server refuses to start if a `recovery.conf` exists. - **`trigger_file`, later `promote_trigger_file`**, named a path the standby polled to decide to promote. PostgreSQL 15 was the last release with it; use `pg_ctl promote` or `pg_promote()`. - **Deleting `standby.signal` and restarting** is not promotion. Without a signal file the server runs only crash recovery and comes up on the **same timeline**, so the WAL it writes can carry the same file names as WAL the old primary wrote or archived.

  • What happens if you run pg_promote() on a PostgreSQL server that is already a primary?
    It fails with `recovery is not in progress`, SQLSTATE `55000`, because recovery control functions only work during recovery. `pg_ctl promote` refuses the same way, reporting that the server is not in standby mode. Neither does anything to the running primary, so a mistaken call is harmless.
  • When would you call pg_promote(wait => false), and what does its true result mean?
    When the caller cannot block, for example a script with its own timeout handling. With `wait => false` the function returns `true` as soon as it has created the `promote` file and signalled the postmaster; promotion may still be replaying WAL. The caller must then poll `pg_is_in_recovery()` until it returns `false` before sending writes.
  • Why is removing standby.signal and restarting not a safe substitute for promotion in PostgreSQL?
    Without a signal file the server performs only crash recovery and continues on the timeline it was replaying, instead of starting a new one. Its new WAL then uses the same segment file names as WAL the old primary may still write or archive, so the two histories cannot be told apart and can overwrite each other.

  • Setting promote_trigger_file is how you promote a standby in PostgreSQL 18.
  • Deleting standby.signal and restarting is an equivalent way to promote.
  • A standby promotes itself once wal_receiver_timeout expires.
  • pg_promote() run on the old primary promotes a remote standby.
  • pg_promote(wait => false) returning true means the server already accepts writes.
open as a page

After a PostgreSQL failover the old primary cannot follow the promoted standby's new timeline; how do you rejoin it with pg_rewind, and what must already be configured?

level: seniorimportance: must knowfreq 55%

basics

~20 s

With the old primary shut down, run pg_rewind against its data directory with --source-server pointing at the new primary and -R, then start it as a standby. It needs data checksums, on by default in initdb since 18, or wal_log_hints = on.

open as a page

How does a libpq connection string listing several PostgreSQL hosts with target_session_attrs=read-write find a newly promoted primary, and what does it not do?

level: middleimportance: should knowfreq 48%

basics

~20 s

libpq tries each listed host in order and, with target_session_attrs=read-write, keeps only a server not in hot standby and not default read-only. It acts only when connecting: open connections break at failover and the application must reconnect.

open as a page

In PostgreSQL, what is a timeline ID, what does a file such as 00000002.history contain, and why does promotion create one?

level: middleimportance: should knowfreq 44%

basics

~20 s

A timeline ID numbers one branch of WAL history and forms the first eight hex digits of every WAL file name. Promotion starts a new timeline and writes a .history file listing each ancestor timeline and the LSN where it branched off.

open as a page

In a planned PostgreSQL 18 switchover for a ticketing platform, in what order do you swap the primary and its streaming standby so nothing committed is lost and the old primary rejoins without pg_rewind?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Confirm the standby is streaming and caught up, stop the primary with a fast shutdown, check the standby replayed the primary's shutdown checkpoint, promote it, then start the old primary with standby.signal and primary_conninfo pointing at the new one.

open as a page