PostgreSQL physical replicas and promotion

A PostgreSQL physical standby starts with a copy of a database cluster and follows its write-ahead log, or WAL. It can answer read-only queries while recovery keeps applying changes. If it must take over writes, the useful question is how much of the chosen history it can recover, alongside whether the former writer has been excluded.

Follow the shop's independent price change: P7, the blue mug, rises from 20.00 to 22.00. Standby A has its commit durably stored but has not replayed it; B is still before the change. Both can answer 20.00, yet only A has shown that it can recover 22.00. We will make that distinction visible in PostgreSQL's measurements, commit settings and handover.

Replicas, lag and read routing establishes the read promises, and Failover and fencing establishes writer exclusion and reconciliation. Here the commands and views are PostgreSQL 18's. Order 103 stays pending with no payment and agreed line prices totalling 29.00; stock remains P3 10, P7 0 and P9 4. Call the price transaction’s WAL update record U and its commit record C. These are symbolic names, not native addresses or elapsed times.

A standby follows the cluster's WAL

The primary's WAL sender streams records to the standby's WAL receiver. The receiver writes them to local WAL; the startup process performs recovery, applying those records to the standby's pages. The application does not send its UPDATE to the standby a second time. A compatible base copy supplies the pages to which later WAL applies.

Physical replication follows the database cluster, including its databases and roles, rather than a selected products table. Use the same PostgreSQL major version and hardware architecture, with matching minor releases preferred. Unlogged-table contents are not replicated. Selecting rows or tables for a separate destination is the different lifecycle described in PostgreSQL logical replication and change feeds.

Create a streaming standby

For one direct standby, prepare the primary with wal_level = replica or higher, enough max_wal_senders for the standby and backup connections, and capacity in max_replication_slots for its slot. Permit a dedicated login with REPLICATION through a narrowly scoped pg_hba.conf rule. Use authenticated, protected transport; the replication login can obtain the cluster's contents.

On A, with its server stopped and a new empty data directory owned by PostgreSQL, take a full base backup. This shell example assumes the connection service shop_primary_replication already names the verified upstream and protected credentials. The destination and slot are deliberately new.

pg_basebackup --dbname='service=shop_primary_replication application_name=shop_a' \
  --pgdata=/srv/postgresql/shop-a --format=plain --wal-method=stream \
  --checkpoint=spread --write-recovery-conf \
  --create-slot --slot=shop_a

--wal-method=stream includes the WAL needed to make the backup consistent and uses another sender connection. --write-recovery-conf, also called -R, creates standby.signal and records the upstream connection and slot in postgresql.auto.conf. The signal file makes the next start enter standby mode. The slot retains WAL that this consumer still needs after the backup; someone must own its storage cost if A never starts.

Check the copied configuration before starting A: its listener, paths, tablespaces, recovery connection and any inherited synchronous policy must fit this server. Start it with hot_standby = on for reads. On this exact server, verify its identity and role, then inspect the actual sender connection:

SELECT current_setting('cluster_name') AS configured_name,
       inet_server_addr(), inet_server_port(), pg_is_in_recovery();
SHOW transaction_read_only;

SELECT status, sender_host, sender_port, slot_name,
       receive_start_tli, received_tli, written_lsn, flushed_lsn
FROM pg_stat_wal_receiver;

The configured name is a label, not proof of identity. Match it with the endpoint and deployment inventory. Expect recovery to be true, transactions to be read-only and the receiver to name the intended primary and slot. A consistent standby can answer reads before it has caught up. Keep its replication progress as a separate readiness condition.

Written, flushed and replayed WAL

WAL arrival, a filesystem write, a durable flush and replay are separate events. A standby can continue receiving and flushing records while its replay is paused. Durable receipt of C then protects recoverable history without yet changing what a new read sees.

After the flush, A has the committed price in recoverable WAL, while its new read still sees 20.00. Once replay includes C, a new snapshot can see 22.00. A report that established an earlier Repeatable Read snapshot can continue to see 20.00. Catching up the server does not replace that snapshot.

A separate drill on native PostgreSQL 18.6 reproduced the durable-but-unreplayed distinction. With A's replay deliberately paused, the primary's price transaction completed using synchronous_commit = on. A's synced receive position reached 0/40013A8, while replay stayed at 0/4000000 and a fresh read returned 20.00. A later no-op price update using remote_apply was observed waiting at SyncRep. Resuming A's replay allowed that commit to complete and a new read returned 22.00.

