WAL archiving and the continuity of the archive

The previous lesson separated a logical dump from a physical copy and ended where both of them end: at one instant, chosen by whoever wrote the schedule. Recovering to a different instant is a separate mechanism, and it lives inside neither backup. It lives in a stream of files the database hands out one at a time, to a command somebody wrote, into a destination it never looks at again. That arrangement turns a nightly base backup into a near-continuous recovery capability, and it is the most quietly fragile thing in this part: the property it rests on — that the stream has no holes — is measured nowhere until a recovery needs it.

The write-ahead log becomes files that somebody else owns

Every change a PostgreSQL cluster makes is written to its write-ahead log before it is allowed to reach a data file. That log is not one growing file. It is a sequence of fixed-size segments — 16 MB in a default installation, fixed when the cluster is initialised — carrying names like 000000010000000000000005. When a segment fills, or when a switch is forced, it is finished: nothing will ever be appended to it again. That is the property that makes it safe to copy elsewhere, and it is why archiving works at segment granularity rather than continuously.

With archive_mode enabled, the server runs archive_command once for each completed segment — or hands it to the module named by archive_library — substituting the file’s full path as %p and its bare name as %f. The documentation states the contract plainly: the command must return zero only when it has succeeded. A zero exit is read as an assertion that this segment now exists durably somewhere else, and the server is then free to recycle its local copy. A non-zero exit means the segment is still the server’s problem, so it stays in pg_wal and the command is retried until it works.

The documentation is equally direct about two requirements operators routinely skip. The command must not overwrite a file already present in the archive, because a name collision that silently replaces good history destroys the continuity the archive exists to provide; the documented shape tests for existence first and fails if the name is taken. And the command has to be honest, because nothing downstream re-reads the destination to check.

archive_mode = on
archive_command = 'test ! -f /srv/wal-archive/%f && cp %p /srv/wal-archive/%f'
archive_timeout = 60s

That archive_timeout is not a default and not a recommendation; it is a number you choose. Left alone, a segment reaches the archive when it fills, which on a quiet cluster can be hours. A forced switch caps how far the archive can trail, at a cost the documentation names: a segment archived early is still archived at full size, so a short timeout on a low-write cluster fills the archive with mostly empty files. The bound it buys belongs to your architecture, not to PostgreSQL. With archiving healthy the data at risk is the current incomplete segment plus whatever the command has queued — a window set by write rate, segment size and the timeout you picked, and statable only by naming all three.

Recovery runs the same arrangement backwards. A restored base backup carries the location where replay must begin, and the server asks restore_command for segments by name, one at a time, replaying each and asking for the next. Recovery to an arbitrary point is therefore never a property of the backup: it is the base backup plus every segment from its start point forward, and the reachable points are exactly the ones those segments cover.

Measured: five segments, one label file, and a recovery that stopped where it was told

The capture recorded for this course exercises the whole path on PostgreSQL 18.6: a base backup taken while a workload ran, business continuing afterwards, somebody destroying the table, and the archive asked to put it back.

$ pg_basebackup -D /work/base -X stream -c fast

  >>> exit code: 0
rows contained in the base backup: 45000

--- business continues after the backup: 5,000 more orders arrive ---
rows now                      : 50000
checksum of the business data : sum(amount)=825025000
recovery target time          : 2026-08-28 13:34:40.077562+00

--- and then somebody runs an unqualified DELETE ---
rows after the mistake        : 0
pg_stat_archiver:
  archived=6 failed=0 last=000000010000000000000005
WAL segments in the archive   : 5
  000000010000000000000001
  000000010000000000000002
  000000010000000000000003
  000000010000000000000003.00000028.backup
  000000010000000000000004
  000000010000000000000005

