Curious Pandas
Explorer0 XP

Replicas, lag and why a replica is not a backup

A replica copies every change a moment later. That's why you can save your name and still see the old one - and why a replica copies a DROP TABLE just as faithfully. Get the data back with point-in-time recovery.

Runs in your browserOn your machine
18 min+40 XP for finishing

Every change a PostgreSQL server makes is first written to its write-ahead log (WAL). That log is what makes the next two things possible.

  • A replica (a standby) receives the primary’s WAL and replays it, so it holds a live copy you can read from. It spreads read load and gives you a server to fail over to.
  • An archive keeps every WAL file, alongside a periodic base backup. Together they let you rebuild the database as it was at any moment you choose. That’s point-in-time recovery (PITR).

Both are copies, but only one of them is a backup.

Visual guideTwo copies that behave very differently
Two copies that behave very differentlyThe primary takes writes and streams its WAL to a replica, which replays it a moment later. The primary also archives every WAL file. Restoring last night's base backup and replaying the archived WAL up to a chosen time rebuilds the database as it was at that moment. When someone drops a table, the replica replays the drop; the archive can stop just before it.primarytakes the writesreplicareplays WAL lateWAL archiveevery change, keptPITRrebuilt at time Tbase backupnightly, 02:00streams WALarchivesreplay to TrestoreTwo copies that behave very differentlyThe primary takes writes and streams its WAL to a replica, which replays it a moment later. The primary also archives every WAL file. Restoring last night's base backup and replaying the archived WAL up to a chosen time rebuilds the database as it was at that moment. When someone drops a table, the replica replays the drop; the archive can stop just before it.primarytakes the writesreplicareplays WAL lateWAL archiveevery change, keptPITRrebuilt at time Tbase backupnightly, 02:00streams WALarchivesreplay to Trestore
  1. The primary takes every write and records it in its WAL.
  2. It streams that WAL to a replica, which replays it a moment later. In PostgreSQL this is asynchronous by default: the primary does not wait.
  3. A DROP TABLE is just another change in the WAL. The replica replays it within a second. A copy of the mistake is still the mistake.
  4. The archive keeps every WAL file, and a base backup is taken each night.
  5. Restore the base backup, replay the archive up to a moment before the DROP, and stop. That is point-in-time recovery.

Lag you can see, and a mistake you can’t undo by copying

Primary, replica, backupreplica lag 800 ms
Reading your own write

You change your display name from Bao to Bamboo Bao, and the page reloads 200 ms later.

The dropped table

A base backup ran at 02:00 with 4,800 orders, and every change since is in the WAL archive. 312 orders came in today. At 14:05:00 someone runs DROP TABLE orders on the primary.

Reading your own writes

PostgreSQL’s documentation says it plainly: WAL “takes some time to arrive from the primary server so there will be a measurable delay between primary and standby.” Usually that’s milliseconds, which is long enough for a user who saves a change and reloads instantly. The fixes trade cost in different places:

  • Route a user’s reads to the primary briefly after they write. Cheap, and everyone else still uses replicas.
  • Make the replica read wait until it has replayed the write. The app remembers the write’s WAL position and compares it with pg_last_wal_replay_lsn() on the replica.
  • Make commits wait with synchronous_commit = remote_apply and the standby listed in synchronous_standby_names (without that, remote_apply quietly behaves like on), so the commit doesn’t return until the standby has applied it. Every write pays for the round trip.

More replicas don’t help. Each one lags just the same.

Recovering a dropped table

Promoting the replica gives you a server with no orders table, because it already replayed the drop. Last night’s backup brings back the table as it was at 02:00, without today’s orders. Only the backup plus the WAL archive, replayed with recovery_target_time set before the mistake, brings everything back. By default (recovery_target_inclusive = on) PostgreSQL stops just after the target, so the target has to fall before the DROP and after the last order you want.

Check yourself+10 XP

Someone runs DROP TABLE on the primary. You have an asynchronous replica and a nightly backup. What brings back every row?

Check yourself+10 XP

Users save a profile change and sometimes still see the old value after a reload. Reads go to a replica. Which change does NOT fix it?

Do it for real · your machine+50 XP bonus

Everything above ran in your browser. Now do it on a real cluster withthe tilt platform. You need Docker, kind and Tilt, about 15 GB of free disk and 8 GB of spare RAM. Setup guide · Can my machine run it?

Look at what tilt’s own database can and can’t recover from:

kubectl -n cnpg-system get cluster demo-pg                 # INSTANCES 1: no replica to fail over to
kubectl -n cnpg-system get cluster demo-pg -o jsonpath='{.spec.backup}'; echo   # empty: no backup configured

The empty backup isn’t an oversight. The Cluster’s own comments in tilt say WAL archiving needs an object store, which a laptop doesn’t have, and that a cluster with no backup stanza “looks configured and has no recovery path whatsoever”. CloudNativePG’s documentation agrees: “A valid WAL archive is required to perform PITR.”

Self-reported. We can't see your machine, and we don't want to. This is a bonus, never required.

Sources

Finished reading?

Missions and quizzes above earn their own XP. This marks the lesson done.