The drill used a primary and two standby processes with separate directories and Unix sockets inside one isolated Linux container. It checks server behaviour on surviving storage; power loss, independent machine failures and external fencing were outside the test.

Read the progress evidence

On the primary, pg_stat_replication has a row for each directly connected WAL sender. Its remote positions come from standby reports, so they can trail the standby's actual progress. A cascading standby's downstream copies need inspection there too.

SELECT application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS sent_not_written,
       pg_wal_lsn_diff(flush_lsn, replay_lsn) AS flushed_not_replayed,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

On A itself, pg_stat_wal_receiver.written_lsn and flushed_lsn separate receiver writes from flushes. There is a naming trap in the recovery functions:

SELECT pg_last_wal_receive_lsn() AS received_and_synced,
       pg_last_wal_replay_lsn()  AS replayed,
       pg_is_wal_replay_paused() AS replay_pause_requested;

pg_last_wal_receive_lsn() already includes sync to disk. It does not merely report arrival into volatile memory. It can be null before streaming starts; after recovery ends it stays at the last streamed-and-synced position. The replay function similarly retains its recovery position after promotion. Neither frozen value proves that a server is still a standby.

A growing flush-to-replay gap directs attention to recovery: a deliberate pause, query conflicts, I/O or work competing for capacity. A gap before durable receipt directs attention upstream, through transport and receiver storage. Sample in fresh short transactions and correlate both sides. A byte difference measures backlog, while write_lag, flush_lag and replay_lag describe recent delays including the sender learning about progress. They do not predict catch-up time. Subtracting the last replayed transaction's timestamp from now also makes an idle caught-up server look increasingly late.

Check the copy that will answer

Suppose the price editor must read its confirmed change from A. A primary connection can obtain a conservative required position after the successful commit:

BEGIN;
SET LOCAL synchronous_commit = on;
UPDATE products SET price = 22.00 WHERE product_id = 'P7';
COMMIT;
-- Only after the client has received successful COMMIT:
SELECT pg_current_wal_insert_lsn() AS required_lsn;

That server-wide insertion position is at or beyond this transaction's commit record; concurrent work may put it further ahead. It is not the exact commit address, and obtaining it does not identify an uncertain commit outcome. Carry it with the cluster system identifier and the relevant timeline ancestry, under a topology protocol that invalidates it when history changes.

Within that validated history, poll the following condition on the exact connection to A, binding the required position as $1. Give the application loop a finite deadline; a null replay position is not ready.

SELECT pg_is_in_recovery()
       AND coalesce(pg_last_wal_replay_lsn() >= $1::pg_lsn, false)
         AS ready;

When it succeeds, run the price query in a subsequent Read Committed statement, or start a new read transaction afterwards. Do not let a pool move the read to B or reuse an earlier Repeatable Read snapshot. On timeout or a changed topology, take the declared primary fallback or return that the freshness promise cannot be met. The general routing choices remain with the prerequisite article.

A numeric LSN contains no timeline identity. Two branches can pass the same numeric address with different contents. PostgreSQL's replication-protocol IDENTIFY_SYSTEM reports the system identifier and timeline; timeline history records establish ancestry. A connection address or matching system identifier alone cannot prove that a promoted branch contains the required commit.

What synchronous commit waits for

Two settings choose the remote promise: synchronous_standby_names selects the required copies, and synchronous_commit selects their required progress. With reliable storage and normal safeguards, all values except off require the primary's own WAL flush. The extra wait differs:

With synchronous_standby_names empty, on enables no remote wait. All the non-off values then have the same local flush behaviour. Use SET LOCAL for a deliberate transaction-specific choice so a borrowed application connection does not keep a weaker setting for its next caller. Local durability is developed in PostgreSQL WAL, checkpoints and durability.

Choose the required standbys

The names match standby application_name values in primary_conninfo. Give each copy a distinct name and inspect sync_state. For the two copies here, compare these policies:

FIRST is priority selection; ANY accepts the specified count from the list. Neither elects a primary or fences the old one. Under ANY 1, C may be on A alone; a later commit may be acknowledged by B after it catches up. Candidate eligibility must cover the actual confirmed history and policy changes, rather than remembering that a server was once synchronous.

If the required acknowledgements cannot arrive, commits wait. With remote_apply, paused replay can hold a write even while receipt and flush advance. A missing required copy can hold writes while the primary itself remains healthy. Choosing local to release future work accepts a different survival promise; it is an incident decision to record, not a way to retain the old promise for free.

