Configure PostgreSQL streaming replication and planned failover
Build and operate a PostgreSQL 18.4 hot standby with bounded WAL retention, verified lag, explicit fencing, planned promotion, endpoint cutover, and safe rewind or rebuild.
Create a rehearsed physical-replication pair and perform a zero-observed-loss planned switchover without allowing two writable primaries.
- PostgreSQL 18.4
- Ubuntu Server 24.04 LTS
- Two isolated PostgreSQL hosts Same PostgreSQL 18.4 minor release, architecture, extensions, locale, tablespaces, checksums policy, durable storage, synchronized time, and private network.
postgres --version && timedatectl show -p NTPSynchronized - Verified physical backup and WAL archive An independently stored, monitored archive and a recent backup that has passed `pg_verifybackup` and isolated recovery.
sudo -u postgres psql -X -c "SELECT * FROM pg_stat_archiver;" - Fencing authority Owned mechanism to remove the old primary from clients, stop/mask service and automation, and control any shared storage or power boundary.
- Application write-drain plan Inventory and owners for APIs, workers, schedulers, ETL, migrations, maintenance and direct database users.
- TLS and secret delivery Server certificate SAN matches private endpoint; trusted CA and protected replication credential exist on standby.
OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.
Full guide
What you will build
- A PostgreSQL 18.4 physical streaming-replication pair on Ubuntu 24.04 LTS, with one writable primary, one read-only hot standby, encrypted and authenticated transport, a dedicated replication role, a physical replication slot, bounded WAL retention, and monitoring that distinguishes transport, flush, replay, and application-visible lag.
- A controlled planned-switchover procedure that freezes and drains writers, proves the target standby has replayed the agreed LSN, fences the old primary before promotion, promotes exactly one standby, moves the client endpoint only after validation, and records the new timeline.
- A rejoin procedure that treats the old primary as untrusted after divergence, uses `pg_rewind` only when prerequisites and dry-run evidence pass, falls back to a new base backup when they do not, and never starts two writable nodes.
- The standby continuously receives and replays WAL, is visible through `pg_stat_replication` and `pg_stat_wal_receiver`, uses the intended slot, and remains within explicit byte/time lag and disk-retention thresholds.
- A synthetic committed marker is visible on the standby before switchover; writes are rejected while the service is frozen; the old primary is network- and service-fenced before the standby is promoted.
- Promotion creates a new timeline, applications reconnect through the controlled endpoint, writes succeed only on the new primary, and the former primary is rewound or rebuilt as a standby without split brain.
- Operators understand that PostgreSQL provides replication and promotion primitives but does not detect failure, establish quorum, fence nodes, or automatically redirect clients; unsupervised automatic failover is explicitly outside this guide.
Architecture
How the parts fit together
Two hosts run the same PostgreSQL 18.4 minor release and compatible operating-system packages. The primary accepts application writes and one TLS replication connection from a dedicated role restricted in `pg_hba.conf` to the standby address. WAL is streamed by a walsender to the standby walreceiver and applied by recovery. A named physical slot protects WAL needed by that standby, while `max_slot_wal_keep_size`, filesystem alerts, and an independent WAL archive bound the risk that an offline standby fills `pg_wal`. Clients use a controlled DNS name, proxy, or service-discovery record rather than either host address. An external runbook and two-person decision coordinate write drain, catch-up, fencing, promotion, endpoint movement, verification, and rejoin. The old primary cannot return to service merely because its process starts; it must be fenced and reconciled with the new timeline.
- Validate versions, identities, storage, checksums, time, network path, TLS trust, backup health, and an independently tested recovery path before changing the primary.
- Configure WAL sender and slot capacity, create a least-privilege replication role, and allow only the standby TLS source in `pg_hba.conf`.
- Create the physical slot, take and verify a base backup, write standby connection settings without exposing the password, and start recovery.
- Observe sender, receiver, slot, replay, conflict, disk, and archive state under normal and interrupted conditions; rehearse rebuilding a disposable standby.
- For planned switchover, freeze new writes, drain sessions and background writers, capture a target LSN, wait until replay reaches it, then fence and stop the old primary.
- Promote one standby, verify timeline and read/write state, move the endpoint, reopen traffic gradually, and monitor correctness rather than only process uptime.
- Keep the former primary fenced while `pg_rewind --dry-run` or a fresh base backup prepares it to follow the new primary; only then restore redundancy.
Assumptions
- Both nodes run PostgreSQL 18.4, the current PostgreSQL 18 maintenance release verified for this guide on 2026-07-28, with the same architecture, data checksums policy, compatible extensions, locales, and tablespace layout.
- Ubuntu 24.04 LTS service names, paths, ownership, firewall, and package provenance are already known. Examples use `/var/lib/postgresql/18/main` but operators resolve the actual `data_directory` before every filesystem command.
- The standby has exclusive storage at least as large as the primary, equivalent durability, enough CPU and I/O to replay peak WAL, synchronized time, and a private low-latency path.
- An application owner can stop or reject writes from API servers, workers, schedulers, migrations, ETL, maintenance jobs, and direct database clients; revoking one login is not assumed to drain all writers.
- A separate system can fence the old primary at the service, network, load-balancer, and where necessary power or storage layer. DNS alone is not fencing.
- A continuous WAL archive and verified physical backup exist independently of the pair. Streaming replication is not a backup and reproduces accidental or malicious changes.
Key concepts
- Physical streaming replication
- The standby replays the primary cluster's WAL records at the storage level. It follows the whole cluster and requires compatible PostgreSQL binaries and physical layout.
- LSN
- A WAL location used to compare generation, send, write, flush, and replay progress. Byte differences are meaningful; wall-clock lag alone can be misleading during idle periods.
- Timeline
- A history branch created when recovery is promoted. The former primary remains on an older branch and cannot safely resume writing without rewind or rebuild.
- Physical replication slot
- A primary-side retention promise for one consumer. It prevents required WAL removal but can exhaust disk if the consumer disappears and retention is not bounded and monitored.
- Promotion
- Ending recovery so a standby becomes writable on a new timeline. Promotion is not failover coordination, client routing, fencing, or validation.
- Fencing
- A verifiable mechanism that prevents the old primary from serving writes before another node is promoted, eliminating split-brain authority.
- pg_rewind
- A destructive target-side utility that synchronizes a diverged cluster with the new source when WAL and `wal_log_hints` or data checksums satisfy its prerequisites. It is not a substitute for backup.
Before you copy
Values used in this guide
{{primaryHost}}Private DNS name of the current primary.
Example: pg-primary.internal.example{{standbyHost}}Private DNS name of the planned standby.
Example: pg-standby.internal.example{{standbyCidr}}Exact standby source address allowed to replicate.
Example: 10.24.8.12/32{{replicationUser}}Dedicated LOGIN REPLICATION role.
Example: physical_replica{{replicationSlot}}Named physical slot owned by this standby.
Example: standby_a_physical{{dataDirectory}}Resolved PostgreSQL cluster data directory.
Example: /var/lib/postgresql/18/main{{writerEndpoint}}Controlled application endpoint for the current primary.
Example: postgres-writer.internal.example{{targetLsn}}Final flushed WAL location recorded after all writers are drained.
Example: 3A/C04A11D8Security and production boundaries
- Use TLS with certificate verification for replication and client connections. `sslmode=require` encrypts but does not authenticate the server; production examples should use `verify-full` and a trusted root.
- Store the replication password in a mode-0600 `.pgpass`, service credential, or approved secret mechanism owned by `postgres`; never embed it in Git, `primary_conninfo` shown by logs, shell history, or process arguments.
- The replication role needs LOGIN and REPLICATION, not SUPERUSER, CREATEDB, CREATEROLE, BYPASSRLS, application ownership, or shell access.
- Hot standbys contain the complete physical dataset and must receive the same disk encryption, host hardening, backup, access, audit, and disposal controls as the primary.
- Fencing is a data-integrity control. A network partition can make both nodes look healthy; never promote until authority to write is singular and evidenced.
- `pg_rewind` overwrites the target data directory. Preserve evidence and a backup, stop the target, prove source/target identity, and prefer a fresh base backup whenever prerequisites are uncertain.
Stop before continuing if
- Stop if versions, checksums, extension binaries, tablespaces, archive health, backup validity, or the exact primary/standby identities are unknown.
- Stop if slot-retained WAL approaches its byte or filesystem threshold, the archive is failing, or the standby cannot sustain peak replay.
- Stop a switchover if any writer remains, replay has not reached the recorded target LSN, or the old primary cannot be fenced at every client path.
- Never promote two candidates, never rely on DNS TTL as fencing, and never restart the former primary in normal mode after another node has been promoted.
- Stop `pg_rewind` if target/source identity, clean shutdown, WAL ancestry, required WAL files, checksums or `wal_log_hints`, tablespace mappings, or a recoverable backup is missing.
verification
Prove node identity, version, cluster identity, and recovery state
On both hosts, capture exact PostgreSQL package and server version, data directory, system identifier, checksum state, timeline-visible control data, recovery state, storage capacity, time synchronization, and service owner. Label the current primary and intended standby from evidence rather than hostnames alone.
Why this step matters
Failover procedures become destructive when a hostname, mount, or package assumption is wrong. PostgreSQL minor versions can interoperate for rolling maintenance, but the upstream recommendation is to keep primary and standby at the same release and update standbys first. System identifiers prove that clusters share ancestry; recovery state proves which node is currently read-only. Checksums and `wal_log_hints` determine whether later `pg_rewind` is possible. Control data must be read with the server state understood, because a stale filesystem path may belong to an abandoned cluster. This preflight creates an immutable evidence record before any role, slot, service, or data directory is changed.
What to understand
Run the SQL on each node through a local Unix socket with `-X` so user psql startup files cannot change behavior.
Resolve `data_directory` from the live server and compare it with systemd/Ubuntu cluster metadata before using filesystem tools.
Record `SHOW server_version_num`, extension versions, locale, encoding, tablespaces, block size, WAL segment size, checksums, and `wal_log_hints`.
Verify UTC time and NTP health because lag, incident, backup, and switchover evidence depends on trustworthy timestamps.
Confirm the current primary has `pg_is_in_recovery() = false` and the intended standby is either empty/not initialized or later reports true.
System changes
- None; this step creates a protected preflight record only.
Syntax explained
psql -XAtq- Ignores user startup commands and emits compact unaligned tuples suitable for unambiguous evidence.
pg_controldata- Reads cluster control metadata; use it only against the resolved data directory with appropriate service-state awareness.
Values stay on this page and are never sent or saved.
sudo -u postgres psql -XAtqc "SELECT version(), current_setting('data_directory'), pg_is_in_recovery();" && sudo -u postgres pg_controldata {{dataDirectory}} | grep -E 'Database system identifier|Database cluster state|Data page checksum version|Latest checkpoint.s TimeLineID' && dpkg-query -W 'postgresql-18*'PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu /var/lib/postgresql/18/main f Database system identifier: 7589916241639021840 Database cluster state: in production Data page checksum version: 1 Latest checkpoint's TimeLineID: 1
Checkpoint: The pair and its prerequisites are unambiguous
sudo -u postgres psql -XAtqc "SELECT current_setting('server_version_num'), pg_is_in_recovery();"Continue whenBoth nodes use PostgreSQL 18.4 and compatible extensions; exactly one current node is writable; identity, data paths, checksums, time, capacity, archive, and backups are recorded.
Stop whenAny node, data directory, system identifier, version, checksum policy, extension, storage, time source, archive, or backup status is unknown or inconsistent.
If this step fails
SQL version and package version differ.
Likely causeA different cluster is listening, packages were upgraded without restart, or PATH resolves another installation.
Inspect socket/port, `pg_postmaster_start_time()`, process executable, and package inventory.
ResolutionIdentify the live cluster and complete an approved minor-version restart before replication work.
The supposed standby reports `pg_is_in_recovery() = false`.
Likely causeIt was promoted, initialized independently, or the operator connected to the wrong endpoint.
Compare system identifiers, timeline/control data, endpoints, and service units.
ResolutionQuarantine the node until its history and authority are established; never overwrite it based only on its label.
Security notes
- Preflight output contains internal paths, versions, topology, and identifiers; store it with incident/change evidence, not in public logs.
Alternatives
- Use a signed inventory collector that runs the same read-only queries and records node identity.
Stop conditions
- Any node, data directory, system identifier, version, checksum policy, extension, storage, time source, archive, or backup status is unknown or inconsistent.
decision
Size WAL retention, replay capacity, and recovery objectives
Measure WAL generation across representative peak and maintenance windows, standby replay capacity, network throughput, archive latency, and free space. Select explicit RPO, RTO, `max_slot_wal_keep_size`, `wal_keep_size`, alert thresholds, and rebuild thresholds instead of copying defaults.
Why this step matters
A replication slot is an availability mechanism with a storage failure mode: it can retain every required WAL byte while its consumer is absent. A fixed retention copied from another cluster says nothing about this workload's peak WAL, outage tolerance, archive reliability, or disk. Time lag can remain null or misleading when no transactions occur, so capacity decisions use LSN bytes and rates. The chosen slot ceiling must leave emergency headroom for checkpoints, bursts, maintenance, and operator response. If the standby can never replay the measured peak, it is not a promotion candidate regardless of its usual one-second lag.
What to understand
Take repeated `pg_stat_wal` and LSN samples over peak writes, bulk loads, index builds, vacuum, and deployment windows.
Measure archive completion latency and prove the archive can supply a segment after the primary is unavailable.
Set warning and critical thresholds below filesystem exhaustion and below `max_slot_wal_keep_size`; document who may drop/rebuild a slot.
Test replay throughput by generating a controlled WAL workload and measuring receive-versus-replay byte deltas.
Define acceptable asynchronous data loss, planned switchover zero-lag criteria, and maximum time to rebuild the standby.
System changes
- None; capacity and policy values are calculated and reviewed.
Syntax explained
max_slot_wal_keep_size- Caps WAL retained by slots at checkpoint evaluation; `-1` is unbounded and can exhaust disk.
wal_keep_size- Keeps a minimum amount of older WAL for standbys but is not per-consumer and does not replace archive or slot monitoring.
Values stay on this page and are never sent or saved.
sudo -u postgres psql -X -c "SELECT now(), pg_current_wal_lsn(), wal_bytes, wal_records FROM pg_stat_wal;"; df -h {{dataDirectory}}/pg_walnow | pg_current_wal_lsn | wal_bytes | wal_records -------------------------------+--------------------+--------------+------------ 2026-07-28 10:00:00+00 | 3A/90000000 | 847166095360 | 1824432291 Filesystem Size Used Avail Use% Mounted on /dev/nvme1n1 400G 112G 288G 29% /var/lib/postgresql
Checkpoint: Retention has byte, time, disk, and owner boundaries
sudo -u postgres psql -XAtqc "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots WHERE slot_name='{{replicationSlot}}';"Continue whenPeak WAL rate, replay rate, archive latency, free space, RPO/RTO, slot ceiling, alerts, operator response time, and rebuild decision are documented and mutually feasible.
Stop whenThe slot ceiling is unbounded without dedicated capacity controls, the standby cannot replay peak WAL, the archive is untested, or no owner can act before disk exhaustion.
If this step fails
WAL rate samples vary by orders of magnitude.
Likely causeSampling omitted batch jobs, checkpoints, maintenance, or release activity.
Correlate samples with workload schedules, `pg_stat_wal`, logs, and known bulk jobs.
ResolutionCapture representative peak windows and size for observed bursts plus response headroom.
Required retention exceeds available safe disk.
Likely causeThe outage/rebuild target and current storage budget are incompatible.
Compare archive retrieval and fresh base-backup duration with the business RTO.
ResolutionIncrease isolated WAL capacity or shorten rebuild/response objectives; do not silently accept disk exhaustion.
Security notes
- Capacity dashboards should not expose internal topology or identifiers publicly.
Alternatives
- Use archive-backed rebuild as the operational choice when slot retention cannot safely cover long outages.
Stop conditions
- The slot ceiling is unbounded without dedicated capacity controls, the standby cannot replay peak WAL, the archive is untested, or no owner can act before disk exhaustion.
config
Configure WAL senders, slots, archive, and bounded retention
Set primary replication parameters through the managed PostgreSQL configuration include, preserving the existing working archive command. Use enough senders and slots for backups and standbys plus operational headroom, but keep values owned and monitored.
Why this step matters
Physical streaming requires `wal_level=replica` and sender capacity. Slots require `max_replication_slots`. These startup settings need a controlled restart, whereas careless editing during an incident can remove backup or logical replication capacity. `wal_log_hints=on` enables future `pg_rewind` when checksums are not enabled, but it increases WAL for hint-bit changes and applies only after restart. The archive command is intentionally not invented here: a production archive must be atomic, idempotent for identical content, reject conflicting duplicates, preserve permissions and encryption, and return nonzero on failure. Replacing it with `/bin/true` reports success while destroying the recovery chain.
What to understand
Inventory existing physical/logical slots, WAL senders, base backups, archivers, and extension requirements before choosing counts.
Place owned settings in a dedicated include rather than editing package defaults without provenance.
Run `pg_file_settings` and `pg_settings.pending_restart` checks before restart; schedule restart with application and backup owners.
After restart, prove archive and replication settings from the live server, not merely the text file.
Retain the prior configuration and exact restart rollback command, but never roll back by disabling archive reporting.
System changes
- Changes startup replication settings and requires a PostgreSQL restart.
- May increase WAL volume because of `wal_log_hints` and additional replication activity.
Syntax explained
wal_level=replica- Writes enough information for physical replication and archiving.
max_wal_senders- Maximum concurrent WAL sender processes; include standbys and streaming base backups plus reviewed headroom.
wal_log_hints=on- Logs hint-bit changes so `pg_rewind` can work without data checksums; it is not itself a failover control.
/etc/postgresql/18/main/conf.d/20-replication.confwal_level = replica
max_wal_senders = 6
max_replication_slots = 4
max_slot_wal_keep_size = '200GB'
wal_keep_size = '2GB'
wal_log_hints = on
# Preserve the independently tested archive_mode and archive_command.
# Do not replace a working archive_command with /bin/true.sudo -u postgres psql -XAtqc "SELECT name,setting,unit,pending_restart FROM pg_settings WHERE name IN ('wal_level','max_wal_senders','max_replication_slots','max_slot_wal_keep_size','wal_keep_size','archive_mode','archive_command','wal_log_hints') ORDER BY name;"archive_command|/usr/local/sbin/archive-wal %p %f||f archive_mode|on||f max_replication_slots|4||f max_slot_wal_keep_size|204800|MB|f max_wal_senders|6||f wal_keep_size|2048|MB|f wal_level|replica||f wal_log_hints|on||f
Checkpoint: The live server exposes the reviewed replication baseline
sudo -u postgres psql -X -c "SELECT name,setting,pending_restart FROM pg_settings WHERE name IN ('wal_level','max_wal_senders','max_replication_slots','max_slot_wal_keep_size','wal_log_hints','archive_mode');"Continue whenValues match the approved capacity model, no relevant pending restart remains, the archiver succeeds, backups still run, and prior configuration is retained.
Stop whenAny existing slot/sender/backup is displaced, archive health degrades, a syntax error exists, `wal_log_hints` expectation is false, or restart ownership is missing.
If this step fails
PostgreSQL will not start after the configuration change.
Likely causeA syntax, unit, include ordering, or unsupported-setting error exists.
Run `postgres -C`/`pg_file_settings` from a safe maintenance context and inspect journal logs.
ResolutionRestore the exact previous include, validate, and restart; do not initialize or delete the data directory.
Archive failures start after restart.
Likely causeThe archive command, credentials, destination, permissions, or network path is unhealthy.
Inspect `pg_stat_archiver`, PostgreSQL logs, destination capacity, and a controlled segment switch.
ResolutionRepair the independent archive and verify a segment end to end before continuing; never mask failure with a successful no-op.
Security notes
- Archive scripts and destinations handle complete WAL content; protect credentials, commands, logs, and retained files as database data.
Alternatives
- Use `archive_library` with a reviewed module where its operational and security model is preferable to a shell command.
Stop conditions
- Any existing slot/sender/backup is displaced, archive health degrades, a syntax error exists, `wal_log_hints` expectation is false, or restart ownership is missing.
config
Create a dedicated replication role and narrow TLS HBA rule
Create a non-superuser replication login with a generated secret delivered outside command history. Add one `hostssl ... replication` HBA line for the exact standby address and strong SCRAM authentication, then validate parsed rules and reload.
Why this step matters
The replication protocol can read the cluster's physical changes and should have a dedicated identity. HBA is ordered, so a correct narrow rule placed after a broader reject or trust rule may not be effective. `hostssl` ensures the connection uses TLS, while SCRAM verifies the role secret; the standby must additionally verify the server certificate and hostname. Passing a password directly in SQL, process arguments, shell history, CI output, or the tutorial form is avoidable. `createuser --pwprompt` accepts it interactively without embedding it in the command; automation should use an approved secret-aware bootstrap, then rotate deliberately and test that the old credential is rejected after cutover.
What to understand
Set `password_encryption=scram-sha-256` before generating the role secret and confirm server TLS certificate chain and SAN.
Use a random high-entropy password or certificate-based design supported by the organization's identity lifecycle.
Place the exact source rule before any conflicting broader rules and inspect `pg_hba_file_rules.error` before reload.
Grant no database object privileges; REPLICATION is sufficient for the physical protocol and `pg_basebackup` requirements described upstream.
Record owner, expiry/rotation, secret store path, and emergency revocation procedure without recording the secret.
System changes
- Creates a cluster-wide login capable of physical replication.
- Adds and reloads a source-restricted authentication rule.
Syntax explained
hostssl replication- Matches TLS replication-protocol connections rather than ordinary database connections.
scram-sha-256- Uses SCRAM password authentication; server identity still requires client certificate verification.
/etc/postgresql/18/main/pg_hba.confValues stay on this page and are never sent or saved.
hostssl replication {{replicationUser}} {{standbyCidr}} scram-sha-256Values stay on this page and are never sent or saved.
sudo -u postgres createuser --pwprompt --replication --no-superuser --no-createdb --no-createrole --no-inherit {{replicationUser}} && sudo -u postgres psql -X -c "SELECT line_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules WHERE user_name @> ARRAY['{{replicationUser}}'];"Enter password for new role:
Enter it again:
line_number | type | database | user_name | address | auth_method | error
-------------+---------+---------------+-----------------------+--------------+-----------------+-------
96 | hostssl | {replication} | {physical_replica} | 10.24.8.12 | scram-sha-256 |
(1 row)Checkpoint: Replication has one narrow authenticated path
sudo -u postgres psql -XAtqc "SELECT rolname,rolsuper,rolreplication,rolcreatedb,rolcreaterole FROM pg_roles WHERE rolname='{{replicationUser}}'; SELECT count(*) FILTER (WHERE error IS NOT NULL) FROM pg_hba_file_rules;"Continue whenThe role is LOGIN+REPLICATION only, the exact hostssl rule parses without error, TLS verify-full succeeds from standby, and no broad trust rule can match first.
Stop whenThe password appears in logs/history, the role is superuser or object owner, the HBA address is broad, TLS name verification fails, or parsed rules contain errors.
If this step fails
Replication reports no pg_hba.conf entry.
Likely causeThe observed source address, address family, SSL state, database token, rule order, or reloaded file differs.
Inspect server log details, `SHOW hba_file`, and `pg_hba_file_rules` without broadening access.
ResolutionAdd or correct only the exact observed approved source and reload; do not add `0.0.0.0/0`.
Certificate verification fails.
Likely causeThe standby does not trust the CA or connects with a hostname absent from certificate SAN.
Inspect the presented chain and SAN using approved TLS tools and compare DNS.
ResolutionIssue/correct the certificate or endpoint name; do not downgrade from `verify-full`.
Security notes
- A physical replica contains all cluster data even if SQL grants are narrow; replication authorization is a high-impact control.
Alternatives
- Use client certificates with a mapped dedicated replication identity when certificate lifecycle and revocation are mature.
Stop conditions
- The password appears in logs/history, the role is superuser or object owner, the HBA address is broad, TLS name verification fails, or parsed rules contain errors.
command
Create and assign one named physical replication slot
Create a physical slot whose name encodes its owner, confirm it is inactive before bootstrap, and register it in monitoring. Never reuse a slot between concurrent standbys or drop a slot merely because it is inactive.
Why this step matters
A physical slot makes the primary retain WAL needed by a specific standby across disconnects and the base-backup-to-start gap. The `immediately_reserve` argument establishes a restart LSN immediately, which begins retention before the consumer connects. This improves bootstrap reliability and also starts the disk-risk clock. Slot inactivity is not proof that it is abandoned: the owner may be under maintenance or network isolation. Every slot needs an inventory record, consumer, alert, ceiling, and two-person drop/rebuild procedure.
What to understand
Choose a lowercase stable name and confirm no standby, backup tool, or automation already uses it.
Record creation LSN/time, owning host, ticket, `max_slot_wal_keep_size`, filesystem threshold, and escalation owner.
Monitor `restart_lsn`, `wal_status`, `safe_wal_size`, `active`, and `inactive_since` together with disk free bytes.
Create immediately before base backup and connect the consumer promptly.
If bootstrap fails, either resume within safe capacity or explicitly abandon the slot and backup after proving identity.
System changes
- Creates a persistent slot and starts retaining WAL for its consumer.
Syntax explained
immediately_reserve=true- Reserves WAL from slot creation instead of waiting for first streaming use.
safe_wal_size- Approximate additional WAL that can be written before the slot risks becoming lost under the configured ceiling.
Values stay on this page and are never sent or saved.
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -c "SELECT * FROM pg_create_physical_replication_slot('{{replicationSlot}}', true);" -c "SELECT slot_name,slot_type,active,restart_lsn,wal_status,safe_wal_size,inactive_since FROM pg_replication_slots WHERE slot_name='{{replicationSlot}}';" slot_name | lsn
----------------------+------------
standby_a_physical | 3A/90000120
(1 row)
slot_name | slot_type | active | restart_lsn | wal_status | safe_wal_size | inactive_since
----------------------+-----------+--------+-------------+------------+---------------+----------------
standby_a_physical | physical | f | 3A/90000120 | reserved | 207451394048 | 2026-07-28 10:22:16+00Checkpoint: The slot is owned, bounded, and ready for one consumer
sudo -u postgres psql -X -c "SELECT slot_name,slot_type,active,restart_lsn,wal_status,safe_wal_size FROM pg_replication_slots WHERE slot_name='{{replicationSlot}}';"Continue whenExactly one physical slot exists with the approved name, reserved WAL, documented owner, finite retention ceiling, active alerts, and imminent consumer bootstrap.
Stop whenThe name collides, multiple consumers are planned, retained WAL grows beyond threshold, owner is unknown, or no archive/rebuild path exists.
If this step fails
Slot creation says it already exists.
Likely causeA previous bootstrap or another consumer owns the name.
Inspect slot state, active PID, replication connections, runbook inventory, and owning host.
ResolutionReuse only after proving it is the same intended consumer and LSN-safe; otherwise select a new owned name.
The slot becomes `lost` or safe WAL reaches zero.
Likely causeThe configured retention ceiling was exceeded before the consumer retained required WAL.
Preserve state, inspect archive coverage, and compare standby/base-backup start LSN.
ResolutionRebuild from a new base backup and recreate the slot; do not pretend streaming can bridge missing WAL.
Security notes
- Restrict slot creation/drop to controlled database administration; dropping can silently invalidate recovery readiness.
Alternatives
- Use archive plus `wal_keep_size` without a permanent slot only when its retention and bootstrap guarantees are explicitly acceptable.
Stop conditions
- The name collides, multiple consumers are planned, retained WAL grows beyond threshold, owner is unknown, or no archive/rebuild path exists.
command
Bootstrap the standby with a verified base backup
Stop the empty standby cluster, prove the target directory contains no data to preserve, then run `pg_basebackup` from the standby with WAL streaming, the named slot, recovery configuration, progress, manifest, checksum verification, and a rate chosen from the capacity plan. Protect the connection secret with `.pgpass` or a service credential.
Why this step matters
A standby must begin from a physically consistent copy of the same cluster. `pg_basebackup` coordinates a live backup and, with streamed WAL, includes the records needed to make it consistent. The permanent slot protects the transition to streaming. `-R` writes `standby.signal` and connection settings, but that convenience can also write sensitive connection material; keep passwords in `.pgpass` or another protected credential source. A plain backup preserves tablespace paths, so operators must map and provision every tablespace deliberately. A completed command is not enough: the manifest, file ownership, checksums, system identifier, generated recovery settings, and slot association are separate acceptance gates.
What to understand
Confirm the target host contains no cluster requiring preservation and take a filesystem/volume snapshot only if policy requires recovery from an operator mistake.
Use `--checkpoint=spread` by default to reduce primary I/O shock; select `fast` only with measured capacity and an approved time constraint.
Keep the default manifest and checksum verification. After completion run `pg_verifybackup` against the untouched backup before first start.
Inspect `postgresql.auto.conf` for host, user, TLS mode, CA path, and slot without printing or committing secret content.
Verify owner/mode for the data directory, `.pgpass`, TLS root, key material, tablespaces, and external configuration includes.
System changes
- Replaces an explicitly empty standby data directory with a full physical cluster copy.
- Creates `standby.signal` and appends recovery connection settings.
Syntax explained
--wal-method=stream- Streams required WAL in parallel with the base copy; consumes an additional WAL sender.
--slot and --write-recovery-conf- Uses the owned retention slot and records it for subsequent standby streaming.
--checkpoint=spread- Spreads the backup-start checkpoint load, trading startup time for lower I/O burst.
Values stay on this page and are never sent or saved.
sudo systemctl stop postgresql@18-main && sudo -u postgres test -z "$(find {{dataDirectory}} -mindepth 1 -maxdepth 1 -print -quit)" && sudo -u postgres pg_basebackup --dbname="host={{primaryHost}} port=5432 user={{replicationUser}} sslmode=verify-full sslrootcert=/etc/postgresql/tls/root.crt" --pgdata={{dataDirectory}} --format=plain --wal-method=stream --slot={{replicationSlot}} --write-recovery-conf --progress --checkpoint=spread --label=standby-a-20260728245760/245760 kB (100%), 1/1 tablespace NOTICE: base backup completed NOTICE: backup manifest written standby.signal backup_manifest postgresql.auto.conf
Checkpoint: The base backup is complete, authentic, and recoverable
sudo -u postgres pg_verifybackup {{dataDirectory}} && sudo -u postgres pg_controldata {{dataDirectory}} | grep 'Database system identifier' && sudo -u postgres test -f {{dataDirectory}}/standby.signalContinue whenManifest verification succeeds, system identifier matches primary, standby.signal exists, slot/TLS settings are correct, files are owned by postgres, and no password is present in evidence.
Stop whenTarget identity is uncertain, directory was not empty, backup or manifest verification fails, tablespace mapping is wrong, required WAL is missing, or credentials are exposed.
If this step fails
`pg_basebackup` stalls near the beginning.
Likely causeThe default spread checkpoint, primary I/O pressure, authentication, sender capacity, or network path is delaying progress.
Inspect `pg_stat_progress_basebackup`, walsenders, logs, I/O, and network without killing a healthy checkpoint.
ResolutionResolve capacity or connection issues and restart into a clean target; use fast checkpoint only after impact approval.
`pg_verifybackup` reports missing or changed files.
Likely causeThe copy is incomplete, corrupted, modified after backup, or external tablespaces were mishandled.
Preserve the report and compare manifest entries, filesystem errors, and tablespace mappings.
ResolutionDiscard the candidate safely and take a new verified base backup; never start it as a standby.
Security notes
- A base backup is the full database cluster. Encrypt transport and storage, restrict the target, and sanitize progress/error logs.
Alternatives
- Restore a separately verified physical backup plus WAL when backup tooling provides stronger encryption, immutability, and inventory.
Stop conditions
- Target identity is uncertain, directory was not empty, backup or manifest verification fails, tablespace mapping is wrong, required WAL is missing, or credentials are exposed.
config
Harden standby connection, recovery, and read-only behavior
Review generated settings, set `primary_conninfo` with certificate verification and keep the secret outside the file, bind the named slot, follow the latest timeline, enable hot standby, and define conflict/retry timeouts according to reporting and RPO policy.
Why this step matters
The generated recovery file is only a starting point. `application_name` identifies the standby in sender statistics; the slot name couples it to retention; `recovery_target_timeline=latest` allows it to follow a promoted upstream timeline. TLS `verify-full` authenticates both certificate chain and host name. A password in `primary_conninfo` can become visible in configuration copies, diagnostics, process state, or backups, so `.pgpass` or a protected credential facility supplies it. Hot-standby conflict delays are availability choices: waiting protects long read queries but increases replay lag; canceling preserves recovery progress but interrupts reports. Neither setting should be tuned blindly.
What to understand
Inspect all configuration sources with `pg_file_settings`; Ubuntu places main configuration outside the data directory by default.
Use an internal DNS name present in certificate SAN and ensure resolver behavior remains available during primary failure.
Set `.pgpass` to the exact host, port, replication database token, and role with mode 0600 and no backups to public storage.
Keep `recovery_target_timeline=latest` for HA standby behavior; targeted PITR uses a separate isolated recovery design.
Choose conflict delays from measured reporting workload and maximum permitted replay lag, then monitor cancellations and bloat.
System changes
- Defines how the standby authenticates, selects its slot, follows timelines, and handles replay conflicts.
Syntax explained
application_name- Stable sender-side identity displayed in `pg_stat_replication` and usable by synchronous policies if later approved.
recovery_target_timeline=latest- Follows the newest timeline found through streaming/archive instead of remaining on an obsolete branch.
max_standby_streaming_delay- Bounds delay caused by conflicting standby queries; it is not a per-query execution timeout.
/etc/postgresql/18/main/conf.d/30-standby.confhot_standby = on
primary_slot_name = 'standby_a_physical'
recovery_target_timeline = 'latest'
wal_receiver_timeout = '30s'
wal_retrieve_retry_interval = '5s'
max_standby_streaming_delay = '30s'
# primary_conninfo uses sslmode=verify-full and no inline password.Values stay on this page and are never sent or saved.
sudo -u postgres grep -E '^(primary_conninfo|primary_slot_name|recovery_target_timeline)' {{dataDirectory}}/postgresql.auto.conf | sed -E 's/(password=)[^ ]+/\1REDACTED/' && sudo -u postgres test -r ~postgres/.pgpass && sudo -u postgres stat -c '%a %U %n' ~postgres/.pgpassprimary_conninfo = 'host=pg-primary.internal.example port=5432 user=physical_replica sslmode=verify-full sslrootcert=/etc/postgresql/tls/root.crt application_name=standby-a' primary_slot_name = 'standby_a_physical' recovery_target_timeline = 'latest' 600 postgres /var/lib/postgresql/.pgpass
Checkpoint: Recovery configuration is exact and secret-safe
sudo -u postgres test -f {{dataDirectory}}/standby.signal && sudo -u postgres grep -q "primary_slot_name = '{{replicationSlot}}'" {{dataDirectory}}/postgresql.auto.confContinue whenStandby signal, exact primary endpoint, application name, slot, latest timeline, verify-full TLS, protected password source, and conflict policy all match review.
Stop whenA password is embedded or logged, TLS is weaker than verify-full, a wrong slot/host is selected, configuration parsing fails, or conflict policy permits uncontrolled lag.
If this step fails
The standby tries the wrong primary after restart.
Likely causeGenerated `postgresql.auto.conf` overrides the reviewed include or contains stale connection data.
Use `pg_file_settings` and `ALTER SYSTEM` provenance to inspect effective ordering offline/on a safe start.
ResolutionRemove only the verified stale assignment and make one reviewed source authoritative.
The `.pgpass` entry is ignored.
Likely causeIts mode, ownership, host, port, database token, username, or wildcard order is wrong.
Check mode/owner and compare fields without displaying the password.
ResolutionCorrect the exact entry and permissions; do not place the secret on the command line.
Security notes
- Configuration backups may contain connection endpoints and CA paths even without passwords; protect them as infrastructure metadata.
Alternatives
- Use certificate authentication for the replication role when certificate issuance, rotation, and revocation are automated and tested.
Stop conditions
- A password is embedded or logged, TLS is weaker than verify-full, a wrong slot/host is selected, configuration parsing fails, or conflict policy permits uncontrolled lag.
verification
Start the standby and prove receive, flush, and replay
Start the standby, require recovery mode, inspect the WAL receiver locally and the sender/slot on primary, then commit a synthetic marker on primary and observe it on standby. Measure LSN byte differences rather than relying on a single lag interval.
Why this step matters
A green systemd unit proves only that a process runs. Recovery mode, receiver state, sender state, slot activity, and application-visible replay prove the intended data path. Sender byte positions distinguish transport, write, flush, and replay delays. `replay_lag` may be null or not represent a current idle delay, so a committed marker with a recorded LSN supplies a deterministic end-to-end test. Use a dedicated non-sensitive table/schema and remove it through a reviewed migration later; never test by changing production business rows.
What to understand
On standby require `pg_is_in_recovery()=true`, one receiver with `status=streaming`, correct sender host and slot.
On primary require one `pg_stat_replication` row with expected client address, application name, state, and slot active.
Create a synthetic marker in a controlled operations schema on primary, commit, capture `pg_current_wal_flush_lsn()`, and poll standby replay.
Query the marker through a read-only standby connection and confirm ordinary writes are rejected.
Record sender, receiver, replay, timeline, archive, conflict, and disk evidence together.
System changes
- Starts the standby and activates the permanent replication slot.
- Creates one controlled synthetic verification record on the primary.
Syntax explained
pg_last_wal_receive_lsn- Latest WAL location received and synchronized locally by the standby.
pg_last_wal_replay_lsn- Latest WAL location whose changes have been replayed and are visible.
sudo systemctl start postgresql@18-main && sudo -u postgres psql -X -c "SELECT pg_is_in_recovery(),status,sender_host,slot_name,written_lsn,flushed_lsn,latest_end_lsn FROM pg_stat_wal_receiver;"pg_is_in_recovery | status | sender_host | slot_name | written_lsn | flushed_lsn | latest_end_lsn -------------------+-----------+--------------------------------+----------------------+-------------+-------------+--------------- t | streaming | pg-primary.internal.example | standby_a_physical | 3A/A00219F0 | 3A/A00219F0 | 3A/A00219F0 (1 row)
Checkpoint: A committed marker reaches a read-only standby
sudo -u postgres psql -XAtqc "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"Continue whenRecovery true, receiver streaming from correct primary with correct slot, marker visible, write test rejected, byte lag within threshold, and archive/slot/disk healthy.
Stop whenStandby is writable, sender identity is wrong, marker is absent, any write succeeds, receiver is not streaming, lag breaches threshold, or logs contain recovery errors.
If this step fails
Receiver alternates between connecting and stopped.
Likely causeTLS/authentication, timeout, DNS, firewall, primary restart, or WAL availability is unstable.
Inspect both PostgreSQL logs, receiver status, endpoint resolution, certificate dates, and exact missing WAL.
ResolutionRepair the exact transport/auth/archive issue and require a stable observation window before accepting readiness.
Receive LSN advances but replay LSN does not.
Likely causeReplay is paused, blocked by recovery conflict, or resource constrained.
Check `pg_is_wal_replay_paused()`, conflicts, long queries, I/O, CPU, and logs.
ResolutionResume only if the pause was approved or resolve the bottleneck; do not promote until replay catches up.
Security notes
- Use a non-sensitive marker and redact addresses/LSNs from public reports; do not weaken standby read-only state for testing.
Alternatives
- Use a signed automated probe that commits and observes a rotating synthetic marker while retaining bounded evidence.
Stop conditions
- Standby is writable, sender identity is wrong, marker is absent, any write succeeds, receiver is not streaming, lag breaches threshold, or logs contain recovery errors.
instruction
Monitor sender, receiver, slots, archive, conflicts, and disk as one SLO
Create alerts and dashboards for connection state, generation-to-send/write/flush/replay bytes, replay timestamp age with idle-awareness, slot safe WAL size, `pg_wal` free space, archive failures, receiver restart, recovery conflicts, and last successful rebuild/failover rehearsal.
Why this step matters
Replication health is a chain, not one boolean. A sender can stream while replay falls behind; a receiver can be current while archive silently fails; a disconnected slot can retain enough WAL to fill disk; an idle primary can make replay timestamp age look old despite zero byte gap. Alerts therefore combine state, bytes, rates, timestamps, filesystem capacity, archive outcomes, conflicts, and recovery-test freshness. Resettable cumulative counters require baselines and reset awareness. A dashboard without an owner and runbook merely documents the outage.
What to understand
Collect primary `pg_stat_replication`, `pg_replication_slots`, `pg_stat_archiver`, `pg_stat_wal`, and filesystem metrics.
Collect standby receiver, receive/replay LSN, replay pause, conflict counters, logs, filesystem and service state.
Alert on slot `safe_wal_size`, `wal_status`, inactivity, and disk remaining with enough lead time to rebuild or intervene.
Separate planned maintenance and idle workload from actual lag by evaluating byte deltas and recent WAL generation.
Track the date/result/duration of the last base-backup verification, switchover, rewind or rebuild rehearsal.
System changes
- Adds read-only monitoring queries, alerts, retention, and operational ownership.
Syntax explained
sent/write/flush/replay_lsn- Separates upstream send, standby OS write, durable flush, and database replay stages.
pg_stat_archiver.failed_count- Cumulative failures since statistics reset; interpret with timestamps and reset time.
sudo -u postgres psql -X -c "SELECT application_name,state,sync_state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn)) AS send_gap,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)) AS replay_gap FROM pg_stat_replication; SELECT slot_name,active,wal_status,pg_size_pretty(safe_wal_size) AS safe_wal,inactive_since FROM pg_replication_slots; SELECT archived_count,failed_count,last_archived_time,last_failed_time FROM pg_stat_archiver;" application_name | state | sync_state | send_gap | replay_gap
------------------+-----------+------------+----------+-----------
standby-a | streaming | async | 0 bytes | 16 kB
slot_name | active | wal_status | safe_wal | inactive_since
standby_a_physical| t | reserved | 193 GB |
archived_count | failed_count | last_archived_time | last_failed_time
48312 | 0 | 2026-07-28 10:41:07+00 |Checkpoint: Every replication failure mode has an actionable alert
sudo -u postgres psql -XAtqc "SELECT count(*) FROM pg_stat_replication WHERE application_name='standby-a' AND state='streaming'; SELECT active,wal_status,safe_wal_size FROM pg_replication_slots WHERE slot_name='{{replicationSlot}}';"Continue whenDashboards show all chain stages, tested warning/critical alerts reach an owner, and each alert links to a safe check and rebuild/fence decision.
Stop whenOnly process-up or time-lag is monitored, slot/archive/disk has no alert, counters lack reset context, or no one can act within capacity headroom.
If this step fails
Replay delay alert fires on an idle cluster with zero byte gap.
Likely causeThe alert uses only `now()-pg_last_xact_replay_timestamp()`.
Compare current/generated and replay LSN plus recent WAL generation.
ResolutionGate time-age alerts with WAL activity and use byte/rate signals for current lag.
Slot disk alert arrives too late to rebuild.
Likely causeThreshold ignores WAL rate, checkpoint timing, and operational response duration.
Calculate time-to-exhaustion from recent peak rate and safe bytes.
ResolutionRaise earlier predictive warnings and expand safe capacity or reduce recovery time.
Security notes
- Restrict monitoring role/functions to required statistics and protect topology-bearing metrics.
Alternatives
- Use PostgreSQL-aware exporters or managed monitoring, validating that derived lag and slot semantics match PostgreSQL 18.
Stop conditions
- Only process-up or time-lag is monitored, slot/archive/disk has no alert, counters lack reset context, or no one can act within capacity headroom.
verification
Rehearse the complete switchover with disposable traffic
Before production, rehearse writer freeze, drain, target LSN capture, catch-up, fencing, promotion, endpoint movement, negative old-primary tests, and rejoin in an isolated copy with realistic WAL volume. Measure every phase and require two operators to follow the exact checklist.
Why this step matters
A production failover plan that has never been executed is a hypothesis. A rehearsal finds hidden writers, cached endpoints, permissions, service auto-restart, stale certificates, missing WAL, rewind prerequisites, and validation gaps without risking the live system. Realistic WAL and connection pools matter because a trivial empty cluster will not exercise catch-up or drain behavior. The rehearsal must include negative tests against the former primary and full redundancy restoration; stopping after promotion omits the most dangerous split-brain and recovery stages.
What to understand
Clone topology and configuration from controlled artifacts without copying production secrets into an unsafe lab.
Generate representative concurrent reads, writes, long transactions, workers, and WAL volume.
Have one operator execute and another independently confirm writer drain, LSN equality, fence evidence, and promotion target.
Measure RTO from write closure through validated reopening and record any committed-marker loss against the chosen RPO.
Deliberately fail one gate—remaining writer, lagging replay, failed fence—and prove the runbook stops rather than promoting.
System changes
- Promotes and rebuilds only an isolated rehearsal pair.
Syntax explained
require-fence-evidence- Makes service/network/storage isolation a machine- and human-checked prerequisite to promotion.
negative old-primary write- Proves clients cannot write to the former authority after routing changes.
Values stay on this page and are never sent or saved.
./operations/rehearse-pg-switchover --environment isolated-restore --primary {{primaryHost}} --standby {{standbyHost}} --require-fence-evidence --no-dns-cache-assumptionsPASS writer freeze: 0 active writers PASS target LSN: 3A/B1008C20 replayed PASS old primary fenced: service/network/storage PASS promoted: timeline 2, recovery=false PASS endpoint moved: one writable backend PASS old endpoint negative write PASS former primary rebuilt as standby RTO 00:02:41 observed data loss 0 bytes
Checkpoint: The team can switch, prove, and restore redundancy
jq -e '.remaining_writers==0 and .replay_gap_bytes==0 and .old_primary_fenced==true and .writable_nodes==1 and .rejoined_standbys==1' rehearsal-result.jsonContinue whenEvery gate passes, induced unsafe states stop the run, RPO/RTO are measured, exactly one writable node exists throughout, and the old primary rejoins safely.
Stop whenThe rehearsal skips fencing/negative tests/rejoin, depends on undocumented operator knowledge, exceeds RPO/RTO, or succeeds only with broad emergency permissions.
If this step fails
The writer drain never reaches zero.
Likely causeA scheduler, worker, direct client, migration, or pool reconnects automatically.
Group `pg_stat_activity` by user/application/client and inspect orchestration schedules.
ResolutionAdd each writer to the quiesce plan and rehearse again; do not terminate unknown sessions blindly.
Clients continue using the old endpoint after TTL.
Likely causeConnection pools, JVM caches, hosts files, proxies, or service discovery retain topology.
Trace connections from application instances and inspect resolver/pool refresh behavior.
ResolutionUse an actively controlled proxy/service endpoint and explicit pool drain; DNS TTL alone is insufficient.
Security notes
- Rehearsal datasets, backups, credentials, and logs remain sensitive even outside production.
Alternatives
- Use a managed service failover rehearsal when infrastructure is provider-owned, while retaining application drain and correctness checks.
Stop conditions
- The rehearsal skips fencing/negative tests/rejoin, depends on undocumented operator knowledge, exceeds RPO/RTO, or succeeds only with broad emergency permissions.
warning
Freeze new writes and drain every existing writer
Enter the approved change window, pause application ingress and all background writers, block new write-capable connections at the controlled layer, wait for in-flight write transactions to finish or explicitly resolve them, then create a final synthetic marker and capture its flushed LSN.
Why this step matters
Planned zero-loss switchover begins at the application write boundary, not with `pg_promote`. A session can remain idle in transaction, a worker can reconnect, and direct credentials can bypass the public API. The team must inventory and close all write paths, allow normal completion where safe, and make an explicit decision about remaining transactions. A final committed marker plus flushed LSN becomes the recovery agreement: promotion cannot proceed until the standby has replayed that exact point. Force-terminating sessions can roll back business work and create retry storms, so it is a last reviewed action rather than a generic drain command.
What to understand
Pause APIs, queues, schedulers, ETL, migrations, maintenance, cron, CI, and direct administrative writers.
Inspect `pg_stat_activity`, prepared transactions, advisory locks, logical jobs, and connection pools from all credentials.
Reject or remove new writer routing while leaving required monitoring and replication paths intact.
Wait for known transactions; escalate old/unknown transactions to application owners before cancel/terminate.
Commit a unique marker, capture `pg_current_wal_flush_lsn()`, timestamp, timeline, and operator approvals.
System changes
- Temporarily makes the application unavailable for writes.
- Creates a controlled final marker and freezes the target LSN.
Syntax explained
pg_current_wal_flush_lsn- Primary WAL location durably flushed locally after the final marker commit.
idle in transaction- A session with an open transaction that may hold locks/snapshots even when not currently executing.
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -c "SELECT pid,usename,application_name,client_addr,xact_start,state,wait_event_type,query FROM pg_stat_activity WHERE backend_type='client backend' AND pid<>pg_backend_pid() ORDER BY xact_start NULLS LAST;" -c "SELECT pg_current_wal_flush_lsn() AS switchover_target_lsn, clock_timestamp() AS captured_at;"pid | usename | application_name | client_addr | xact_start | state | wait_event_type | query -----+---------+------------------+-------------+------------+-------+-----------------+------- (0 rows) switchover_target_lsn | captured_at -----------------------+------------------------------- 3A/C04A11D8 | 2026-07-28 12:00:08.221+00
Checkpoint: No writer can create WAL beyond the recorded target
sudo -u postgres psql -XAtqc "SELECT count(*) FROM pg_stat_activity WHERE backend_type='client backend' AND pid<>pg_backend_pid(); SELECT gid FROM pg_prepared_xacts;"Continue whenAll writer sources are paused, no unexplained client/prepared transaction remains, new writes are rejected, and final marker/target LSN are protected in the change record.
Stop whenAny application, worker, direct client, prepared transaction, migration, or auto-reconnecting writer remains unexplained or can write after the target is captured.
If this step fails
A write connection reappears after drain.
Likely causeAn unpaused autoscaler, scheduler, health repair, or pool reconnects.
Identify application_name/user/client and trace its controller before taking action.
ResolutionPause the owning controller and restart the drain/target-LSN capture; the previous target is obsolete.
A long transaction cannot finish within the window.
Likely causeBusiness work, lock wait, client abandonment, or prepared transaction remains.
Inspect transaction age, locks, query, owner, retry semantics, and prepared-xact purpose.
ResolutionAbort switchover unless the application owner approves safe cancellation and consequence handling.
Security notes
- Do not expose query text containing secrets in change records; redact while retaining identity and transaction evidence.
Alternatives
- Use an application maintenance mode and connection proxy that can atomically reject writer roles and drain pools.
Stop conditions
- Any application, worker, direct client, prepared transaction, migration, or auto-reconnecting writer remains unexplained or can write after the target is captured.
warning
Wait for exact replay, then fence and stop the old primary
On standby, wait until `pg_last_wal_replay_lsn()` is at or beyond the recorded target and the final marker is visible. Only then isolate the old primary from clients and automation, stop PostgreSQL, verify it cannot restart or receive traffic, and preserve its control/timeline state.
Why this step matters
Replay equality establishes that the candidate contains the agreed final commit. Fencing establishes that the old authority cannot resume. Both are mandatory and ordered: promote before catch-up risks data loss; promote before fencing risks two primaries. A clean shutdown alone is not durable fencing because service managers, humans, orchestration, or boot can restart it. Conversely, power fencing without removing client routing may leave stale paths. The evidence must cover service, network/load balancer, automation, and any shared storage or power boundary relevant to this design.
What to understand
Poll replay LSN without pausing replay and verify the unique marker through a read-only standby query.
Confirm receiver has no error and archive/slot state is healthy at the target.
Remove old primary from writer endpoint and client security path, then stop and mask its service through the owned mechanism.
Disable orchestration auto-heal/start and prove a client cannot connect or write to the old address.
Capture clean shutdown checkpoint/timeline/control data for possible rewind while keeping the node isolated.
System changes
- Stops and fences the current primary, creating a deliberate write outage.
- Removes the old node from client and automation control paths.
Syntax explained
pg_wal_lsn_diff(replay,target) >= 0- Proves the candidate has applied at least the agreed final WAL location.
service/network/orchestration fence- Independent controls preventing accidental old-primary service, routing, and restart.
Values stay on this page and are never sent or saved.
sudo -u postgres psql -XAtqc "SELECT pg_last_wal_replay_lsn(), pg_wal_lsn_diff(pg_last_wal_replay_lsn(),'{{targetLsn}}') >= 0, pg_is_in_recovery();" && ./operations/fence-node --node {{primaryHost}} --deny-client --disable-service-autostart --verify3A/C04A11D8|t|t PASS removed from writer endpoint PASS client ingress denied PASS postgresql@18-main stopped and masked PASS orchestration restart disabled PASS storage ownership remains singular
Checkpoint: The candidate is current and the old authority is inert
./operations/fence-node --node {{primaryHost}} --verify && sudo -u postgres psql -XAtqc "SELECT pg_wal_lsn_diff(pg_last_wal_replay_lsn(),'{{targetLsn}}') >= 0;"Continue whenTarget replay/marker verified, old primary stopped and fenced on every client/automation path, two operators approve, and exactly zero writable nodes exist before promotion.
Stop whenReplay is behind, marker is absent, old primary responds, service can auto-restart, storage authority is ambiguous, or fence evidence is incomplete.
If this step fails
Replay stops short of the target after writer drain.
Likely causeReceiver/archive failure, missing WAL, replay pause, conflict, or incorrect target LSN exists.
Inspect receiver, replay pause, logs, archive for exact segment, and target capture provenance.
ResolutionKeep old primary available but write-frozen if safe, repair replication, or abort and restore writers there; do not promote behind target.
The fencing tool cannot verify one control plane.
Likely causePermissions, stale inventory, provider outage, or automation ownership is missing.
Manually inspect that exact service/network/storage/orchestration layer with a second operator.
ResolutionAbort promotion until a reliable fence is established; uncertainty is not evidence.
Security notes
- Fencing commands are high-impact and must be scoped to the exact node identity, peer-reviewed, and logged without secrets.
Alternatives
- Use hardware/provider power fencing or a quorum-controlled HA manager when manual multi-layer fencing cannot meet RTO.
Stop conditions
- Replay is behind, marker is absent, old primary responds, service can auto-restart, storage authority is ambiguous, or fence evidence is incomplete.
command
Promote exactly one standby and reopen traffic gradually
Promote the verified target with a bounded wait, confirm recovery ended and a new timeline is active, run read/write and invariant checks directly, update the controlled writer endpoint, drain/refresh pools, then reopen a small fraction of traffic before full service.
Why this step matters
Promotion is irreversible as a simple toggle: the standby exits recovery and branches history onto a new timeline. The command's success means recovery ended, not that applications, invariants, sequences, extensions, background jobs, archive, backups, or endpoint routing are healthy. Direct validation precedes endpoint movement, and canary traffic precedes full load. The writer endpoint must contain exactly one backend and the old primary must remain fenced. Preserve the promotion timestamp, old/new timeline, last replay LSN, first new commit LSN, and endpoint change for later rewind and audit.
What to understand
Invoke promotion once against the exact standby after rechecking fence and replay evidence.
Require `pg_is_in_recovery()=false`, writable transaction state, expected databases/extensions, and a new timeline in control/log evidence.
Commit a non-sensitive canary transaction and read it back directly from the promoted node.
Move only the writer endpoint, verify old backend absence from multiple client networks, and explicitly recycle stale pools.
Reopen traffic in stages while monitoring errors, latency, invariants, WAL/archive, backups, jobs, and unauthorized old-node attempts.
System changes
- Creates a new writable PostgreSQL timeline.
- Moves the application writer endpoint and resumes production writes.
Syntax explained
pg_promote(true,60)- Requests promotion and waits up to 60 seconds for recovery to end; a timeout needs investigation, not a second blind promotion.
one-backend endpoint- Maintains singular writer authority while clients reconnect.
Values stay on this page and are never sent or saved.
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -c "SELECT pg_promote(true, 60);" -c "SELECT pg_is_in_recovery(), current_setting('transaction_read_only'), pg_current_wal_lsn();" && ./operations/move-writer-endpoint --endpoint {{writerEndpoint}} --target {{standbyHost}} --require-old-fencedpg_promote ------------ t (1 row) pg_is_in_recovery | transaction_read_only | pg_current_wal_lsn -------------------+-----------------------+-------------------- f | off | 3A/C1000060 PASS writer endpoint has one backend: pg-standby.internal.example PASS old backend absent PASS canary transaction committed and read back
Checkpoint: One new primary serves validated writes
sudo -u postgres psql -XAtqc "SELECT NOT pg_is_in_recovery(), current_setting('transaction_read_only')='off';" && ./operations/check-writer-endpoint --expect {{standbyHost}} --expect-count 1Continue whenPromotion complete on a new timeline, one direct canary commit succeeds, endpoint has exactly one backend, old node remains unreachable, and application invariants pass under staged traffic.
Stop whenPromotion state is ambiguous, more than one writer is reachable, timeline/invariants differ, archive/backup fails, canary write fails, or error/latency thresholds breach.
If this step fails
`pg_promote` times out.
Likely causePromotion is still completing, replay is paused, startup process is blocked, or the operator targeted the wrong server.
Query recovery state once, inspect logs/processes/control data, and retain the old-primary fence.
ResolutionDiagnose the single candidate; do not promote another node or reopen the old primary without a new authority decision.
Some application instances still connect to the old host.
Likely causePools, DNS caches, static connection strings, or alternate endpoints bypass control.
Trace `pg_stat_activity`, deployment configuration, resolver and proxy state from every client group.
ResolutionKeep old node fenced, correct and recycle clients, and delay full traffic until all paths converge.
Security notes
- Promotion and endpoint credentials are privileged operational capabilities; require short-lived access and two-person authorization.
Alternatives
- Use a PostgreSQL-aware proxy or managed service endpoint that can atomically change one writer backend after external fencing.
Stop conditions
- Promotion state is ambiguous, more than one writer is reachable, timeline/invariants differ, archive/backup fails, canary write fails, or error/latency thresholds breach.
command
Rejoin the former primary with pg_rewind or a fresh base backup
Keep the former primary fenced and stopped. Prove same system identifier, divergence, clean target shutdown, source authority, checksums or `wal_log_hints`, required WAL/archive availability, and secure source credentials. Run `pg_rewind --dry-run`, stop for explicit approval, then perform the actual rewind with recovery configuration and start only as a standby. If any prerequisite fails, preserve evidence and rebuild the target with a fresh base backup.
Why this step matters
`pg_rewind` changes the target data directory so it matches the new primary after timelines diverge. It relies on changed-block evidence produced by data checksums or `wal_log_hints`, needs WAL back to the divergence checkpoint, and copies configuration files from the source as part of synchronization. Therefore the former primary must remain stopped and backed up, the new primary must be the authoritative source, credentials must not leak, and post-rewind recovery configuration must be reviewed. A dry run reduces—not removes—risk. If ancestry or required WAL is uncertain, a fresh base backup is slower but clearer and safer.
What to understand
Preserve old-primary control data, logs, timeline files, configuration and a recoverable snapshot before destructive work.
Verify source and target system identifiers match and source is the sole promoted primary; target is stopped and fenced.
Confirm checksums or `wal_log_hints` were enabled before divergence and every required WAL segment is in target `pg_wal` or archive.
Use a narrowly granted rewind connection role per upstream requirements, TLS verify-full, and a protected password source.
`--write-recovery-conf` creates `standby.signal` and appends source connection settings only after the approved actual rewind; inspect generated settings and keep passwords in a protected `.pgpass` or equivalent secret channel.
After actual rewind, restore node-specific configuration, bind the reviewed permanent slot, start as recovery, and verify read-only state, WAL receiver source, slot, timeline, and application marker before considering the node redundant.
System changes
- Dry run is read-only; actual `pg_rewind` overwrites target cluster files.
- A rebuild deletes/replaces the target copy only after preserved evidence and exact path confirmation.
Syntax explained
--target-pgdata- The stopped diverged cluster that will be overwritten; reversing source and target can destroy the authoritative copy.
--source-server- Connects to the new authoritative primary for source files and metadata through a normal database connection.
--dry-run- Checks and reports planned rewind without modifying target files; still requires identity and credential care.
Values stay on this page and are never sent or saved.
sudo -u postgres pg_rewind --dry-run --target-pgdata={{dataDirectory}} --source-server="host={{standbyHost}} port=5432 dbname=postgres user=rewind_check sslmode=verify-full sslrootcert=/etc/postgresql/tls/root.crt" --progress && ./operations/require-two-person-approval --change rejoin-former-primary --target {{primaryHost}} --source {{standbyHost}} && sudo -u postgres pg_rewind --target-pgdata={{dataDirectory}} --source-server="host={{standbyHost}} port=5432 dbname=postgres user=rewind_check sslmode=verify-full sslrootcert=/etc/postgresql/tls/root.crt" --write-recovery-conf --progress && sudo -u postgres sh -c "printf 'primary_slot_name = {{replicationSlot}}\n' >> '{{dataDirectory}}/postgresql.auto.conf'" && sudo systemctl start postgresql@18-main && sudo -u postgres psql -XAtqc "SELECT pg_is_in_recovery();" && sudo -u postgres psql -X -c "SELECT status,sender_host,slot_name,latest_end_lsn FROM pg_stat_wal_receiver;"pg_rewind: servers diverged at WAL location 3A/C04A11D8 on timeline 1 pg_rewind: rewinding from last common checkpoint at 3A/C0000028 on timeline 1 pg_rewind: dry run complete APPROVED rejoin-former-primary target=pg-primary.internal.example source=pg-standby.internal.example reviewers=2 pg_rewind: servers diverged at WAL location 3A/C04A11D8 on timeline 1 pg_rewind: Done! t status | sender_host | slot_name | latest_end_lsn -----------+-------------------------------+----------------------+---------------- streaming | pg-standby.internal.example | pg_primary_rejoined | 3A/C1000060
Checkpoint: The former primary rejoins only as a caught-up standby
sudo -u postgres psql -XAtqc "SELECT pg_is_in_recovery(),status,sender_host,slot_name FROM pg_stat_wal_receiver;"Continue whenDry run/prerequisites pass or a fresh verified base backup is used; node starts in recovery against new primary, streams with a new owned slot, sees post-promotion marker, and rejects writes.
Stop whenSource/target identity is uncertain, target is running/unfenced, system IDs differ, required WAL is missing, checksums/hints prerequisites fail, backup is absent, or credentials/config copies are unsafe.
If this step fails
`pg_rewind` cannot find the previous checkpoint WAL.
Likely causeRequired divergence history was recycled or never archived.
Record exact missing WAL and inspect the verified archive; do not fetch from an unknown cluster.
ResolutionStop rewind and rebuild from a fresh base backup of the new primary.
Rewound node starts writable.
Likely causestandby.signal or primary connection settings were not restored, or it was accidentally promoted.
Immediately fence/stop it and inspect signal/config/control/timeline evidence.
ResolutionTreat as split-brain risk, preserve evidence, and rebuild or correct recovery only after authority is re-established.
Security notes
- `pg_rewind` source access can read cluster files through server-side functions. Use a purpose-built role, encrypted connection, short lifetime, and revoke it after rejoin.
Alternatives
- Prefer a new verified `pg_basebackup` whenever target history or rewind prerequisites are not provable.
Stop conditions
- Source/target identity is uncertain, target is running/unfenced, system IDs differ, required WAL is missing, checksums/hints prerequisites fail, backup is absent, or credentials/config copies are unsafe.
Finish line
Verification checklist
sudo -u postgres psql -X -c "SELECT application_name,state,sync_state,sent_lsn,flush_lsn,replay_lsn FROM pg_stat_replication;"Exactly the intended standby streams and its sender/replay positions remain within approved byte/time thresholds.sudo -u postgres psql -X -c "SELECT slot_name,active,wal_status,safe_wal_size FROM pg_replication_slots; SELECT archived_count,failed_count,last_archived_time,last_failed_time FROM pg_stat_archiver;"Owned slot active with safe capacity; archive current without unresolved failure../operations/check-writer-endpoint --expect {{standbyHost}} --expect-count 1One promoted primary serves writer endpoint and the former primary remains fenced.sudo -u postgres psql -XAtqc "SELECT count(*) FROM pg_stat_replication WHERE state='streaming';"Former primary has been rewound or rebuilt and follows the new timeline as read-only standby.Recovery guidance
Common problems and safe checks
The standby repeatedly reports requested WAL segment has already been removed.
Likely causeThe standby fell behind beyond unprotected retention, used the wrong slot, or the slot was absent/inactive.
Inspect `pg_stat_wal_receiver` on standby and `pg_replication_slots` on primary.Check the archive for the exact segment without modifying either cluster.
ResolutionRestore the segment from the verified archive if valid; otherwise rebuild from a new base backup. Do not copy arbitrary `pg_wal` files.
The physical slot retains rapidly growing WAL.
Likely causeIts consumer is offline, misconfigured, blocked, or too slow while the slot continues its retention promise.
Measure `pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)` and filesystem free space.Confirm slot `active`, `wal_status`, `safe_wal_size`, and the owning standby.
ResolutionRestore the consumer promptly or follow the approved rebuild-and-drop procedure before disk exhaustion; never drop an unidentified slot.
The standby is streaming but replay lag grows.
Likely causeReplay I/O/CPU is saturated, recovery conflicts pause work, WAL generation exceeds capacity, or replay was intentionally paused.
Compare receive, flush, and replay LSNs and inspect `pg_stat_database_conflicts`.Check `pg_is_wal_replay_paused()`, storage latency, CPU, and PostgreSQL logs.
ResolutionResume an intentionally paused replay or remove the verified bottleneck; do not promote a lagging standby.
Promotion completed but applications cannot write.
Likely causeThe endpoint, pool, authentication, read-only transaction setting, firewall, or application state still references the old topology.
Connect directly to the new node and query `pg_is_in_recovery()` and `transaction_read_only`.Resolve the writer endpoint from an application host and inspect pool targets.
ResolutionKeep traffic closed, correct routing/authentication, prove one writable primary, then reopen gradually.
The old primary starts and accepts connections after promotion.
Likely causeFencing was incomplete or service automation restarted it using its old data directory.
Remove it from every endpoint and inspect service, firewall, orchestration, and storage attachment state.Compare system identifiers and timeline histories without starting writes.
ResolutionImmediately isolate the old node, preserve logs, declare a split-brain incident, and reconcile from the authoritative new primary.
`pg_rewind` reports that required WAL is missing.
Likely causeThe target's divergence WAL was recycled and is absent from both `pg_wal` and the configured archive.
Record the exact missing segment and inspect retained archives read-only.Verify the last common checkpoint and target/source system identifiers.
ResolutionAbandon rewind and rebuild the old primary as a new standby from a fresh verified base backup.
Read queries on standby are canceled.
Likely causeWAL replay conflicts with snapshots, locks, buffer pins, or required cleanup on the primary.
Inspect `pg_stat_database_conflicts` by reason and the canceled statement logs.Review `max_standby_streaming_delay` and long standby transactions.
ResolutionShorten reporting transactions or route them elsewhere; tune conflict settings only after evaluating replay lag and primary bloat tradeoffs.
Replication authentication fails despite a correct role.
Likely causeThe `pg_hba.conf` rule, TLS mode, certificate name, source address, password file, or config reload is wrong.
Use `pg_hba_file_rules` to inspect parsed rules and errors.Test certificate chain/name and inspect server logs without printing the password.
ResolutionCorrect the narrow rule or credential, reload configuration, and retain certificate verification; do not temporarily add `trust` or a broad CIDR.
Reference
Frequently asked questions
Does PostgreSQL automatically fail over this pair?
No. PostgreSQL 18 supplies streaming, recovery, promotion, and inspection primitives but not failure detection, quorum, fencing, or client rerouting. This guide is a planned human-coordinated switchover.
Does a replication slot make the standby safe?
It protects WAL from normal recycling for that consumer, but can fill the primary disk. Safety requires byte limits, alerts, ownership, archive coverage, and a rebuild/drop decision process.
Can the old primary simply become primary again?
Not after the promoted standby accepts writes. The histories diverge. Keep the old node fenced and use verified `pg_rewind` or a fresh base backup to make it a standby.
Recovery
Rollback
Before promotion, rollback means aborting the switchover: keep the standby in recovery, reopen the original primary only after confirming it remains sole authority, undo the write freeze, and revalidate streaming. After promotion and any new commit, there is no safe role-toggle rollback. The former primary stays fenced and is rewound or rebuilt; moving authority back requires another complete planned switchover from the current authoritative timeline.
- If a pre-promotion gate fails, do not promote; preserve target LSN and diagnostics.
- Confirm the original primary was never superseded and remains the only writable node.
- Undo endpoint/service fencing in controlled order and reopen application writes gradually.
- Repair or rebuild the standby and require marker, lag, archive, slot, and read-only checks.
- If promotion occurred, declare the promoted node authoritative and keep the old primary fenced.
- Preserve old-node control/log/timeline evidence and choose verified pg_rewind or fresh base backup.
- Restore redundancy, then schedule a new planned switchover if returning workloads to the original host is still desired.
- Never point clients back to a diverged old primary or attempt to merge physical timelines.
Evidence