Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-replica-down~35 min

The failover target had not been replicating for six weeks, and the dashboard was green the whole time

Reported symptoms

  • ●A planned failover rehearsal at 20:00 promotes db-standby-02 and the application immediately reports missing data from the preceding six weeks
  • ●The promotion itself succeeds in under a second and the standby becomes writable normally, so nothing about the promotion mechanics is suspect
  • ●The replication lag dashboard has shown a flat zero for the entire six-week period and shows zero at the moment of the rehearsal
  • ●Nobody has been paged about replication at any point since the standby was built eight months earlier
  • ●The standby has been serving read-only reporting traffic throughout and those reports have not obviously been wrong, because they aggregate over months
  • ●The rehearsal is aborted and the standby is taken out of service, leaving the estate with no failover target
  • ●The primary is entirely healthy and has been throughout

Evidence

  • · pg_stat_replication on the primary is empty and the monitoring history shows it has been empty since 11 July
  • · pg_stat_wal_receiver on the standby is also empty, so the walreceiver process is not running rather than running and stalled
  • · The standby log contains repeated FATAL: could not connect to the primary server: connection to server at 10.12.4.7 port 5432 failed: Connection timed out, at five-second intervals, beginning 11 July at 23:14
  • · The primary address in primary_conninfo is 10.12.4.7, which was the primary address before a network renumbering completed on 11 July
  • · The primary now answers on 10.12.9.7 and its DNS name resolves correctly, but primary_conninfo on the standby names the literal old address
  • · The dashboard graphs a metric derived from now() minus pg_last_xact_replay_timestamp() collected from the primary, which returns NULL when pg_stat_replication has no row for the standby
  • · The monitoring system renders a NULL sample as 0 rather than as a gap, so the graph showed a flat zero for six weeks
  • · pg_last_wal_replay_lsn on the standby equals its pg_last_wal_receive_lsn, so the standby is internally consistent and simply stopped six weeks ago
Diagnosis and resolutionclick to reveal

Root cause

The standby lost its replication connection during a network renumbering and never regained it, because `primary_conninfo` named the primary by a literal IP address that ceased to exist. The standby behaved correctly throughout. It retried every five seconds, logged a `FATAL` each time, continued to serve read-only queries from the data it had, and reported its own state accurately to anybody who asked. It never pretended to be current. Nobody asked. The monitoring was built on a metric that cannot express the condition it needed to detect. The dashboard graphed `now() - pg_last_xact_replay_timestamp()`, collected via the primary's `pg_stat_replication` — and when a standby is not connected, it has no row in `pg_stat_replication` at all, so the collector returned NULL. The monitoring system rendered NULL as zero. A disconnected standby therefore produced a perfect lag graph, indistinguishable from a standby that was perfectly caught up. The one condition that mattered most was the one condition the metric could not represent, and it was rendered as the healthiest possible value. There is a second defect underneath. Even had the metric been present, it is the wrong metric: `now() - pg_last_xact_replay_timestamp()` measures time since the last commit **reached** the standby, which on an idle primary grows with wall clock and produces false alarms, and which on a disconnected standby is unavailable entirely. A team that had been alerted by it would have learned to ignore it. The reporting traffic did not surface the problem because the reports aggregate over months and six weeks of missing recent data did not move the numbers enough for anybody to notice — which is its own finding about those reports.

Remediation

Do not promote the standby, and if it has been promoted, do not point anything at it. It is six weeks stale and promoting it discards six weeks of committed transactions. The rehearsal correctly stopped at this point. Establish the standby's actual position before deciding anything: ```sql -- On the standby: SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(); SELECT * FROM pg_stat_wal_receiver; ``` An empty `pg_stat_wal_receiver` means the receiver process is not running, which distinguishes "cannot connect" from "connected and stalled" — two conditions with different causes and different fixes. Correct `primary_conninfo`. It is a reloadable parameter, so this needs no restart: ```sql ALTER SYSTEM SET primary_conninfo = 'host=db-primary-01.internal port=5432 user=repl application_name=standby-02'; SELECT pg_reload_conf(); ``` Use the DNS name rather than an address. That is the specific change that prevents a recurrence, and it is worth making even though the immediate fix would work with the new literal address. Then confirm the standby can actually catch up, which is not guaranteed. Six weeks of WAL must still exist on the primary or in the archive. Check: ```sql -- On the primary: SELECT slot_name, active, wal_status, restart_lsn FROM pg_replication_slots; ``` If this standby had a slot, the WAL is retained and it will catch up — and the primary has been carrying six weeks of WAL, which is its own incident. If it had no slot, the WAL is long gone and the standby must be rebuilt from a new base backup. Whichever applies, watch it reconnect and verify from the **primary** that it appears in `pg_stat_replication` with `state = 'streaming'` and a shrinking byte lag.