A commit waiting for a synchronous reply has already passed its local commit work. A client timeout, lost connection or cancelled wait does not establish rollback. Recover the request's outcome using Retries and uncertain commit outcomes, preserving its original identity.

Keep the standby able to recover

A useful standby needs both a complete WAL chain and enough capacity to apply it. A receiver marked streaming does not prove either future resumability or a bounded time to useful service.

Slots, archives and missing WAL

A physical slot keeps WAL from its restart_lsn, the oldest position its consumer may still need. Inspect retention on the sending server:

SELECT slot_name, active, restart_lsn, wal_status,
       safe_wal_size, invalidation_reason,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
         AS retained_span_bytes
FROM pg_replication_slots
WHERE slot_type = 'physical';

That span is an approximate history obligation, not an exact directory-size count. Compare its growth with actual WAL storage and free space. A disconnected A with an unlimited slot can keep retaining while the primary writes. max_slot_wal_keep_size caps slot retention at checkpoint time, trading storage protection for the possibility that A loses required segments. It is not a hard whole-directory quota.

wal_status distinguishes reserved or extended retention from unreserved, where required files are liable to removal, and lost, where the slot is unusable. safe_wal_size gives the remaining headroom before the slot risks loss; null also covers unlimited retention or an already lost slot. Read it with the status. Check configured idle-slot invalidation too.

wal_keep_size separately retains a minimum recent amount, without tracking each consumer's needs. If A asks for a segment that is gone, a usable archive can supply it through restore_command. Without a complete chain from its base state, take a fresh base copy. Do not skip the segment or delete files from pg_wal. Before removing an abandoned slot, decide whether its consumer will return, recover from archive or be rebuilt. Restoring PostgreSQL to a recovery target owns the archive and backup chain.

Queries that delay recovery

Return to the report whose snapshot includes P7 at 20.00. After A replays C, new snapshots can read 22.00 while that report still needs the old tuple. Later cleanup on the primary can generate WAL that removes it. When this cleanup record reaches recovery, recovery must wait or cancel the conflicting read. The conflict holds subsequent WAL back; it does not hide the already replayed C from new snapshots.

max_standby_streaming_delay and max_standby_archive_delay limit the delay recovery may tolerate for the corresponding WAL source. They are replay-delay budgets, not a fresh timeout for each query. A query can inherit little remaining grace after previous delays. Short budgets favour a ready failover copy; long budgets favour reports at the cost of lag. Inspect pg_stat_database_conflicts and the server logs to identify the actual conflict.

hot_standby_feedback sends a retention horizon upstream so cleanup can preserve tuples needed by standby snapshots. That shifts cost to the primary: dead versions can accumulate. It does not prevent every recovery conflict, such as replay of conflicting DDL locks. A reporting standby and a failover candidate may therefore need different policies. Coordinate the primary's retained horizons with PostgreSQL vacuum and transaction age.

Choose the history to promote

Now the primary is unreachable. Before promoting anything, enforce the fencing protocol from the prerequisite: confirm the former writer cannot serve authoritative writes and cannot restart as one. PostgreSQL's promotion command performs neither failure detection nor that exclusion.

Identify each candidate's system identifier, timeline ancestry, recovery state and recoverable WAL. A's durable C can be replayed even though its last price query said 20.00. B's identical answer hides the missing commit. Under a policy that required A's durable acknowledgement for C, choosing B without obtaining C sacrifices that confirmed edit.

An emergency may leave only a possible loss boundary. Last-known replay or lag is incomplete evidence if the source continued writing afterwards. Preserve what is known, which commit settings were in use and which surviving histories contain the acknowledged work. If no candidate meets the promise, retaining the promise means waiting for a qualifying source or keeping writes unavailable. Accepting possible loss must be explicit.

Switchover with a known boundary

A planned transfer can establish more. Stop write admission and drain in-flight work, including workers and other direct writers. After the final committed work, record a final source WAL boundary and its history identity. Confirm a completed source shutdown or fence and keep restart exclusion in force. Confirm the candidate has the required durable WAL and can replay through that boundary before opening the new writer.

A clean source shutdown can generate additional WAL; obtain or preserve what recovery actually needs rather than treating an earlier application boundary as the end of all server activity. If stopping the source prevents the candidate obtaining missing WAL, keep service closed while retrieving it from an approved surviving source. Reopening writes to “help it catch up” invalidates the final boundary.

