Streaming Replication Basics

Physical streaming replication ships WAL records from a primary to one or more standbys in near real time. Standbys replay WAL and stay read-only until promoted.

Recipe

Quick setup checklist for a single async standby on PostgreSQL 18.4.

-- Primary: allow replication connections
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
SELECT pg_reload_conf();
 
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';

# Standby: base backup from primary, then start in recovery
pg_basebackup -h primary.db.internal -D /var/lib/postgresql/18/main \
  -U replicator -Fp -Xs -P -R

When to reach for this: You need a hot standby for failover, read scaling, or backups without adding logical decoding overhead.

Working Example

Primary postgresql.conf and pg_hba.conf, then standby creation:

# On primary host - pg_hba.conf snippet
# host  replication  replicator  10.0.0.0/8  scram-sha-256
 
# On standby host
sudo -u postgres pg_basebackup \
  -h primary.db.internal \
  -p 5432 \
  -U replicator \
  -D /var/lib/postgresql/18/main \
  -Fp -Xs -P -R \
  -C -S standby1_slot

-- On primary: confirm standby is connected
SELECT application_name, client_addr, state, sync_state
FROM pg_stat_replication;

What this demonstrates:

• wal_level = replica enables physical replication without full logical decoding cost.
• pg_basebackup -R writes primary_conninfo and standby.signal for streaming recovery.
• -C -S standby1_slot creates a physical replication slot so WAL is retained for the new standby.

Deep Dive

How It Works

• The primary writes transactions to WAL on disk.
• WAL sender processes on the primary read WAL and stream it to WAL receiver processes on standbys.
• Standbys apply WAL via the startup (recovery) process; they serve read queries when hot_standby = on.
• Promotion ends recovery: remove standby.signal, run pg_ctl promote, or let Patroni 3.x elect a new leader.

Primary Settings

Standby Files

Gotchas

• Missing replication slot - Standby offline too long; primary recycles WAL and standby cannot catch up. Fix: Always use a physical slot per standby.
• wal_level too low - minimal or insufficient setting blocks replication. Fix: Set replica or logical, restart if required.
• Replication user blocked by pg_hba - Connection refused or auth failures. Fix: Add replication entry with scram-sha-256.
• Promoting without proxy update - Apps still point at old primary IP. Fix: Route through HAProxy or Patroni REST endpoint before failover drills.
• Large lag on busy primaries - Network or slow replay on standby. Fix: Monitor pg_stat_replication.replay_lag and size standby I/O to match primary.

Alternatives

FAQs

Related

• Synchronous vs Asynchronous - RPO trade-offs
• Replication Slots - WAL retention
• Replication Monitoring - lag alerts
• HA Basics - uptime targets

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.