Copies of the truth

We put a standby five milliseconds away from a primary, made it synchronous, and ran one client inserting single rows. Commits per second fell from 757 to 136. Then we stopped the standby and ran one more insert with a three-second statement_timeout. It waited two minutes and eight seconds, until we cancelled it by hand, and when it returned, the row was committed anyway.

A replica, the first rung past the bigger box, is not a cache, not a backup, and not a second primary. It's a copy of the primary's pages, always a little behind. That "a little behind" is this chapter: how far, what closing the gap costs, and what happens to reads and data that fall into it. (Every demo ran on three disposable containers: a primary, a replica, and a logical subscriber.)

A replica is a server stuck in recovery

After a crash, Postgres reapplies WAL from the last checkpoint to the data pages (Write it down first). A streaming replica does that forever, with WAL that arrives over the network.

The standby reports its progress as LSNs, byte offsets in the WAL stream (LSNs are byte offsets). Replaying physical WAL makes it a byte-for-byte copy of the whole cluster: no single tables, no replica-only indexes, no mixed versions. Those are jobs for logical replication.

It takes reads and refuses anything that writes, even a temp table, with SQLSTATE 25006. In production logs, that code means a write was routed to a replica, or a failover demoted your "primary".

ERROR:  cannot execute INSERT in a read-only transaction
ERROR:  cannot execute CREATE TABLE in a read-only transaction
ERROR:  cannot execute ANALYZE during recovery
ERROR:  cannot execute pg_current_xact_id() during recovery

prove "not even a temp table" \
  has "$(err replica 'create temp table t (id int)')" "ERROR:  25006: cannot execute CREATE TABLE in a read-only transaction"

One command copies a primary into a standby

On a managed service, a replica is a button. By hand, you need wal_level of at least replica (the default), a role with the replication attribute allowed in pg_hba.conf, and then one command that copies the data directory as a standby:

pg_basebackup -h repl-primary -U replicator \
  -D /var/lib/postgresql/18/docker \
  -X stream -R -P -v \
  --slot=replica1 --create-slot \
  -d 'application_name=replica1'

-R writes the standby settings, --slot with --create-slot creates a replication slot (next section), and application_name is how the standby shows up everywhere: name every one.

primary_conninfo = 'user=replicator password=replpw ... host=''repl-primary''
                    port=5432 application_name=replica1 sslmode=prefer ...'
primary_slot_name = 'replica1'

LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/508CA38
LOG:  database system is ready to accept read-only connections
LOG:  started streaming WAL from primary at 0/6000000 on timeline 1

auto=$(compose exec -T replica bash -c 'test -f "$PGDATA/standby.signal" && echo SIGNAL; cat "$PGDATA/postgresql.auto.conf"' | tr -d '\r')
prove "-R wrote standby.signal" has "$auto" "SIGNAL"
prove "--slot with --create-slot recorded primary_slot_name" has "$auto" "primary_slot_name = 'replica1'"

Replication slots are a promise to keep WAL

A replication slot is the primary's record of how far one consumer has gotten, and the primary won't recycle WAL a slot still needs. With one, a replica can be down an hour and catch up. Without one, a replica that falls far enough behind finds its WAL recycled and has to be rebuilt.

The promise has no expiry by default. With our replica stopped, twenty seconds of pgbench left its slot holding 173 MB. A forgotten slot holds WAL until the disk fills, and a full pg_wal stops the primary. Capped with max_slot_wal_keep_size (PostgreSQL 13+), a forgotten slot is marked lost instead and the primary keeps running. A replica recloned in minutes beats a primary with a full disk.

select slot_name, active, inactive_since, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
         as retained
from pg_replication_slots;

 slot_name | active |        inactive_since         | wal_status | retained
-----------+--------+-------------------------------+------------+----------
 replica1  | f      | 2026-10-06 03:31:14.019976+00 | reserved   | 173 MB

 slot_name | active | wal_status | restart_lsn | invalidation_reason
-----------+--------+------------+-------------+---------------------
 replica1  | t      | reserved   | 0/8D6EAA48  |
 forgotten | f      | lost       |             | wal_removed

