Replication¶

What replication is for¶

Replication keeps copies of the same data on several servers. It serves three different goals, and which goal matters decides how it should be configured:

The measurements on this page come from a real PostgreSQL 16.2 primary and streaming replica (set up with pg_basebackup) running on one Apple Silicon Mac, with the primary flushing its WAL to stable storage (F_FULLFSYNC).

Three ways to replicate¶

Physical replication produces an exact byte-for-byte copy: same major version, same architecture, whole cluster only, and the replica is read-only — measured: INSERT on the replica failed with 25006 cannot execute INSERT in a read-only transaction. Logical replication can replicate selected tables, between different versions (enabling near-zero-downtime upgrades), and into a replica that also accepts its own writes.

How PostgreSQL streaming replication works¶

1. The primary writes WAL as usual. A walsender process streams new WAL records to each connected replica.
2. The replica's walreceiver writes them to its own WAL files and flushes them.
3. The replica's startup process replays the records into its data pages — the same code path as crash recovery, running forever.
4. The replica reports back how far it has written, flushed, and applied (as WAL positions, LSNs). pg_stat_replication on the primary shows them, plus write_lag, flush_lag, replay_lag.

A replication slot (created here with pg_basebackup -C -S replica1) makes the primary keep WAL until the replica has received it, so a disconnected replica can catch up. The danger: an abandoned slot makes the primary retain WAL forever, filling its disk — set max_slot_wal_keep_size and monitor slots.

Asynchronous vs. synchronous¶

Asynchronous (the default)¶

The primary acknowledges a commit as soon as its own WAL is flushed. Replicas follow behind by the replication lag.

Measured:

• Read-your-writes failed 200 out of 200 times. Each iteration committed a row on the primary and immediately queried the replica for it; the replica, on the same machine, was a few milliseconds behind every time.
• Lag under load: during a bulk insert of 2 million rows, the replica fell up to 46 MB of WAL behind the primary (replay was slower than generation), then caught up after the load ended.

On failover, everything in the lag window is lost: transactions the client was told were committed never reached the promoted replica. The amount of data that can be lost is the RPO (recovery point objective); for async replication, RPO > 0 and grows with lag.

Synchronous¶

The primary waits for one or more named standbys (synchronous_standby_names) before acknowledging. synchronous_commit selects how far the standby must get:

Here the extra cost was under a millisecond, because both servers ran on one machine (network round trip ≈ 0) and the standby used macOS's default non-flushing sync method; the primary's own ~9 ms fsync dominated. In a real deployment, synchronous replication adds one network round trip plus the standby's flush to every commit: ~0.5–2 ms within a data center, tens of milliseconds across regions.

The availability trap: a synchronous standby that disappears¶

Measured: with the synchronous standby stopped, a single-row INSERT on the primary hung. statement_timeout = '3s' did not end it — the wait for synchronous replication happens at commit, after the statement itself has finished. It waited ~10 minutes until cancelled with pg_cancel_backend, and then:

WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not
         have been replicated to the standby.

The row was visible on the primary afterward. Two lessons:

1. With one synchronous standby, the primary's write availability depends on the standby's. Production setups use quorum commit — synchronous_standby_names = 'ANY 1 (r1, r2)' waits for any one of two standbys — so one standby failing doesn't stop writes.
2. A commit that was cancelled or timed out on the client side may still be committed. The client cannot know; operations must be idempotent or verifiable after reconnecting.

Replication lag and its consequences¶

Lag comes from: network latency, the replica's disk or CPU being slower than the primary's, single-threaded WAL replay (PostgreSQL replays with one process), long-running queries on the replica that conflict with replay, and bursts like bulk loads (measured above).

Anomalies seen by applications reading from async replicas:

Query conflicts on hot standbys¶

The replica replays changes that may remove row versions a long query on the replica still needs (VACUUM on the primary cleaned them up). PostgreSQL then waits up to max_standby_streaming_delay (30 s), and cancels the query: canceling statement due to conflict with recovery. hot_standby_feedback = on makes the replica tell the primary which snapshots it still needs, preventing the conflict — at the price of replica queries holding back VACUUM on the primary (see MVCC in PostgreSQL).

Failover¶

When the primary fails, a replica is promoted (pg_ctl promote / pg_promote()): it finishes replaying received WAL and starts accepting writes. The hard parts are around it:

• Detecting failure without false positives (a network blip must not trigger failover). Tools: Patroni (with etcd/Consul for consensus), repmgr, pg_auto_failover, cloud-managed failover.
• Avoiding split brain: if the old primary is only partitioned, not dead, it may keep accepting writes. Failover managers fence it (shut it down, revoke its leader lease) before promoting. This is a consensus problem — see Raft Consensus.
• Redirecting clients: DNS, a virtual IP, a proxy, or multi-host connection strings (target_session_attrs=read-write).
• Rejoining the old primary: it may have WAL the new primary never got (the async lag window); pg_rewind rewinds it to the divergence point so it can follow the new primary.

Topologies¶

Monitoring¶

-- On the primary: one row per connected standby
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Replication slots: is any retaining a dangerous amount of WAL?
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- On a standby: time since the last replayed transaction
SELECT now() - pg_last_xact_replay_timestamp();

Alert on bytes behind, replay lag, inactive slots retaining WAL, and a standby not connected.

Real-world usage: in a typical web app with 90% reads and 10% writes, adding 2 replicas roughly triples read capacity: the primary handles writes and the read share it keeps, each replica takes a share of the reads. It does nothing for write throughput — that requires sharding.

Common pitfall¶

Routing all reads to replicas, including the read that immediately follows a user's own write. Measured above, a replica on the same machine missed 200 out of 200 such reads; across a network and under load, it is no better. The result is an intermittent "my changes didn't save" bug that is hard to reproduce on an idle staging system. Route reads that follow a write to the primary (or wait for the replica to reach the write's LSN), and reserve replicas for reads that tolerate staleness.