Two independent statements about the archive appear there, and it matters that they are independent. pg_stat_archiver is the database’s own account of what it handed off: six files accepted, none refused, the last 000000010000000000000005. The listing is the destination’s account of what exists. The counts differ for a reason worth internalising — archived=6 counts six files handed to the command, five of them WAL segments and the sixth 000000010000000000000003.00000028.backup, the label written when the base backup ended. The view counts archiving events; only the listing says the sequence has no holes.

Recovery was then pointed at 2026-08-28 13:34:40.077562+00, a time recorded from a live transaction after the base backup and before the deletion. The lines below are excerpted from that same capture’s recovery log.

  cp: cannot stat '/work/wal-archive/00000002.history': No such file or directory
  2026-08-28 13:35:12.701 UTC [631] LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
  2026-08-28 13:35:12.707 UTC [631] LOG:  restored log file "000000010000000000000003" from archive
  2026-08-28 13:35:12.708 UTC [631] LOG:  starting point-in-time recovery to 2026-08-28 13:34:40.077562+00
  2026-08-28 13:35:12.713 UTC [631] LOG:  restored log file "000000010000000000000004" from archive
  2026-08-28 13:35:12.726 UTC [631] LOG:  restored log file "000000010000000000000005" from archive
  2026-08-28 13:35:12.735 UTC [631] LOG:  recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00
  2026-08-28 13:35:12.736 UTC [631] LOG:  selected new timeline ID: 2
  2026-08-28 13:35:12.740 UTC [631] LOG:  archive recovery complete

Three segments were fetched, in order, by name. Replay began at 0/3000028 — inside segment ...03, which is why segments ...01 and ...02 are irrelevant to this recovery; they predate the base backup and nothing asks for them. Replay stopped at the first commit past the target and selected a new timeline. The verification afterwards reported rows recovered : 50000 (expected 50000) and sum(amount) : 825025000 (expected 825025000), which is the only acceptance criterion in this course: a property recorded before the incident, reproduced by the recovered system.

Notice the cp: cannot stat line. The full capture holds three of them, two asking for 00000002.history and one for 00000001.history, and every one is normal: a cluster on timeline 1 has no history file, and timeline 2 did not exist until recovery selected it. A restore_command returning non-zero is not an error condition; it is how recovery is told a file is not there. Hold that thought, because it is the whole mechanism of this lesson’s failure mode.

A missing log, loudly and then quietly

The recovery above worked because every name it asked for resolved. The failure mode is worth meeting first in its extreme form, the only form that announces itself. The same capture starts a copy of the data directory whose pg_wal had been emptied — the shape a common arrangement produces when data sits on one volume, the log on another, and only the data volume is captured.

$ pg_ctl -D /work/nowal start

  waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
>>> exit code: 1

2026-08-28 13:34:37.879 UTC [132] LOG:  creating missing WAL directory "pg_wal/archive_status"
2026-08-28 13:34:37.879 UTC [132] LOG:  creating missing WAL directory "pg_wal/summaries"
2026-08-28 13:34:37.879 UTC [132] LOG:  invalid checkpoint record
2026-08-28 13:34:37.879 UTC [132] PANIC:  could not locate a valid checkpoint record at 0/2F20158
2026-08-28 13:34:37.941 UTC [126] LOG:  startup process (PID 132) was terminated by signal 6: Aborted

That failure is immediate and precise. could not locate a valid checkpoint record at 0/2F20158 says the server computed the exact position it had to begin from, looked for the record there, and found nothing; startup aborted with exit status 1. The two creating missing WAL directory lines above the PANIC are the same arithmetic in a smaller key — the server rebuilt the empty scaffolding it expects beneath pg_wal, then found the content gone.

Now subtract one segment instead of all of them, from the middle of the archive rather than from pg_wal. Every part of that signal disappears. Replay reaches the gap, restore_command returns non-zero on a name that does not resolve, and recovery treats it as it treated 00000002.history in the successful capture: a file that is not there. Provided the gap lies past the point that capture logged as consistent recovery state reached at 0/3000120, the cluster finishes as that one did — archive recovery complete, a new timeline, connections accepted — with every transaction behind the gap absent and nothing in the log saying so.

