PITR fails after a silent gap in the WAL archive

archive_command is a contract: exit status 0 means the file is durably stored, and PostgreSQL is then free to recycle or remove that segment from pg_wal. The wrapper script returns 0 unconditionally, so a transient NFS error on one segment was reported as success. That segment was recycled, and the archive now has a permanent, unrecoverable gap.

Because each command call succeeded from PostgreSQL's point of view, pg_stat_archiver.failed_count stayed at 0 and last_archived_time kept advancing - the two metrics most teams alert on. The failure is invisible from inside the server.

The correct idiom never lies about failure:

test ! -f /mnt/archive/%f && cp %p /mnt/archive/%f

If the copy fails or the file already exists, the command returns nonzero, PostgreSQL retries the same segment forever and retains WAL - which grows pg_wal, an alertable symptom, instead of creating a hole.

Monitoring that would have caught it:

• Alert on pg_stat_archiver.last_failed_time / failed_count and on staleness of last_archived_time (no successful archive in N minutes).
• Independently verify continuity from outside: compare the archive listing against pg_walfile_name(pg_current_wal_lsn()) and assert that segments form an unbroken sequence.
• Use a tool that tracks and verifies what it shipped (pgBackRest, WAL-G) rather than a hand-written cp wrapper.

The RPO is not 'the last backup' - it is 'the last point before the gap'. Continuous recovery is possible only from the 02:00 base backup forward through uninterrupted WAL. Everything after 0A3F/7B is unusable because replay cannot skip a segment. So the theoretical best recovery point is wherever the archive broke, which could be hours or days earlier; nothing after it can be reached.

Options, in order of value:

1. Find the missing segment. It is a single 16 MB file. Check:
   • the streaming standby's pg_wal - it received that WAL and may still hold it;
   • the primary's own pg_wal if wal_keep_size retained it and it has not been recycled;
   • any NFS snapshots of the archive mount (verify contents against the file's expected size and checksum, not just filename).
2. If no copy exists, run a second restore that stops at the last segment before the gap and accept the data loss, then reconcile with application-level records (order confirmations, downstream systems) to quantify what was lost.
3. Only then decide whether to also replay forward by re-applying the archive from whatever point is reachable - do not attempt to stitch files of unknown provenance into the archive; a corrupted segment produces a corrupt database silently.

Do not use recovery_target_time = '14:31' when the WAL does not exist; choose the target only after confirming continuity with pg_waldump on the candidate segments. And restore to a separate host, never over the primary - the deleted rows may be recoverable from the primary's own heap before it is vacuumed anyway.

Archiving

• Replace the wrapper with the test ! -f ... && cp ... form, or move to pgBackRest/WAL-G with repository verification and retention.
• Alert on three independent signals: pg_stat_archiver.failed_count increasing, last_archived_time older than ~5 minutes, and pg_wal file count / total size above a threshold (when an archive stalls, pg_wal grows - that is the early warning you want).
• Set archive_mode = always on the standby too, so a standby being promoted does not suddenly stop archiving.
• Keep the archive immutable: no process other than PostgreSQL's archiver writes there, and it is not the same filesystem as pg_wal.

Drill, scheduled monthly

1. Provision a scratch host, unzip the latest base backup, set restore_command and a recovery_target_time from a log line in the drill scenario.
2. Set recovery_target_action = 'pause' (not the default promote) so you can inspect the database before it opens for writes, then pg_wal_replay_resume() or cancel after checking row counts.
3. Validate the backup itself with pg_verifybackup before any restore attempt.
4. Record actual RTO from a cold start, and compare the achieved recovery point against the target - a backup you have never restored is an assumption, not a backup.
5. Rotate the scenario: full restore, single-table restore, restore to a point in the middle of the retained window.