Verification

`pg_stat_replication` on the primary contains a row for this standby with `state = 'streaming'`. This is the check the monitoring should have been making. `pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)` falls to a small number and stays there. `pg_stat_wal_receiver` on the standby shows `status = 'streaming'` and names the correct sender host. A row written on the primary appears on the standby within seconds. This is the end-to-end check, and it is the only one that tests the whole path rather than one view: ```sql -- primary CREATE TABLE IF NOT EXISTS repl_probe(at timestamptz); INSERT INTO repl_probe VALUES (now()); -- standby, a few seconds later SELECT max(at) FROM repl_probe; ``` The corrected monitoring fires when the standby is stopped deliberately. Test this before closing the incident: stop the standby, confirm an alert arrives, start it again. An alert that has never fired is an alert that has never been tested.

Prevention

**Alert on the standby being absent, before alerting on it being slow.** A missing row in `pg_stat_replication` is the condition that matters most and no lag threshold can express it: ```sql SELECT count(*) FROM pg_stat_replication WHERE application_name = 'standby-02'; ``` Zero is the alert. Compare against an expected inventory of standbys rather than against whatever happens to be connected, or a standby that never connects will never be missed. **Never render NULL as zero.** This is the defect that made six weeks invisible. A NULL sample is missing data and must be displayed as a gap and alerted on as a collection failure. A monitoring system that silently substitutes a healthy value for absent data is worse than no monitoring, because it actively asserts health. **Alert on byte lag rather than time lag.** `pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)` is meaningful when the primary is idle; `now() - pg_last_xact_replay_timestamp()` is not, and produces false alarms that train teams to ignore replication alerts. **Collect from the standby as well as the primary.** The primary-side view is unavailable exactly when the primary is the thing that failed. `pg_stat_wal_receiver` and `pg_last_wal_replay_lsn` on the standby survive that. **Use DNS names in `primary_conninfo`.** A literal address is a dependency on a network layout that will change. **Rehearse failover on a schedule, and treat the rehearsal as the monitoring test.** This rehearsal found a six-week-old fault. Running it monthly would have found it in under a month; running it never would have found it during a real failure. **Add replication to the network change checklist.** The renumbering completed successfully and nobody asked what held a literal address.

Reported symptoms

A planned failover rehearsal at 20:00 promotes db-standby-02. The application immediately reports missing data from the preceding six weeks.

The promotion itself worked perfectly — under a second, and the standby became writable normally.

The replication lag dashboard has shown a flat zero for the entire six weeks, and shows zero at the moment of the rehearsal. Nobody has been paged about replication since the standby was built eight months ago.

The standby has been serving read-only reporting traffic throughout. Those reports have not obviously been wrong, because they aggregate over months.

The rehearsal is aborted, the standby is taken out of service, and the estate now has no failover target.

The primary is entirely healthy and has been throughout.

Evidence provided

Read-only / Safethe primary has no standby, and has not had one since July
$ psql -c "SELECT * FROM pg_stat_replication;"
 pid | usesysid | usename | application_name | client_addr | state | sent_lsn | write_lsn | flush_lsn | replay_lsn 
-----+----------+---------+------------------+-------------+-------+----------+-----------+-----------+------------
(0 rows)

Illustrative output

