Streaming replication

conceptThe concept

Open the interactive lesson to use this diagram.

A standby is created from a base backup and started with standby.signal and primary_conninfo. Its WAL receiver connects to a WAL sender on the primary and streams WAL, which the startup process replays. With hot_standby = on it serves read-only queries.

By default replication is asynchronous: commits return when WAL is flushed locally, so a failover can lose the last moments of transactions. synchronous_standby_names makes commits wait for standby confirmation; synchronous_commit picks the level: remote_write, on (flushed on the standby) or remote_apply (visible on the standby, for read-your-writes).

A replication slot makes the primary keep WAL until the standby has received it, so a disconnected standby can catch up. The danger: an abandoned slot retains WAL forever and fills the disk; cap it with max_slot_wal_keep_size.

whyWhy it matters

Replication provides high availability, read scaling and a hot copy for upgrades and migrations. Misconfigured, it causes full disks (stale slots), lost transactions (async failover) or cancelled reports on the standby (query conflicts).

Use caseReal-world scenario

The shop runs a primary and two standbys in different availability zones: one synchronous with ANY 1 (s1, s2) for zero data loss, and reporting queries on the other with hot_standby_feedback considered carefully.

Try itExamples

Run and edit these in the interactive lesson.

# primary: postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
max_slot_wal_keep_size = '50GB'
synchronous_standby_names = 'ANY 1 (standby_a, standby_b)'

# primary: pg_hba.conf
hostssl replication replicator 10.0.0.0/16 scram-sha-256

# standby: clone and configure in one step
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/18/main \
  -X stream -R --slot=standby_a -C -P

SELECT application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

SELECT pg_is_in_recovery() AS is_standby,
       current_setting('synchronous_commit') AS synchronous_commit,
       current_setting('hot_standby') AS hot_standby;

internalsUnder the hood

Long queries on a standby can conflict with WAL replay (for example, VACUUM on the primary removed rows the query still needs). The standby waits up to max_standby_streaming_delay and then cancels the query. hot_standby_feedback avoids that by telling the primary about the standby's oldest snapshot, at the cost of bloat on the primary.

Avoid / preferDo this, not that

SELECT pg_create_physical_replication_slot('standby_b');
-- standby_b is decommissioned; the slot stays

max_slot_wal_keep_size = '100GB'
-- and alert on inactive slots

hot_standby_feedback = on  # everywhere, by default

-- enable it only on replicas serving long queries, and watch primary bloat

PitfallsCommon mistakes

• Leaving a replication slot for a decommissioned standby. The primary keeps all WAL until the disk is full.
• Assuming synchronous_commit = on with a synchronous standby means reads on that standby see the commit. Only remote_apply guarantees that.
• Using a single synchronous standby with no alternative. When it goes down, every commit on the primary waits.

LimitsWhen not to use it

A replica is not a backup: a DROP TABLE or bad UPDATE reaches it within milliseconds. Keep PITR backups alongside replicas.

SecuritySecurity notes

Replication connections stream every change. Use a dedicated role with the REPLICATION attribute, restrict it in pg_hba.conf to standby addresses, and require TLS with certificate verification.

ChallengeTry it yourself

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• High availability and replication
• Logical replication