An explicit recovery target is what converts that silence back into a signal. Recovery with no target replays to the end of whatever WAL it can find and then completes, on a cluster short of where you assumed it would be; ending at the end of the available log is exactly what an untargeted recovery is asked to do. Give it a target it cannot reach and it can no longer report success: the attempt ends fatally with recovery ended before configured recovery target was reached, the message the PostgreSQL course’s point-in-time-recovery capture records on this same 18.6 build. A restore test that sets no target is weaker than it looks, because the shortfall it cannot detect is the one an incident finds later.

Two signals that bound the recovery window, and one that reads the destination

Because a gap is invisible until recovery needs it, the archive must be monitored as a production signal rather than inspected during an incident. pg_stat_archiver supplies most of what is needed, and its columns have distinct shapes.

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time,
       now() - last_archived_time AS archive_age
FROM pg_stat_archiver;

failed_count only ever increases until statistics are reset, so alerting on its absolute value is meaningless — a cluster that failed nine times last March sits permanently above any threshold. The signal is the increase: any movement means segments are being retried and the archive is behind. last_archived_time is a timestamp, and the useful quantity is its age relative to now, compared against the interval you expect from your write rate and your archive_timeout. In the measured capture that pair read archived=6 failed=0 with the last archived segment named, and the directory listing agreed with it.

Neither signal catches the worst case. An archive_command that exits 0 without writing the file leaves failed_count at zero and last_archived_time moving forward while the destination accumulates nothing. The database believes the segment is safe elsewhere and recycles its own copy; the gap is created then and stays undetected until a recovery walks into it. Treat such a command as a defect rather than a risk, and pair it with a periodic check that lists the archive and confirms an unbroken sequence from the current backup label file forward. That listing is the only check that reads what the recovery will read.

Production discipline

1. Verify the archive has caught up before you rely on it. Read pg_stat_archiver for failed_count and the last archived segment, then list the destination and confirm the sequence is contiguous from the backup label file forward. Both readings in the measured capture — archived=6 failed=0 and five contiguous segments — preceded the recovery attempt.
2. Alert on failed_count increasing and on the age of last_archived_time separately. The first catches a command reporting honest failures, the second catches an archiver that has stopped making progress at all. Alerting on the absolute value of a monotonic counter catches neither.
3. Treat an archive command that can exit 0 without a durable write as a defect. A false success is worse than a failure: it releases the server’s own copy and creates a hole no counter or dashboard will report.
4. Set an explicit recovery target in every restore test. Without one, recovery ends at the end of the available log and reports success; with one, a short archive announces itself as recovery ended before configured recovery target was reached rather than as data loss discovered later.
5. Size and monitor the destination as part of the write path. A failing archive command retains segments in pg_wal, so the destination’s availability and free space bound how long the primary can keep writing — a number to state rather than discover.

Cross-course references

• PostgreSQL for Production Sysadmins — Part XII (WAL, Checkpoints and Crash Recovery) is the full treatment of the log this lesson only archives, including how a redo location like 0/3000028 is chosen; Part XIII (Backup, Archiving and Point-in-Time Recovery) covers the configuration surface — archive_mode, archive_command, restore_command, recovery targets — that this lesson exercises against one measured incident.
• Observability for Production Sysadmins — Part XIII (Rates and Counters) explains why archived_count and failed_count are monotonic counters whose rate carries the signal and whose value carries almost none; the alert design in this lesson is that material applied to a chain whose breakage has no other symptom.
• Ceph & Distributed Storage for Production Sysadmins — Part LXIV (Nearfull, Backfillfull and Full) describes what a distributed store does as it nears capacity, which becomes this lesson’s problem the moment the WAL archive lives on one: the point at which writes are refused is the point at which archive_command starts failing and pg_wal starts growing on the database host.

Quiz