Promote and verify the new primary

With the authority and history gates satisfied, issue promotion on the verified candidate. These are alternatives, using the server's data directory or an authorised connection to that server:

pg_ctl --pgdata=/srv/postgresql/shop-a --wait --timeout=60 promote

SELECT pg_promote(wait => true, wait_seconds => 60);

Promotion leaves recovery after applying immediately available archive or local WAL; it does not reconnect to the old primary to find missing history. Recovery may therefore advance beyond the last sampled replay position. A successful waited call confirms promotion completion. A timeout needs further inspection: it does not prove that promotion failed or authorise a competing promotion.

Check this server again with pg_is_in_recovery(), now false, its current timeline through the replication protocol, and a fresh read of expected business facts. Promotion creates a new timeline. Recovery-position functions retain their old values, so do not use them as a new primary's current WAL counters.

In the drill, the former primary's clean stop completed before A was promoted with a waited pg_ctl call. A then reported recovery false and its current WAL filename began 00000002, identifying timeline 2. Its latest completed checkpoint still reported timeline 1 at that moment. Checkpoint metadata describes that checkpoint; promotion completion does not promise an immediate new-timeline checkpoint. Fresh row checks found the retained price and order facts below.

SELECT p.price AS catalogue_price, p.stock AS mug_stock,
       o.status,
       (SELECT sum(quantity * unit_price) FROM order_items
        WHERE order_id = 103) AS line_total,
       (SELECT count(*) FROM payments WHERE order_id = 103)
         AS payment_count
FROM products p CROSS JOIN orders o
WHERE p.product_id = 'P7' AND o.order_id = 103;
-- A after recovering C: 22.00 | 0 | pending | 29.00 | 0
-- B without C:         20.00 | 0 | pending | 29.00 | 0

The row check complements the history and authority evidence; it cannot prove them. Admit writes only after those checks pass. Replace old pooled connections and direct worker targets, then verify the new endpoint under the intended caller identities. Account for requests whose replies were lost during the transfer. A changed DNS record does not replace an existing connection.

Return the former primary as a standby

Keep the former primary excluded while preserving its data, WAL and outside request evidence. A rewind or new base copy replaces state. If B omitted C, neither operation merges the confirmed price edit into B's new history. Reconciliation of accepted operations must be resolved separately under Failover and fencing.

pg_rewind can reuse unchanged blocks when source and target share an ancestor. The target is the former primary being replaced; the source is the chosen new primary. The target needs data checksums or wal_log_hints enabled before the divergent work, with full_page_writes = on. Required target WAL back to divergence and source WAL for subsequent recovery must be available. Turning hints on afterwards cannot recreate missing history.

With the target cleanly shut down, preserved evidence and an authorised source connection, first assess those prerequisites and run the supported rewind procedure. This shell outline names both directions explicitly:

pg_rewind --target-pgdata=/srv/postgresql/former-primary \
  --source-server='service=shop_new_primary_rewind' \
  --write-recovery-conf

-R writes standby recovery configuration for the source connection. Recheck it before starting: rewind copies configuration too, so listener settings, paths and upstream credentials may need correction. Configure an appropriate replication login and deliberately owned physical slot for ongoing streaming, plus archive access where needed. Verify standby.signal is present. If rewind's prerequisites fail, or a failed rewind leaves the directory unusable, create a fresh base copy instead.

Verify the restored protection

Start the returned copy in recovery. Verify its endpoint, system identifier and chosen timeline history, then its receiver's actual upstream and slot. On the new primary, verify streaming, flush and replay advancing for the returning copy. Check the intended synchronous policy explicitly: copied settings may refer to a departed standby or to the server itself. A policy waiting for that missing name can leave the promoted server's commits blocked.

Re-establish the intended slots and required standby set, test the chosen commit promise and check callers' routing. A successful promotion restores a writer. Only a qualified surviving standby, working retention and the intended acknowledgement policy restore the planned protection. Rehearse the return as well as the promotion, with a paused replay, an unavailable required copy and a former primary that tries to return.

The drill preserved the former primary's directory and returned it using a fresh full base copy of A. The returned process was in recovery, streaming from A on timeline 2 through its new physical slot. After explicitly selecting it as the required standby, a remote_apply commit completed and its fresh read returned 22.00 | 0 | pending | 29.00 | 0. This exercised fresh-copy return; the rewind procedure above was checked against the manual.