Use when setting up a hot-standby replica, choosing synchronous_commit durability, or debugging replica lag and query cancellation. Prevents disk filling from a physical slot without max_slot_wal_keep_size, primary stalls when a synchronous standby goes down, and standby query cancellation from conflicting vacuum. Covers pg_basebackup clone, primary_conninfo + standby.signal, hot_standby, physical replication slots, WAL archiving, synchronous_commit modes, synchronous_standby_names quorum, pg_promote, cascading replication, pg_stat_replication monitoring, hot_standby_feedback. Keywords: streaming replication, hot standby, pg_basebackup, replication slot, synchronous_commit, synchronous_standby_names, pg_promote, standby.signal, pg_stat_replication, replica lag, max_slot_wal_keep_size, query canceled on standby, disk full replication, how to set up a replica, failover
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Use when setting up a hot-standby replica, choosing synchronous_commit durability, or debugging replica lag and query cancellation. Prevents disk filling from a physical slot without max_slot_wal_keep_size, primary stalls when a synchronous standby goes down, and standby query cancellation from conflicting vacuum. Covers pg_basebackup clone, primary_conninfo + standby.signal, hot_standby, physical replication slots, WAL archiving, synchronous_commit modes, synchronous_standby_names quorum, pg_promote, cascading replication, pg_stat_replication monitoring, hot_standby_feedback. Keywords: streaming replication, hot standby, pg_basebackup, replication slot, synchronous_commit, synchronous_standby_names, pg_promote, standby.signal, pg_stat_replication, replica lag, max_slot_wal_keep_size, query canceled on standby, disk full replication, how to set up a replica, failover
license
MIT
compatibility
Designed for Claude Code. Requires PostgreSQL 15, 16, or 17.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
postgres-impl-streaming-replication
Quick Reference :
Streaming (physical) replication ships raw WAL records over TCP from a primary to one or more standbys. The standby is a byte-identical copy of the whole cluster , every database, role, and table , and replays WAL continuously. It is not selective ; for row-level, cross-version replication use logical replication instead.
Setup is three steps : (1) prepare the primary (wal_level = replica, a REPLICATION role, a pg_hba.conf replication line), (2) clone the data directory with pg_basebackup ... -R, (3) start the standby , the standby.signal file puts it in standby mode and it streams from primary_conninfo.
A standby with hot_standby = on (the default) serves read-only queries. A physical replication slot stops the primary from recycling WAL the standby has not consumed yet , but a slot whose consumer is gone grows pg_wal without bound. ALWAYS set max_slot_wal_keep_size (v13+) so a dead slot is invalidated instead of filling the disk.
synchronous_commit trades durability against commit latency. The default on waits for the local WAL flush ; with synchronous_standby_names set it also waits for the standby. NEVER point synchronous_standby_names at a single standby with no quorum , if that standby goes down, every commit on the primary stalls.
When To Use This Skill :
ALWAYS use this skill when :
Setting up a hot-standby read replica or a high-availability failover pair
Choosing a synchronous_commit durability level
A replica is lagging, or a standby query is being canceled
pg_wal is filling up because of a replication slot
Planning promotion / failover or cascading replication
NEVER use this skill for :
Row-level or cross-major-version replication, publications/subscriptions : that is logical replication , see postgres-impl-logical-replication
Point-in-time recovery and pg_basebackup for backup (not replica) : see postgres-impl-backup-restore
WAL fundamentals and the durability rule : see postgres-core-architecture
Decision Trees :
Physical streaming or logical replication? :
What do you need to replicate?
├── The entire cluster, byte-identical, same major version, for HA / read scaling :
│ PHYSICAL streaming replication (this skill)
├── Specific tables, or across major versions / platforms, or filtered rows :
│ LOGICAL replication (see postgres-impl-logical-replication)
└── A one-off restorable snapshot, not a live follower :
pg_basebackup as backup (see postgres-impl-backup-restore)
synchronous_commit durability ladder :
How much data loss is acceptable on a crash?
├── Throughput matters more than the last few commits (bulk load, cache) :
│ off , no wait at all ; recent commits lost on crash
├── Survive a primary OS crash, standby state irrelevant (DEFAULT) :
│ on , wait for local WAL fsync (+ standby fsync if sync standby set)
├── Standby may lose commits only on a standby OS crash :
│ remote_write , wait for standby OS write, not its fsync
└── Zero data loss AND the commit must be visible on the standby :
remote_apply , wait for the standby to REPLAY the commit
(highest latency ; needs synchronous_standby_names)
WAL retention for a standby :
How does the primary keep WAL the standby still needs?
├── Replication slot (RECOMMENDED) :
│ retains exactly what the standby has not consumed.
│ ALWAYS pair with max_slot_wal_keep_size so a dead slot
│ is invalidated instead of filling pg_wal.
├── wal_keep_size :
│ retains a fixed amount of recent WAL ; simple, but a slow
│ standby beyond the window must re-base or use archive.
└── archive_command + restore_command :
the standby fetches missed WAL from the archive ;
the durable fallback, combine with a slot or wal_keep_size.
Patterns :
Pattern 1 : Prepare the primary
ALWAYS create a dedicated REPLICATION-attribute role and add a replication line to pg_hba.conf.
NEVER reuse a superuser for the replication connection , the role needs REPLICATION and LOGIN, nothing more.
-- On the primaryCREATE ROLE repuser WITH REPLICATION LOGIN PASSWORD 'strong-secret';
# postgresql.conf (wal_level=replica and max_wal_senders=10 are defaults since v10)wal_level = replica
max_wal_senders = 10max_replication_slots = 10# pg_hba.conf , "replication" is a literal keyword, not a database name
host replication repuser 10.0.0.0/24 scram-sha-256
WHY : wal_level = replica (the default) logs enough for WAL shipping. The replication keyword in pg_hba.conf is a special pseudo-database that ONLY matches replication connections , a normal host all line does not grant replication. Use scram-sha-256, never trust or md5, for the replication role. Source : postgresql.org/docs/17/warm-standby.html.
Pattern 2 : Clone the standby with pg_basebackup
ALWAYS use pg_basebackup -R , it writes standby.signal and the connection settings for you.
NEVER hand-copy the primary's data directory while it is running , pg_basebackup takes a consistent online base backup; a raw cp does not.
WHY : --wal-method=stream (-X stream) streams WAL during the backup so the result is immediately consistent. --write-recovery-conf (-R) creates an empty standby.signal file in the new data directory and appends primary_conninfo (and primary_slot_name, if -S was given) to postgresql.auto.conf. Start the cluster on that data directory and it comes up as a streaming standby. Source : postgresql.org/docs/17/app-pgbasebackup.html.
Pattern 3 : Physical replication slot with a size cap
ALWAYS create a physical slot AND set max_slot_wal_keep_size together.
NEVER run a slot uncapped , if the standby disconnects, the slot pins WAL forever and fills pg_wal until the primary crashes with a full disk.
-- On the primary : create the slotSELECT pg_create_physical_replication_slot('standby1_slot');
# Primary postgresql.conf : cap how much WAL a slot may pinmax_slot_wal_keep_size = 10GB
# Standby postgresql.auto.conf (written by pg_basebackup -S, or set manually)primary_slot_name = 'standby1_slot'
WHY : a slot makes the primary retain exactly the WAL the standby has not confirmed , better than wal_keep_size guesswork. The danger is the inverse : a slot whose consumer never returns retains WAL without limit. max_slot_wal_keep_size (v13+) caps that , once a slot would exceed the cap it is invalidated (pg_replication_slots.wal_status = 'lost') and WAL is freed. The standby then needs a re-base or the archive. A bounded outage beats a full disk. Source : postgresql.org/docs/17/warm-standby.html.
Pattern 4 : Synchronous replication with a quorum
ALWAYS use ANY n (...) or FIRST n (...) with more standbys than n, so one standby can fail without stalling commits.
NEVER set synchronous_standby_names to a single standby name , losing that one standby blocks every commit on the primary.
# Primary postgresql.confsynchronous_commit = on# Quorum : wait for ANY 1 of these 2 standbys to confirmsynchronous_standby_names = 'ANY 1 (standby1, standby2)'# Priority : wait for the FIRST available of the list# synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
WHY : with synchronous_standby_names set, synchronous_commit = on makes a commit wait for the named standbys to flush WAL to disk. ANY n (list) is quorum-based , any n of the listed standbys satisfy it. FIRST n (list) is priority-based , the first n available in list order. A bare list 's1, s2' is the legacy form, equal to FIRST 1. Always list MORE standbys than n so a single failure is absorbed. The standby names match application_name in each standby's primary_conninfo. Source : postgresql.org/docs/17/warm-standby.html.
Pattern 5 : Hot standby reads and recovery-conflict tuning
ALWAYS decide between hot_standby_feedback (no cancellations, primary bloat risk) and max_standby_streaming_delay (bounded lag, queries cancelled).
NEVER run long analytic queries on a default-tuned standby and expect them to finish , WAL replay cancels conflicting queries after 30 seconds.
# Standby postgresql.confhot_standby = on# default ; serves read-only queries# Option A : let standby queries finish, tell the primary to hold tupleshot_standby_feedback = on# Option B : bound replay lag instead, accept query cancellationmax_standby_streaming_delay = 30s # default
WHY : a hot standby replays WAL while serving reads. When replay hits a change that conflicts with a running query (a VACUUM removing tuples the query still sees, an ACCESS EXCLUSIVE lock), PostgreSQL cancels the query after max_standby_streaming_delay , SQLSTATE 40001, "canceling statement due to conflict with recovery". hot_standby_feedback = on sends the standby's oldest xmin upstream so the primary's VACUUM keeps those tuples , no cancellation, but the primary bloats if a standby query runs long. Pick one tradeoff per workload. Source : postgresql.org/docs/17/hot-standby.html.
Pattern 6 : Promotion and failover
ALWAYS promote with pg_promote() or pg_ctl promote.
NEVER expect a promote_trigger_file , that parameter was removed in v16.
-- On the standby : promote it to a read-write primarySELECT pg_promote(wait =>true, wait_seconds =>60);
-- Verify : in_recovery is now falseSELECT pg_is_in_recovery();
# Equivalent from the shell
pg_ctl promote -D /var/lib/postgresql/17/standby
WHY : promotion ends recovery , the standby finishes replaying any WAL already available, then switches to normal read-write operation on a new timeline. It does NOT contact the old primary, so the old primary MUST be fenced off first to avoid a split brain. Other standbys following the promoted node need recovery_target_timeline = 'latest' (the default) to follow the timeline switch. Source : postgresql.org/docs/17/warm-standby.html.
Pattern 7 : Cascading replication
ALWAYS enable max_wal_senders and hot_standby on a standby that will feed other standbys.
NEVER expect synchronous replication through a cascade , cascaded streaming is asynchronous only.
# Cascading (intermediate) standby postgresql.confhot_standby = onmax_wal_senders = 10# Downstream standby : point primary_conninfo at the cascading standbyprimary_conninfo = 'host=cascade.internal user=repuser application_name=leaf1'
WHY : a cascading standby relays WAL downstream, cutting the number of direct streaming connections to the primary. It forwards both WAL streamed from the primary and WAL restored from archive, and keeps feeding downstream servers even if its own upstream link drops. Cascaded links are always asynchronous , synchronous_standby_names on the primary has no effect on standbys behind a cascade. Source : postgresql.org/docs/17/warm-standby.html.
Pattern 8 : Monitor lag and slot health
ALWAYS alarm on both replay lag (pg_stat_replication) and slot retention (pg_replication_slots).
NEVER assume a connected standby is current , a standby can stream WAL yet fall far behind on replay.
-- On the primary : per-standby lag (LSN positions + time intervals)SELECT application_name, state,
sent_lsn, write_lsn, flush_lsn, replay_lsn,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- On the primary : WAL pinned by each slot ; watch wal_statusSELECT slot_name, active, wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
-- On the standby : how far behind replay is, in timeSELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;
WHY : replay_lsn trailing sent_lsn means WAL has arrived but is not applied yet , reads on the standby see stale data. pg_replication_slots.wal_status of extended or unreserved warns that a slot is approaching max_slot_wal_keep_size ; lost means the slot was already invalidated. Monitoring both is the only early warning before a disk fills or a failover loses data. Source : postgresql.org/docs/17/warm-standby.html.
Anti-Patterns :
(One-liners ; full diagnosis + fix in references/anti-patterns.md.)
Physical slot with no max_slot_wal_keep_size , a disconnected standby pins WAL until pg_wal fills the disk and the primary crashes.
synchronous_standby_names naming a single standby , that standby going down stalls every commit on the primary.
synchronous_commit = remote_apply with one standby , primary commit latency is hostage to standby replay.
Long query on a default-tuned hot standby , canceled after max_standby_streaming_delay : SQLSTATE 40001.
hot_standby_feedback = on ignored as a bloat source , long standby queries hold back the primary's VACUUM.
Raw cp of a running primary's data directory , inconsistent copy ; use pg_basebackup.
pg_hba.conf has host all but no host replication line , the replication connection is rejected.
Promoting a standby without fencing the old primary , split brain, divergent timelines.
Expecting promote_trigger_file to work , removed in v16 ; use pg_promote().
Monitoring connection state but not replay_lsn , a "connected" standby can still serve badly stale reads.
pg_dump run against a hot standby during heavy replay , conflicts can cancel the dump : SQLSTATE 40001.