Read-only / Safethe standby has been saying so every five seconds for six weeks
$ grep FATAL /var/log/postgresql/postgresql-18-main.log | tail -2
2026-08-28 19:58:41.204 UTC [1188] FATAL:  could not connect to the primary server: connection to server at "10.12.4.7", port 5432 failed: Connection timed out
2026-08-28 19:58:46.209 UTC [1189] FATAL:  could not connect to the primary server: connection to server at "10.12.4.7", port 5432 failed: Connection timed out

Illustrative output

The first such line is dated 11 July at 23:14.

primary_conninfo names 10.12.4.7. The primary has answered on 10.12.9.7 since a network renumbering completed on 11 July. Its DNS name resolves correctly.

The dashboard graphs a metric derived from now() - pg_last_xact_replay_timestamp(), collected from the primary’s pg_stat_replication. With no row present, the collector returns NULL. The monitoring system renders NULL as 0.

On the standby, pg_last_wal_replay_lsn() equals pg_last_wal_receive_lsn() — it is internally consistent and simply stopped six weeks ago.

Work the evidence before reading on

  1. The lag graph showed zero for six weeks. What was it actually measuring?
  2. pg_stat_wal_receiver on the standby is empty. What does that distinguish from a stalled receiver?
  3. The standby served read queries throughout without anybody noticing. What does that tell you about those reports?
  4. If the monitoring had graphed the metric correctly, would it have caught this?

Root cause

The standby behaved correctly and told the truth

It retried every five seconds, logged a FATAL each time, served read-only queries from the data it had, and reported its own state accurately. It never claimed to be current.

primary_conninfo named a literal address that stopped existing on 11 July. The renumbering completed successfully and nothing asked what held a hard-coded address.

The metric could not express the condition

The reports were not a safety net

Six weeks of missing recent data did not move numbers that aggregate over months. That is worth recording as its own finding: those reports could not have detected a six-week data gap, which limits what they can be trusted to tell anybody.

Resolution

Establish the standby’s real position:

-- on the standby
SELECT pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp();
SELECT * FROM pg_stat_wal_receiver;

An empty pg_stat_wal_receiver means the receiver process is not running — “cannot connect” — as distinct from a receiver that is connected and stalled. Different causes, different fixes.

Correct primary_conninfo. It is reloadable, so no restart:

ALTER SYSTEM SET primary_conninfo =
  'host=db-primary-01.internal port=5432 user=repl application_name=standby-02';
SELECT pg_reload_conf();

Use the DNS name. That is the change that prevents recurrence, and it is worth making even though the new literal address would also work.

Then establish whether catching up is even possible:

-- on the primary
SELECT slot_name, active, wal_status, restart_lsn FROM pg_replication_slots;

If this standby had a slot, six weeks of WAL has been retained and it will catch up — and the primary has been carrying six weeks of WAL, which is a second incident. If it had no slot, the WAL is gone and the standby must be rebuilt from a new base backup.

Verification

pg_stat_replication on the primary has a row for this standby with state = 'streaming'. That is the check the monitoring should have been making all along.

Byte lag falls to a small number and stays there.

pg_stat_wal_receiver on the standby shows status = 'streaming' and the correct sender.

End to end, which is the only check that tests the whole path:

-- primary
CREATE TABLE IF NOT EXISTS repl_probe(at timestamptz);
INSERT INTO repl_probe VALUES (now());
-- standby, seconds later
SELECT max(at) FROM repl_probe;

And the alert fires. Stop the standby deliberately, confirm the page arrives, start it again. An alert that has never fired has never been tested, and this incident is what an untested alert looks like after six weeks.

Prevention

Alert on absence before lag. A missing row in pg_stat_replication is the condition that matters most, and no lag threshold can express it. Compare against an expected inventory of standbys, not against whatever happens to be connected.

Never render NULL as zero. Missing data is a gap and a collection alert, never a healthy value.

Alert on byte lag, not time lag. pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) is meaningful on an idle primary; now() - pg_last_xact_replay_timestamp() is not.

Collect from the standby too. The primary-side view is unavailable exactly when the primary is what failed.

Use DNS names in primary_conninfo.

Rehearse failover monthly, and treat the rehearsal as the test of the monitoring. This one found a six-week-old fault; a monthly cadence would have found it in under a month, and never rehearsing would have found it during a real failure.

Add replication to the network change checklist.