# Postgres: replication lag ## Concept A streaming replica is a second Postgres that replays the primary's write-ahead log as it arrives. It is never *quite* the same database as the primary — there is always some amount of WAL in flight between them, and during that window the replica will happily answer a query with an older answer. This exercise makes that window visible and measurable: you'll write a million rows to the primary and watch the replica report a count of `1`, watch the byte distance between the two nodes climb to 72 MB and drain back to zero, freeze the replica mid-stream so the lag stops being a blur, and then flip replication to *synchronous* and watch a `COMMIT` on the primary block until the replica says it's caught up — and then hang forever when the replica dies. The headline surprise, if you only take one thing: the replica does not gradually count up to a million. It sits at the old number and then jumps. ## Mental model: four LSNs, four different promises An LSN (Log Sequence Number) is a byte offset into the WAL stream, written as `0/46BEED00`. Every change to the database appends to that stream, so "how far behind is the replica" is literally a subtraction of two byte offsets — which is what `pg_wal_lsn_diff()` does. The important thing is that a WAL record makes *four* separate journeys, and `pg_stat_replication` on the primary has a column for each. They are not the same event and they do not carry the same guarantee: | Column | Reached when… | What is actually guaranteed at this point | | --- | --- | --- | | `sent_lsn` | the primary's walsender has pushed the bytes onto the network | Nothing about the replica at all. This is the primary's opinion. | | `write_lsn` | the replica's walreceiver has `write()`n them to the OS | Survives the replica's `postgres` process crashing. Does **not** survive the replica's machine losing power — the bytes are in the OS page cache. | | `flush_lsn` | the replica has `fsync()`ed them to disk | Survives the replica's machine dying. The data is durable on two nodes. **But it is not yet queryable on the replica** — this is the distinction Part 3 is built around. | | `replay_lsn` | the replica's startup process has *applied* the records | The change is now visible to `SELECT`s on the replica. | Alongside those, `write_lag` / `flush_lag` / `replay_lag` report the same three distances as *time* rather than bytes — how long it took the most recent locally-flushed WAL to reach each stage. Useful, but Part 3 shows a case where `replay_lag` is actively misleading. The replica has its own view, from the other side: `pg_last_wal_replay_lsn()` (same number as the primary's `replay_lsn`) and `pg_last_xact_replay_timestamp()` (the commit *time* of the last transaction it replayed — subtract it from `now()` for an honest staleness figure). ### Where the commit is allowed to return By default, replication is **asynchronous**: the primary commits, returns to the client, and the WAL makes its way over whenever it makes its way over. Setting `synchronous_standby_names` makes at least one standby *synchronous*, and then `synchronous_commit` decides which of the four journeys above a `COMMIT` waits for: | `synchronous_commit` | Commit returns once… | Reach for it when… | | --- | --- | --- | | `off` | the WAL is in the primary's own buffer — not even flushed locally | You can tolerate losing the last ~0.2s of committed transactions on a primary crash. Bulk loads, analytics staging, click-tracking. Note this risks data loss with *no* replica involved. | | `local` | the primary has fsynced locally. Standbys ignored. | Per-transaction escape hatch: you have sync replication on globally, but this one bulk job shouldn't pay for it. `SET synchronous_commit = local;` inside that session. | | `remote_write` | the standby's `write_lsn` has passed the commit | You want to survive the *standby's* process dying, but not a full standby power loss, and you don't want to pay for its fsync. An unusual middle ground. | | `on` *(default)* | the standby's `flush_lsn` has passed the commit | The normal choice when you want zero data loss on primary failure. The transaction is durable on two machines before the client hears "ok". | | `remote_apply` | the standby's `replay_lsn` has passed the commit | You are load-balancing reads onto the replica and cannot tolerate read-your-writes violations. The most expensive setting — you pay the replay time on every commit. | The gap between `on` and `remote_apply` is the whole point: with `on`, your committed transaction is *durable* on the replica but not yet *visible* there. Part 4 demonstrates exactly that, by making one setting block and the other not, with nothing else changed. ## Setup Two Postgres containers on a private Docker network, so they can find each other by name. This is the one part of the exercise worth scripting — get the cluster up, then type everything after it by hand. ```bash docker network create pgnet ``` **1. The primary.** ```bash docker run -d --name pg-primary --network pgnet \ -e POSTGRES_PASSWORD=postgres postgres:16 ``` Wait a few seconds for it to initialize. The defaults are already replication-ready — `wal_level = replica` and `max_wal_senders = 10` — so there is nothing to tune. **2. A replication role, and permission to use it.** ```bash docker exec -i pg-primary psql -U postgres \ -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replpass';" docker exec -i pg-primary bash -c \ "echo 'host replication replicator all scram-sha-256' >> /var/lib/postgresql/data/pg_hba.conf" docker exec -i pg-primary psql -U postgres -c "SELECT pg_reload_conf();" ``` The `pg_hba.conf` line is required and easy to forget: `replication` is a *pseudo-database* in `pg_hba.conf`, and the stock file only allows it from `localhost`. A rule granting `all` databases does not cover it. **3. Take a base backup into a named volume.** ```bash docker volume create pg-replica-data docker run --rm --network pgnet \ -e PGPASSWORD=replpass -v pg-replica-data:/pgdata \ --entrypoint bash postgres:16 -c \ "install -d -o postgres -g postgres -m 0700 /pgdata/pgdata && \ gosu postgres pg_basebackup -h pg-primary -U replicator -D /pgdata/pgdata -Fp -Xs -R -P" ``` ``` waiting for checkpoint 23182/23182 kB (100%), 0/1 tablespace 23182/23182 kB (100%), 1/1 tablespace ``` `pg_basebackup` is a physical, byte-for-byte copy of the primary's data directory. `-Xs` streams WAL concurrently with the copy so the backup is self-consistent, and `-R` is the flag that matters here: it writes `standby.signal` and a `primary_conninfo` line into the new data directory, which is what turns a copy into a standby. The `install -d … -m 0700` dance is not incidental. A fresh Docker volume is owned by root and mode 0755; Postgres refuses to start on a data directory it doesn't own with exactly `0700` (or `0750`). Creating the directory with the right owner and mode up front avoids a `data directory has invalid permissions` failure at step 4. **4. Start the standby.** ```bash docker run -d --name pg-replica --network pgnet \ -v pg-replica-data:/var/lib/postgresql/data \ -e PGDATA=/var/lib/postgresql/data/pgdata \ postgres:16 ``` The official entrypoint sees an already-initialized `PGDATA` and skips `initdb`, so it just starts Postgres — which finds `standby.signal` and comes up in recovery. Using a named volume (rather than baking the backup into the container's own command) matters for Part 4, where you stop and start this container and it needs to survive that. Check the log: ```bash docker logs pg-replica ``` ``` LOG: entering standby mode LOG: consistent recovery state reached at 0/3000100 LOG: database system is ready to accept read-only connections LOG: started streaming WAL from primary at 0/4000000 on timeline 1 ``` That last line is the one you want. Now open **two terminals**: Terminal **P** (the primary): ```bash docker exec -it pg-primary psql -U postgres ``` Terminal **R** (the replica): ```bash docker exec -it pg-replica psql -U postgres ``` ## Steps ### Part 1 — what a replica is What this part establishes: a standby is a *whole running Postgres* that happens to be permanently in recovery. That single fact explains both what it can do (serve reads) and what it can't (accept any write at all). 1. **R**: `SELECT pg_is_in_recovery();` ``` pg_is_in_recovery ------------------- t ``` **P**: the same query returns `f`. This boolean is the cheapest way to ask a connection "am I talking to the primary or a replica?", and it's what most connection poolers use to route writes. 2. **P**: look at the link from the primary's side. ```sql SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_state FROM pg_stat_replication; ``` ``` -[ RECORD 1 ]----+------------ application_name | walreceiver client_addr | 172.20.0.3 state | streaming sent_lsn | 0/303BAD0 write_lsn | 0/303BAD0 flush_lsn | 0/303BAD0 replay_lsn | 0/303BAD0 write_lag | flush_lag | replay_lag | sync_state | async ``` (`\x` for expanded display makes this readable.) All four LSNs are identical because the system is idle — there is no WAL in flight for them to disagree about. The three lag columns are *null*, not zero: nothing has been measured yet. And `sync_state | async` is the default — the primary is not waiting for this replica for anything. Note this view lives on the **primary**. Run it on the replica and you get zero rows, which confuses everyone once. 3. **P**: create something to replicate. ```sql CREATE TABLE events (id bigserial PRIMARY KEY, payload text, created_at timestamptz DEFAULT clock_timestamp()); INSERT INTO events (payload) VALUES ('hello from the primary'); ``` **R**: `SELECT id, payload FROM events;` ``` id | payload ----+------------------------ 1 | hello from the primary ``` The DDL replicated too. Physical replication ships WAL, and `CREATE TABLE` is WAL — there is no schema/data distinction at this level. 4. **Predict**: the replica is a fully functional Postgres with the whole table in it. What happens if you write to it? 5. **R**: ```sql INSERT INTO events (payload) VALUES ('hello from the replica'); ``` 6. **Observe**: ``` ERROR: cannot execute INSERT in a read-only transaction ``` And `CREATE TABLE t (x int);` gives `ERROR: cannot execute CREATE TABLE in a read-only transaction`. This isn't a permissions check on the `postgres` role — it's structural. The replica's WAL position is a single linear pointer into the primary's stream; a local write would fork history and there would be no way to continue replaying. So every write path is refused, superuser or not. 7. **R**: the replica's own view of where it is. ```sql SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(); ``` ``` pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp ------------------------+------------------------------- 0/30655B8 | 2026-07-22 15:19:32.947974+00 ``` These are the two functions you actually use in production monitoring, because they work from the replica — where your read traffic is — rather than requiring a connection to the primary. ### Part 2 — the burst What this part tests: how big the in-flight window really is. Per the mental model, `sent_lsn - replay_lsn` is a byte distance, so a large enough write should make it large enough to see. 8. **Predict**: you're about to insert a million rows on the primary and then immediately count on the replica. The replica currently sees 1 row. What count comes back — something part-way, or one of the endpoints? 9. **P**: ```sql INSERT INTO events (payload) SELECT 'burst-'||g FROM generate_series(1,1000000) g; ``` **R** (immediately): `SELECT count(*) FROM events;` 10. **Observe**: ``` count ------- 1 ``` One. Not 400,000, not 999,000 — the same number as before the burst. **P**, meanwhile: ```sql SELECT pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind FROM pg_stat_replication; ``` ``` behind -------- 63 MB ``` 63 MB of WAL exists that the replica has not applied. Wait a few seconds, re-run the count on **R**, and it is `1000001`. 11. Now watch the drain happen rather than sampling it. In **P**, leave this running (`\watch` re-runs a query on an interval): ```sql SELECT clock_timestamp()::time(3) AS t, sent_lsn, replay_lsn, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS behind, replay_lag FROM pg_stat_replication \watch 0.5 ``` Then in a *third* shell (or just re-use **R**'s terminal with a `docker exec` to the primary), fire a bigger burst: ```sql INSERT INTO events (payload) SELECT 'burst2-'||g FROM generate_series(1,3000000) g; ``` 12. **Observe** (the repeated `\watch` header block between iterations is trimmed here; each line is one iteration): ``` 15:19:49.169 | 0/A5A4000 | 0/5D332D8 | 72 MB | 00:00:00.247231 15:19:49.67 | 0/C6CC778 | 0/C6CC778 | 0 bytes | 00:00:00.724977 15:19:50.175 | 0/C6CC778 | 0/C6CC778 | 0 bytes | 00:00:00.724977 15:19:50.669 | 0/C6CC778 | 0/C6CC778 | 0 bytes | 00:00:00.724977 15:19:51.169 | 0/E580000 | 0/D3FFFC8 | 18 MB | 00:00:00.226341 15:19:51.67 | 0/11000000 | 0/10FFFFD0 | 48 bytes | 00:00:00.235517 15:19:52.169 | 0/14100000 | 0/133FFFF8 | 13 MB | 00:00:00.090702 15:19:52.669 | 0/173FC000 | 0/1641EC70 | 16 MB | 00:00:00.171189 15:19:53.169 | 0/1A000000 | 0/19FFFFF8 | 8 bytes | 00:00:00.14963 15:19:53.669 | 0/1CEE0000 | 0/1BFFFFF0 | 15 MB | 00:00:00.249475 15:19:54.169 | 0/1F800000 | 0/1E919660 | 15 MB | 00:00:00.0441 15:19:54.673 | 0/22000000 | 0/21FFFFD0 | 48 bytes | 00:00:00.135897 15:19:55.17 | 0/25400000 | 0/244595C8 | 16 MB | 00:00:00.230483 15:19:55.669 | 0/27000000 | 0/26FFFFF8 | 8 bytes | 00:00:00.306014 15:19:56.174 | 0/28A7CAE0 | 0/28A7CAE0 | 0 bytes | 00:00:00.117995 15:19:56.675 | 0/28A7CAE0 | 0/28A7CAE0 | 0 bytes | 00:00:00.117995 ``` Both LSNs march forward together, with the replica sawtoothing between roughly 16 MB behind and a handful of *bytes* behind, then settling at `0 bytes` the moment the burst ends. Steady state on an idle link is exact equality, not "close enough". The sawtooth is the shape to notice: replay isn't a smooth trickle, it proceeds in bursts as WAL segments arrive and get applied. And look at `replay_lag` — it stays around 0.1–0.3 s the entire time, even in the rows where the replica is 16 MB behind. Bytes-behind and time-behind are genuinely different measurements: the replica is far behind in *volume* while staying close behind in *wall-clock*, because it is applying continuously and just can't keep up with the rate. 13. **Predict**: so during those seconds when the replica is 16 MB behind, a `count(*)` on it should return something part-way through the burst. Should it not? 14. **R**: watch the count itself, then burst again from **P**. ```sql SELECT clock_timestamp()::time(3) AS t, count(*) FROM events \watch 0.3 ``` ```sql INSERT INTO events (payload) SELECT 'atomic-'||g FROM generate_series(1,3000000) g; ``` 15. **Observe**: ``` 15:20:25.302 | 4000001 15:20:26.125 | 4000001 15:20:27.028 | 4000001 15:20:28.22 | 4000001 15:20:29.146 | 4000001 15:20:30.031 | 4000001 15:20:30.95 | 4000001 15:20:31.54 | 4000001 15:20:32.023 | 7000001 15:20:32.321 | 7000001 15:20:32.618 | 7000001 ``` Six and a half seconds at `4000001`, and then a single step to `7000001`. Never `5200000`. Never anything in between. This is the most important thing in the exercise, and it's the thing the phrase "replication lag" tends to hide. The replica *was* receiving and applying those three million row-insert WAL records the whole time — you just watched `replay_lsn` climb through them in step 12. But row versions only become visible when the transaction that created them is known to have committed, and the commit record is a single WAL record at the very end. Replaying it flips all three million rows into visibility at once. MVCC on the replica is the same MVCC as on the primary, so a replica is never "half a transaction" behind — it is always a consistent snapshot of some past moment of the primary. Which is a much better guarantee than "it's a bit behind", and a much worse one for your latency graph: a single huge transaction means the replica is stale by *its entire duration*, no matter how fast your network is. ### Part 3 — freeze the replica What this part tests: the distinction between `flush_lsn` and `replay_lsn` from the mental model — durable versus visible. Both drain to zero instantly on a healthy local link, so the only way to look at them properly is to stop one of them. `pg_wal_replay_pause()` does exactly that: the walreceiver keeps receiving and flushing, but the startup process stops applying. 16. **R**: note where you are, then stop time. ```sql SELECT count(*) FROM events; SELECT pg_wal_replay_pause(); SELECT pg_get_wal_replay_pause_state(); ``` ``` count --------- 7000001 pg_get_wal_replay_pause_state ------------------------------- paused ``` 17. **P**: write, with the replica frozen. ```sql INSERT INTO events (payload) SELECT 'paused-'||g FROM generate_series(1,200000) g; ``` 18. **Predict**: the replica is not applying WAL. Is it also not *receiving* it? What do you expect `flush_lsn` and `replay_lsn` to say? 19. **P**: ```sql SELECT sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn)) AS flush_behind, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_behind FROM pg_stat_replication; ``` 20. **Observe**: ``` -[ RECORD 1 ]----+---------------- sent_lsn | 0/46BEED00 write_lsn | 0/46BEED00 flush_lsn | 0/46BEED00 replay_lsn | 0/44DD9A60 write_lag | 00:00:00.011367 flush_lag | 00:00:00.01626 replay_lag | 00:00:00.102855 flush_behind | 0 bytes replay_behind | 30 MB ``` There it is: the four LSNs split into two groups. `sent`, `write` and `flush` are all at `0/46BEED00` — every byte the primary produced is already `fsync()`ed onto the replica's disk, `0 bytes` behind. And `replay_lsn` is stuck at `0/44DD9A60`, exactly where it was when you paused, 30 MB back. Read that as a sentence: **those 200,000 rows are durable on two machines and queryable on neither but one.** If the primary's disk caught fire right now, nothing is lost. If you query the replica right now, they don't exist. Durability and visibility are separate properties, and this is the row of the mental-model table that most people collapse into one. 21. **R**: confirm from the other side. ```sql SELECT count(*) FROM events; SELECT pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS staleness; ``` ``` count --------- 7000001 pg_last_wal_replay_lsn | staleness ------------------------+---------------- 0/44DD9A60 | 00:00:16.91876 ``` Still `7000001`. The replica is serving reads perfectly happily — it is a completely healthy database, just one that is now almost 17 seconds in the past. Now compare that `00:00:16.9` with `replay_lag | 00:00:00.102855` from step 20. **The primary's `replay_lag` is lying.** It is defined as how long the *most recently applied* WAL took to be applied — and since nothing has been applied since the pause, it is frozen at whatever it last measured. A replica stalled for an hour can show a `replay_lag` of 100 ms. If you take one operational lesson from this exercise, take this one: alert on `now() - pg_last_xact_replay_timestamp()` measured on the replica, or on `pg_wal_lsn_diff` bytes, not on `replay_lag`. 22. **Predict**: you're about to resume. Does the replica have to re-fetch those 30 MB? 23. **R**: `SELECT pg_wal_replay_resume();` then `SELECT count(*) FROM events;` 24. **Observe**: ``` count --------- 7200001 ``` Immediate — the WAL was already on local disk (that's what `flush_lsn` was telling you), so catching up was pure CPU, no network. Back on **P**, `replay_behind` is `4864 bytes` and falling to `0`. ### Part 4 — make it synchronous What this part tests: the `synchronous_commit` table from the mental model. So far every commit on the primary has returned without the replica being consulted at all — that's what `sync_state | async` meant in step 2. Now make the primary wait, and find out precisely *what* it waits for. 25. **P**: promote the standby to synchronous. ```sql ALTER SYSTEM SET synchronous_standby_names = '*'; SELECT pg_reload_conf(); SELECT application_name, sync_state, sync_priority FROM pg_stat_replication; ``` ``` application_name | sync_state | sync_priority ------------------+------------+--------------- walreceiver | sync | 1 ``` `async` → `sync`, with no restart — this is a reload-only setting. `'*'` means "any connected standby will do"; in production you name them, and the name is the standby's `application_name`. (Run these as two separate statements. `ALTER SYSTEM` cannot run inside a transaction block, so pasting both on one line into `psql -c` fails with `ERROR: ALTER SYSTEM cannot run inside a transaction block`.) 26. **R**: freeze replay again, so "has the replica applied it?" is a question with a knowable answer. ```sql SELECT pg_wal_replay_pause(); ``` 27. **Predict**: replication is now synchronous and the replica is not applying anything. Does a plain `INSERT` on the primary block? 28. **P**: ```sql INSERT INTO events (payload) VALUES ('sync-A'); ``` 29. **Observe**: it returns immediately — about 80 ms including container overhead. No blocking at all. Because `synchronous_commit` defaults to `on`, and per the mental model `on` waits for the standby's **`flush_lsn`**. The pause stopped replay, not receipt — flush is still keeping up, so the commit's condition is satisfied. "Synchronous replication" out of the box guarantees durability on two nodes, and says nothing whatsoever about whether you can read your write on the replica. 30. **Predict**: now change one setting — the last row of the table — and run the same insert. 31. **P**: ```sql SET synchronous_commit = remote_apply; INSERT INTO events (payload) VALUES ('sync-B'); ``` 32. **Observe**: nothing. The `INSERT` hangs, indefinitely, with the replica's replay paused. Same statement, same cluster, same replica — the only thing that changed is which of the four LSNs the commit is waiting on. Leave it hanging and hit **Ctrl-C**: ``` 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 ``` Read that carefully, because it is one of the sharpest edges in Postgres. You cancelled the statement and the statement *committed anyway* — `INSERT 0 1`. Cancelling a synchronous commit only abandons the *wait*; the transaction was already durable locally before the wait began. There is no way to un-commit it. So a client that times out and reports failure to its user may be reporting a transaction that is permanently in the database. Any application talking to a synchronous cluster has to treat a commit timeout as "unknown", never "failed". 33. **R**: `SELECT pg_wal_replay_resume();` — and on **R**, `SELECT payload FROM events WHERE payload LIKE 'sync-%';` returns both `sync-A` and `sync-B`, confirming the cancelled one is really there. 34. **Predict**: the failure mode. Replication is synchronous with `synchronous_standby_names = '*'` and exactly one standby. What happens to the primary if that standby goes away entirely? 35. In a shell: `docker stop pg-replica`. Then on **P**, with `synchronous_commit` back at its default `on`: ```sql INSERT INTO events (payload) VALUES ('during-outage'); ``` 36. **Observe**: it hangs. Not for a timeout period — there is no timeout. Open a second connection to the primary and look: ```sql SELECT pid, state, wait_event_type, wait_event, left(query,40) AS query FROM pg_stat_activity WHERE state = 'active' AND wait_event = 'SyncRep'; ``` ``` -[ RECORD 1 ]---+----------------------------------------- pid | 259 state | active wait_event_type | IPC wait_event | SyncRep query | INSERT INTO events (payload) VALUES ('du ``` `wait_event = SyncRep`, and `SELECT count(*) FROM pg_stat_replication;` returns `0` — there is no standby to satisfy the condition, so the condition can never be satisfied. This is a primary that is up, healthy, accepting connections, answering every read query instantly, and unable to commit a single write. It will sit like that forever. This is the classic operational trap of synchronous replication with one standby: you configured it to avoid losing data, and you converted a *replica* outage into a *primary* outage. The standard answer is to run at least two standbys with `synchronous_standby_names = 'ANY 1 (s1, s2)'`, so any one of them can be down without stalling writes. 37. **The escape hatch.** From that second connection — reads and `ALTER SYSTEM` still work fine: ```sql ALTER SYSTEM SET synchronous_standby_names = ''; SELECT pg_reload_conf(); ``` The hung `INSERT` returns the instant the reload lands (measured: 10.7 s, which was exactly how long it took to type the escape hatch). Downgrading to asynchronous is a config reload, no restart — which is the thing to have committed to memory before you ever turn synchronous replication on. 38. `docker start pg-replica`, wait a few seconds, and check **P**: ``` application_name | state | sync_state | behind ------------------+-----------+------------+--------- walreceiver | streaming | async | 0 bytes ``` It reconnected on its own and caught up, including `during-outage`. The standby re-requests the stream from its last flushed position; because the primary still had those WAL segments on disk, it simply resumed. Note the word *because* — see "Go deeper" for what happens when it doesn't. ## What you should see A replica that answers `SELECT pg_is_in_recovery()` with `t` and refuses every write with `cannot execute INSERT in a read-only transaction`. A million-row burst on the primary that leaves the replica reporting a count of `1` while `pg_stat_replication` shows it `63 MB` behind. A `\watch` on the primary showing `sent_lsn` and `replay_lsn` sawtoothing 16 MB apart and then locking to `0 bytes` the moment writes stop. A count on the replica that sits at `4000001` for six and a half seconds and then jumps to `7000001` in a single step, never passing through anything in between. With replay paused: `flush_behind | 0 bytes` next to `replay_behind | 30 MB`, a replica 17 seconds stale, and a primary still cheerfully reporting `replay_lag | 00:00:00.102855`. And under synchronous replication, an `INSERT` that returns in 80 ms under `synchronous_commit = on`, blocks forever under `remote_apply`, commits anyway when you Ctrl-C it, and stalls the entire primary on `wait_event = SyncRep` when the standby dies. ## Why Physical streaming replication is one linear byte stream and nothing more. The primary appends WAL; the standby reads it, writes it, flushes it, and applies it. Every property in this exercise falls out of that one design. The replica is read-only because its position in that stream is a single pointer. A local write would create WAL that does not exist on the primary, and the next record streamed in would have nowhere consistent to land. It's not a policy, it's the absence of any coherent alternative — which is why even a superuser can't override it. The count jumps rather than climbs because visibility on the replica is decided by exactly the same MVCC rules as on the primary: a row version is visible once its creating transaction is known to have committed. Replay processes the three million insert records first and the single commit record last, so three million rows become visible in one instant. That's what makes a replica *usable* — it always shows a consistent past state of the primary, never a torn one — and it's also why a long transaction on the primary pins the replica's apparent staleness to that transaction's whole duration, regardless of bandwidth. The four LSNs are four separate promises because there are genuinely four distinct moments, and different applications need different ones. Pausing replay just holds two of them still long enough to see they were never the same number. Once you accept that `flush` and `replay` are different events, `synchronous_commit = on` blocking on one and `remote_apply` blocking on the other stops being trivia and becomes the actual decision: do you need this write to be *safe*, or do you need it to be *visible over there*? The default answers "safe", and a read-your-writes bug on a read replica is what it costs. And the hung primary in step 36 is the honest price of synchronous replication. `synchronous_commit = on` is a promise that no committed transaction can be lost if the primary dies — which is only keepable if the primary refuses to commit when it has nobody to tell. There is no timeout setting because a timeout would silently break the promise at the worst possible moment. The only real fix is more standbys, so that "nobody to tell" stops being one machine away. ## Go deeper Everything below was run against this same cluster; the quoted output is real, but the steps are left for you to reproduce. - **Replication slots.** Step 38 worked only because the primary still had the needed WAL segments. `docker stop pg-replica`, then churn WAL on the primary until they're recycled — a dozen rounds of `pg_switch_wal()`, `CHECKPOINT`, and a 200k-row insert does it (the `CHECKPOINT` is the part that matters; segments are recycled at checkpoint, not on switch). Then `docker start pg-replica` and read its log: ``` FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000061 has already been removed LOG: waiting for WAL to become available at 0/61002000 ``` Note what *doesn't* happen: the replica doesn't crash. It stays up, `pg_is_in_recovery()` still returns `t`, it keeps serving reads at its frozen position, and it retries every five seconds forever. A silently permanently-stale replica is a nastier failure than a dead one. The fix is a slot: `SELECT pg_create_physical_replication_slot('replica1');` on the primary and `primary_slot_name = 'replica1'` on the replica. The primary then retains WAL for that slot no matter how long the standby is gone — which is the fix *and* the new failure mode, since a forgotten slot fills the primary's disk. Watch `pg_replication_slots.wal_status` go `reserved` → `extended` → `lost`. - **Query conflicts.** On the replica set `max_standby_streaming_delay = 2s` (the default is 30 s and you don't want to wait), then open a `BEGIN ISOLATION LEVEL REPEATABLE READ;` and hold a snapshot with a `SELECT count(*)` and a `pg_sleep(20)`. Meanwhile on the primary, `DELETE` a few million rows and `VACUUM`. The replica must choose between applying WAL that removes row versions your open snapshot still needs and stalling replay — and by default replay wins: ``` ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. ``` Set `hot_standby_feedback = on` and the conflict disappears — at the cost of the replica's snapshots now holding back vacuum *on the primary*, which connects this exercise directly to Part 4 of [MVCC and vacuum](postgres-mvcc-and-vacuum.md): a long read on your replica becomes bloat on your primary. - **A delayed standby.** Append `recovery_min_apply_delay = '1min'` to the replica's `postgresql.auto.conf` and reload. It now streams and flushes in real time but deliberately replays a minute late — a running undo button for `DELETE FROM users;` with no `WHERE`. Insert 100,000 rows on the primary and look: ``` flush_behind | 0 bytes replay_behind | 14 MB ``` with the replica reporting `count = 0` for the new rows and a staleness of `00:04:01`. That is Part 3's `flush`/`replay` split made permanent and on purpose. Delete the line and reload, and the backlog applies in seconds. - **Promotion.** `SELECT pg_promote();` on the replica. `pg_is_in_recovery()` flips to `f`, `INSERT` starts working, and the log says: ``` LOG: received promote request LOG: selected new timeline ID: 2 ``` `SELECT timeline_id FROM pg_control_checkpoint();` now returns `2` on the replica and `1` on the old primary. They have forked history, and the old primary cannot stream from the new one without `pg_rewind`. This is what a failover actually is — and why "just point the app at the replica" is never the whole story. - **`remote_write` vs `on`.** Repeat step 31 with `SET synchronous_commit = remote_write;` while replay is paused. It returns immediately, like `on` — both are satisfied by the receive path. Then compare their latency under a tight loop of small commits to see what the replica's `fsync` actually costs you per transaction. ## Cleanup ```bash docker rm -f pg-primary pg-replica docker volume rm pg-replica-data docker network rm pgnet ```