PostgreSQL Replication Readiness Review

How to use this review

Monthly, from both ends. The primary’s view and the standby’s view answer different questions, and the primary’s view disappears at exactly the moment you most need it.

The metric that is loudest when nothing is wrong

Time since the last replayed commit is anti-correlated with real lag

now() - pg_last_xact_replay_timestamp() returns time since the last commit reached the standby. On an idle primary that grows with the wall clock regardless of replication health.

Measured on a healthy pair, three consecutive samples:

 apply_lag_time  | fully_caught_up 
-----------------+-----------------
 00:00:00.951159 | t
 00:00:09.003664 | t
 00:00:17.054691 | t

Byte lag across the same window: 0 bytes.

So the metric is loud on a quiet Sunday and quiet during a bulk load. In one estate it fired most Sunday mornings for a year, was muted, and then missed a genuine 3.1 GB lag on a Tuesday — because commits were arriving continuously and the metric behaved itself.

Alert on pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn).

Absence, then slowness

A disconnected standby has no row at all

Every lag metric derived from pg_stat_replication returns NULL when the standby is gone. A monitoring system that renders NULL as zero draws a perfect graph for a standby that stopped replicating weeks ago.

Alert on the count first, against a written inventory:

SELECT count(*) FROM pg_stat_replication WHERE application_name = 'standby-02';

And never render NULL as zero. A NULL sample is missing data; it belongs on the graph as a gap and in the alerting as a collection failure.

Test both alerts this period

-- on the standby: genuine lag, on demand, harmlessly
SELECT pg_wal_replay_pause();
-- generate writes on the primary, wait for the page, then:
SELECT pg_wal_replay_resume();

And stop a standby deliberately to test the absence alert.

Both take a minute. An alert that has never fired has never been shown to work, and every replication alert that failed in the incidents this course is built from had been configured correctly and never tested.

Slots outlive the things they were created for

A slot with no consumer retains WAL forever

SELECT slot_name, slot_type, active, wal_status,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

Three things to read:

active = false for longer than a deliberate window. It means nothing is connected now, not that nothing will reconnect — confirm with the owning team before dropping.
wal_status other than reserved. The primary can no longer guarantee the WAL this slot needs.
catalog_xmin_age. A logical slot pins the freeze horizon of the whole database, and no vacuum of any kind can freeze past it.

One measured estate spent three weeks vacuuming a table that could not be vacuumed, because a Debezium slot created eleven months earlier had outlived the team that owned it.

Every slot needs an owner recorded outside the database.

Prove it end to end

-- primary
INSERT INTO repl_probe VALUES (now());

-- standby, seconds later
SELECT max(at) FROM repl_probe;

-- standby: this must FAIL
INSERT INTO repl_probe VALUES (now());
-- ERROR:  cannot execute INSERT in a read-only transaction

Two statements per standby. They exercise the whole path, which no view does on its own.

Where the numbers come from

Lag comes from pg_stat_replication on the primary in bytes, using pg_wal_lsn_diff, and from pg_stat_wal_receiver and pg_last_wal_replay_lsn() on each standby. Time-based lag is recorded but never used as the threshold, because it climbs on an idle primary while the standby is perfectly caught up.

Slot retention comes from pg_replication_slots: active, wal_status, and the bytes held from restart_lsn. max_slot_wal_keep_size comes from pg_settings with its source, because a value inherited from the -1 default is a decision nobody made.

Access this needs

A role holding pg_monitor on the primary and on every standby, since the primary-side and standby-side views answer different questions and the primary-side one is unavailable in the failure that matters most.

Read access to each standby’s configuration — primary_conninfo, recovery_min_apply_delay, cluster_name — and to the pg_wal filesystem on the primary.

The ability to write one row to a test table on the primary and read it back on each standby. That is the only item that proves data is moving; everything else proves a process is connected.

What the review produces

A dated record naming the reviewer, every node in the topology, and the disposition of every item. Attach the pg_stat_replication extract with one row per expected standby, the slot listing with active and wal_status, and the end-to-end write-and-read test with its timing.

A standby that is expected and absent is the finding that outranks every lag measurement, and it goes to the service owner the same day.

Sign-off

Reviewer: ________________ Date: ___________
Database owner: ___________ Date: ___________
Service owner: ____________ Date: ___________

Every critical item must pass. A failing critical item is a blocker, not a note for the next sprint: record the date, the reviewer, the disposition of every item that did not pass, and the name of whoever accepted the residual risk.