# ... create slot "forgotten", write 100 MB, cap at 64 MB, checkpoint
prove "after a checkpoint, the slot is lost: invalidation_reason wal_removed" \
  wait_for 30 primary "select wal_status = 'lost' and invalidation_reason = 'wal_removed'
                       from pg_replication_slots where slot_name = 'forgotten'"
prove "the replica's own slot is untouched: active and reserved" \
  is "$(on primary -c "select active, wal_status from pg_replication_slots where slot_name = 'replica1'")" "t|reserved"

Four LSNs show where a standby falls behind

One view on the primary tells you how far behind each standby is. pg_stat_replication has a row per connected standby, and its four positions read as a pipeline: sent_lsn (sent), write_lsn (written to the standby's OS), flush_lsn (durable on its disk), replay_lsn (applied, visible to queries). A gap between flush_lsn and replay_lsn means replay is falling behind, the common case. Byte lag is pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn), and replay_lag is the same in time.

The alert that matters most is a standby missing from the view: it isn't connected at all.

Lag follows WAL volume

Under 16 pgbench clients (about 2,070 transactions per second) the replica stayed kilobytes behind, worst sample 31 ms. One bulk update across 1.2 million rows wrote 574 MB of WAL in 9.8 seconds, and the replica fell at worst 24 MB and 0.7 seconds behind, catching up half a second after it finished. (The lab repeats it with 400,000 rows.) That's a floor on a shared laptop disk, not a forecast, but the shape transfers: lag follows WAL volume, so batch bulk work (A big load forces checkpoints and floods replicas).

     sampled  |  sent   |  flush  | replay  |   replay_lag
--------------+---------+---------+---------+-----------------
 03:18:16.194 | 0 bytes | 0 bytes | 0 bytes | 00:00:00.001356
 03:18:19.694 | 8192 kB | 16 MB   | 24 MB   | 00:00:00.687615
 03:18:26.194 | 0 bytes | 0 bytes | 0 bytes | 00:00:00.097072

# sample pg_stat_replication while "update commit_test set n = n + 1" (400,000 rows) runs
prove "during the burst, the replica fell behind" gt "$maxlag" 0
prove "after it, the lag closes: the replica replays to the primary's position" caught_up

Replay is one process

The primary generates WAL from every backend at once; a standby applies it with a single startup process, record by record, in order. Through PostgreSQL 18 there is no parallel replay. So a primary writing on dozens of cores can produce WAL faster than one core can apply it, and then replay lag grows steadily however big the replica is: more cores on the standby don't help, because only the speed of one core and the storage behind it matter. The fixes are on the primary's side: write less WAL (batch bulk work, drop unused indexes, turn on wal_compression for full-page images), or split the writes across primaries (Every write funnels into one WAL stream puts numbers on the volume). Alert on replay_lag that keeps rising under normal load: it's the first sign that one primary is outgrowing its replicas.

Asynchronous is the default, and it can lose commits

Lag isn't only a stale read. By default a commit returns once its WAL is flushed on the primary; replicas get it milliseconds later. If the primary dies inside that window and you promote a replica, commits the app saw succeed are gone (Durable here is not durable everywhere). For most apps that's fine. Write it down as an RPO (Write down RPO and RTO first). If the answer is "zero lost commits", you need synchronous replication, and its price.

Synchronous commit costs a round trip

Synchronous replication makes a commit wait until a standby named in synchronous_standby_names confirms it; synchronous_commit says how far it must have gotten. With a synchronous standby 5 ms away and one client inserting single rows:

The jump that matters is local to remote_write: five milliseconds, one round trip. In the same zone it shrinks toward a millisecond; across a continent every commit pays 60 ms or more. With 16 clients, group commit shares round trips, and on cost half the throughput instead of five sixths.

You don't have to pay it everywhere. Set it per transaction, so payments wait for the standby and page-view logging doesn't:

begin;
set local synchronous_commit = on;  -- waits for the standby's flush
insert into payments ...;
commit;

on is enough for durability. remote_apply costs a little more and buys something else: the row is already visible on the standby when the commit returns, which matters for reads after a write.

synchronous_standby_names = 'FIRST 1 (replica1, replica2)'
synchronous_standby_names = 'ANY 1 (replica1, replica2, replica3)'

# No injected delay in the lab, so it times off against local, then proves the mechanism.
prove "off is far cheaper than local: no flush at all (over 5 times faster)" gt "$(median local)" "$(awk -v x="$(median off)" 'BEGIN { print 5 * x }')"
on replica -c "select pg_wal_replay_pause()"
prove "with replay paused, a remote_apply commit waits (wait event SyncRep)" \
  is "$(on primary -c "select count(*) from pg_stat_activity
                       where wait_event = 'SyncRep' and query like '%remote_apply%'")" 1

When the synchronous standby goes away, writes stop

This is the part that bites. We stopped the synchronous standby and ran one insert with statement_timeout = '3s'. It hung in wait event SyncRep; the timeout never fired. Two minutes later, we cancelled it:

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.
INSERT 0 1
Time: 128415.478 ms (02:08.415)

Read the detail twice. The commit was written locally before the wait began, so cancelling can't undo it: the row exists, and a client that gave up on a timeout believes it failed. Every write queues like this until a synchronous standby returns.

compose stop replica
bg "$tmp/hang.txt" primary -c "set statement_timeout = '3s'" -c "insert into accounts (name) values ('Waits for the standby')"
sleep 6
prove "the insert hangs past its 3 s statement_timeout, waiting on SyncRep" \
  is "$(on primary -c "select wait_event_type || ' ' || wait_event, now() - query_start > interval '3 seconds'
                       from pg_stat_activity where wait_event = 'SyncRep'")" "IPC SyncRep|t"
on primary -c "select pg_cancel_backend(pid) from pg_stat_activity where wait_event = 'SyncRep'"
prove "the row exists" is "$(on primary -c "select count(*) from accounts where name = 'Waits for the standby'")" 1

Read replicas scale reads, not writes

Every replica replays every write, so replicas add no write capacity, and they inherit the primary's bloat, missing indexes, and slow queries. A query that reads 35,000 pages reads 35,000 pages on the replica too. Fix it first (Scale up before you scale out).

When reads really are the bottleneck, replicas serve the ones that tolerate being a little behind: dashboards, search, exports, reports. Route them in the app, two pools and an explicit choice per call site. A proxy that guesses gets fooled by a select that calls a function that writes.

After a write, a replica can show the old value

The classic replica bug: a user changes their name, the redirect reads from a replica that hasn't replayed the update, and they see their old name. They change it again. Support gets a ticket. (We reproduced it by pausing replay: the replica had received the update but not replayed it.) The fixes, simplest first:

1. Stick to the primary after a write, for a few seconds. Enough for most apps.
2. Route by need: anything that renders the user's own action reads from the primary.
3. Wait for the LSN: after the write commits, read pg_current_wal_lsn() in a separate statement, carry it, and read from a replica once its pg_last_wal_replay_lsn() has passed it, else fall back. Take it after the commit, not inside the transaction (returning pg_current_wal_lsn() is too early): the commit record comes later in the WAL, and a replica past the earlier LSN can still hide the row.
4. remote_apply, per transaction, for the few writes worth its price.

 pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_is_wal_replay_paused
-------------------------+------------------------+-------------------------
 0/9FAFAB90              | 0/9FAF37E8             | t

async function readAfter(lsn: string, sql: string, params: unknown[]) {
  for (let i = 0; i < 5; i++) {
    const { rows } = await replica.query(
      "select pg_last_wal_replay_lsn() >= $1::pg_lsn as ok", [lsn]);
    if (rows[0].ok) return replica.query(sql, params);
    await new Promise((r) => setTimeout(r, 20));
  }
  return primary.query(sql, params); // replica too far behind: don't wait forever
}

prove "with replay paused, the replica still shows the old name" is "$(on replica -c 'select name from users where id = 42')" "User 42"
prove "it has received the write's LSN but not replayed it" \
  is "$(on replica -c "select pg_last_wal_receive_lsn() >= '$lsn', pg_last_wal_replay_lsn() >= '$lsn' as caught_up, pg_is_wal_replay_paused()")" "t|f|t"

Replica queries fight replay

A replica query holds a snapshot, and replay sometimes has to change pages out from under it. The common case: vacuum on the primary removes row versions no primary query needs, but a replica query still does. The primary has no idea that query exists. So replay waits, up to max_standby_streaming_delay (30 seconds by default), lag grows, and then the replica cancels the query:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Each way out moves the cost. Let replay wait longer, and lag grows. Turn on hot_standby_feedback, and vacuum on the primary spares the replica's rows: no cancellations, but bloat, as if a long transaction ran on the primary (Long transactions hold everything back). Or retry (SQLSTATE 40001).

So separate the workloads: app-read replicas with feedback off and retries, a reporting replica with feedback on and a statement_timeout. Never use a replica that can fall minutes behind as your failover candidate.

tuples: 0 removed, 1133508 remain, 1133508 are dead but not yet removable
removable cutoff: 2492494, which was 1 XIDs old when operation ended

out=$(cat "$tmp/conflict.txt")
prove "the replica cancels the query: 40001 canceling statement due to conflict with recovery" \
  has "$out" "ERROR:  40001: canceling statement due to conflict with recovery"
prove "vacuum on the primary removes nothing: the dead rows are not yet removable" \
  bash -c "grep -Eq 'tuples: 0 removed, [0-9]+ remain, [1-9][0-9]* are dead but not yet removable' <<<'$vac'"

Promotion takes one call and starts a new timeline

When the primary dies, you promote a replica with select pg_promote(). Ours took writes 200 ms later, on a new timeline, a branch of history forked at that point. (Its first id was 1035 where the old primary stopped at 1004: sequences log values ahead. If your app treats ids as dense, it was already wrong.)

The old primary doesn't know

Promotion is the easy half. Our old primary was still running, because nothing told it to stop, and it took an insert. Two servers accepting writes, each with rows the other lacks: split brain, and nothing merges it back. Any client still pointed at the old primary writes into a branch about to be discarded.

prove "the new primary writes on timeline 2 (WAL file names start 00000002)" \
  is "$(on replica -c "select left(pg_walfile_name(pg_current_wal_lsn()), 8)")" "00000002"
prove "split brain: the old primary doesn't know, and still accepts writes" \
  is "$(on primary -c "select pg_is_in_recovery()" -c "insert into accounts (name) values ('Written to the OLD primary') returning 'ok'" | tr '\n' ' ')" "f ok "

LOG:  new timeline 2 forked off current database system timeline 1
      before current recovery point 0/9FB37B20

pg_rewind: error: could not open file ".../pg_wal/00000001000000000000009E":
No such file or directory

Don't hand-roll failover

A script that calls pg_promote() when a health check fails makes split brain on purpose. A failover must decide the primary is really dead (a slow one looks dead), fence it so it can't take writes, pick the replica with the most WAL, move clients, and rejoin the old primary.

Patroni is the most widely used open-source tool for this: nodes compete for a leader lock with a time-to-live in a consensus store, and a primary that can't renew it demotes itself. On Kubernetes, operators such as CloudNativePG do the job; on a managed service, it's what you pay for. Either way, fail over on purpose, on a schedule: an unexercised failover is the untested backup of availability.

Logical replication copies rows, not pages

Streaming replication copies pages. Logical replication copies row changes: the publisher decodes its WAL into "insert this row, update that one", and a subscriber applies them to ordinary tables. The subscriber is a normal, writable database with its own indexes and tables, even on a different major version. It needs wal_level = logical on the publisher (changing it takes a restart, so plan it), a publication, and a subscription:

-- on the publisher
create publication app_pub for table accounts, users, projects;
 
-- on the subscriber, after copying the schema with pg_dump --schema-only
create subscription app_sub
  connection 'host=repl-primary dbname=app user=replicator password=replpw'
  publication app_pub;

It creates a logical slot on the publisher, copies the existing rows, and streams; ours applied changes within a second. Everything about slots applies: a stalled subscriber holds WAL on the publisher.

Logical replication skips DDL and sequences

Logical replication leaves things behind: DDL, sequence state (through PostgreSQL 18), large objects, and views. Two of those bite:

• DDL. Add a column on the publisher and use it, and the subscription stops (missing replicated column: "region") while its slot holds WAL. Additive DDL goes to the subscriber first.
• Sequences. The subscriber's never move, so its first insert collided with id 1. Before an upgrade cutover, setval every sequence. (PostgreSQL 19, in beta as of October 2026, lets a publication include sequences with for all sequences and syncs them on request.)

And give every table a primary key: the subscriber finds rows by replica identity, the primary key by default, and a keyless table in a publication makes every update to it fail on the publisher, in your app's write path.

ERROR:  cannot update table "feature_flags" because it does not have a replica
        identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

create publication big_accounts
  for table accounts (id, name, plan) where (plan = 'enterprise')
  with (publish = 'insert');

prove "sequences aren't replicated: an insert on the subscriber collides with id 1 (23505)" \
  has "$(err subscriber "insert into accounts (name) values ('Written on the subscriber')")" "ERROR:  23505: duplicate key value violates unique constraint \"accounts_pkey\""
out=$(err primary "update feature_flags set enabled = true where account_id = 1")
prove "on the publisher, updating a published table without a replica identity fails (55000)" \
  has "$out" 'ERROR:  55000: cannot update table "feature_flags" because it does not have a replica identity and publishes updates'

on primary -c "create publication big_accounts for table accounts (id, name, plan) where (plan = 'enterprise')"
prove "a row filter on a non-identity column, in a publication that publishes updates, makes updates fail" \
  has "$(err primary "update accounts set name = name where id = 3")" 'cannot update table "accounts"'

Logical replication is for upgrades, moves, and feeds

Reach for it when the copy has to differ from the source: a major version upgrade with seconds of downtime (the cutover), a few tables synced elsewhere, a move between providers, or a feed for change-data-capture, search, and cache consumers.

One catch: logical slots live on the server that made them, so after our promotion the subscriber still followed the old primary. PostgreSQL 17's failover slots fix that (failover = true, sync_replication_slots = on); before 17, resync after every failover.

select slot_name, lsn
from pg_create_logical_replication_slot('standby_decoding', 'test_decoding');
 
select lsn, xid, data
from pg_logical_slot_get_changes('standby_decoding', null, null);

 0/9FB377D8 | 2492514 | BEGIN 2492514
 0/9FB377D8 | 2492514 | table public.accounts: UPDATE: id[bigint]:7 ...
 0/9FB37880 | 2492514 | COMMIT 2492514

Streaming for failover, logical for a job

Most production setups end up with both: streaming standbys for failover, run by a tool that knows how to promote them, and logical replication brought in for a specific job, often temporarily.

The short version

• A streaming replica is an identical, read-only copy of the whole cluster, always a little behind.
• Cap every slot with max_slot_wal_keep_size and alert on inactive ones.
• Synchronous commit costs a round trip per commit (1.3 ms became 7.4 ms with a standby 5 ms away). Set it per transaction; run two candidates.
• Route reads per call site; for read-your-writes, pin to the primary or wait for the write's LSN.
• Long replica queries get cancelled, delay replay, or bloat the primary. Keep failover candidates apart from reporting replicas.
• Never hand-roll failover, and practice it on a schedule.
• Logical replication copies rows, not DDL or sequences: additive DDL to the subscriber first, a primary key on every table.

Further reading

• Log-shipping standby servers, PostgreSQL documentation. Streaming replication, slots, cascading, and synchronous replication with FIRST and ANY, from the source.
• Hot standby, PostgreSQL documentation. What runs on a standby, every kind of replay conflict, and how each setting changes them.
• Replication settings, PostgreSQL documentation. Defaults and exact semantics for every setting in this chapter, including slot limits and failover slot synchronization.
• Logical replication, PostgreSQL documentation. Publications, subscriptions, row filters, conflicts, failover, and upgrades.
• pg_rewind, PostgreSQL documentation. Its requirements, and what to do when the WAL it needs is gone.
• Patroni, GitHub. The most widely used open-source failover manager; its docs explain leader locks, fencing, and synchronous mode.