WAL archiving: archive_command, integrity, retention · PostgreSQL

A base backup is one instant. The archive is what turns it into every instant since. Lesson XII-03 covered archive_mode as a WAL retention mechanism; this lesson covers it as the recovery mechanism it exists for.

The configuration

wal_level = replica          # or logical; minimal cannot be archived
archive_mode = on            # requires a restart
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60         # optional; bounds worst-case data loss

Read-only / Safethe cluster used for the measurements in this part— Captured on 18.6.

$ SELECT name, setting FROM pg_settings WHERE name LIKE 'archive%' OR name = 'wal_level';

 archive_command | test ! -f /archive/%f && cp %p /archive/%f
archive_mode    | on
archive_timeout | 60
wal_level       | replica

%p is the path of the segment to archive, relative to PGDATA. %f is its bare filename. The test ! -f half is not decoration: it refuses to overwrite a file that already exists. Without it, a re-archived segment overwrites a good one, and the archive is silently broken at exactly the point you will need it.

Requirements for an archive_command

It must return non-zero on failure. PostgreSQL treats zero as “archived” and recycles the segment. A command that fails quietly and exits zero destroys WAL.

It must not overwrite an existing file. A segment can be presented for archiving more than once — after a crash, after a restart. If the second attempt overwrites the first, and the first was correct while the second is truncated, the archive is now broken.

It must be durable before returning zero. cp returns before the data reaches disk. On a local archive directory this is a real exposure window; the documentation’s cp example is illustrative, not a production recommendation.

It must be fast enough. Archiving is serial. If archiving one 16 MB segment takes longer than generating the next one, the queue grows without bound. At the 4,359 kB/s from lesson XII-02, a segment is generated roughly every four seconds.

It must not depend on anything that can hang. A command blocked on an unresponsive NFS mount blocks archiving entirely, and pg_wal grows at the full generation rate until it is killed.

When it fails

Service impact possiblea genuine archive_command failure— Real log output from 18.6, captured before the /archive directory existed.

$ docker logs rbpg-pitr 2>&1 | grep -A2 'archive command failed'

cp: cannot create regular file '/archive/000000010000000000000001': No such file or directory
2026-08-27 21:22:04.341 UTC [62] LOG:  archive command failed with exit code 1
2026-08-27 21:22:04.343 UTC [62] DETAIL:  The failed archive command was: test ! -f /archive/000000010000000000000001 && cp pg_wal/000000010000000000000001 /archive/000000010000000000000001

The DETAIL line reports the exact expanded command, with %f and %p substituted. That is what makes an archive failure diagnosable: you can copy the line, run it by hand, and see the real error.

PostgreSQL retries a failing archive_command indefinitely. It does not give up and it does not skip the segment. Skipping would silently break the archive, so refusing to skip is correct — and the consequence is that the segment cannot be recycled and pg_wal grows at the full generation rate until the command succeeds.

This is the mechanism behind one of the most common PostgreSQL outages: a full pg_wal filesystem caused by an archive destination that went away. Lesson XII-03 measured the same growth from a different cause. Part XVIII covers the recovery.

Read-only / Safethe view to alert on— EXECUTION VALIDATED on 18.6 during normal operation. All seven columns of the view.

$ SELECT * FROM pg_stat_archiver;

-[ RECORD 1 ]------+------------------------------
archived_count     | 1
last_archived_wal  | 000000010000000000000021
last_archived_time | 2026-08-27 21:46:03.786349+00
failed_count       | 0
last_failed_wal    |
last_failed_time   |
stats_reset        | 2026-08-27 21:41:03.608505+00

Alert on failed_count increasing, and on last_archived_time falling behind. A stalled archive on a quiet cluster looks identical to a healthy one until you compare it against the clock.

-- how far behind is the archive?
SELECT last_archived_wal,
       now() - last_archived_time AS archive_age,
       failed_count,
       now() - last_failed_time   AS since_last_failure
  FROM pg_stat_archiver;

The precedence trap

ALTER SYSTEM wrote the file, reported success, and changed nothing

While preparing this part I tried to force a repeated archive failure:

ALTER SYSTEM SET archive_command = 'exit 1';
SELECT pg_reload_conf();

Both succeeded. The file was written:

Read-only / Safepostgresql.auto.conf, after ALTER SYSTEM— Captured on 18.6.

$ grep archive /var/lib/postgresql/18/docker/postgresql.auto.conf

archive_command = 'exit 1'

And the setting did not change:

Read-only / Safewhat the server was actually using— Captured on 18.6 immediately afterwards. Note the source column.

$ SELECT name, setting, source FROM pg_settings WHERE name = 'archive_command';

      name       |                  setting                   |    source
-----------------+--------------------------------------------+--------------
archive_command | test ! -f /archive/%f && cp %p /archive/%f | command line

source is command line. This container was started with -c archive_command=... as a postmaster argument, and command-line arguments outrank postgresql.auto.conf.

This is the precedence rule from Part III, met by accident, and it is a genuine production hazard rather than a container curiosity: the official PostgreSQL container image is routinely configured this way, so an operator who changes archive_command with ALTER SYSTEM on such a cluster gets a success message, a modified file, and no change in behaviour. They then believe archiving is configured differently from how it is.

Confirm with pg_settings.source, never with the file contents.

Retention

The archive has to contain enough to reach every point you promise to recover to. That means, at minimum:

Every segment from the start LSN of the oldest base backup you intend to keep onward. Not the oldest segment you happen to have — the one that backup needs.
The .backup label files, which record each backup’s start and end positions.
Every timeline history file. A .history file is tiny and losing one makes the timeline it describes unreachable.

# Bounded by the oldest backup, not by a fixed age.
# Deleting segments a surviving backup needs makes that backup useless.
pg_archivecleanup /archive 000000010000000000000009

pg_archivecleanup deletes segments older than the one named. The name to pass comes from the oldest base backup you are keeping, and this is the ordering that matters: expire the backup first, then trim the archive to the new oldest backup. Reversing those two steps deletes WAL a live backup depends on.

archive_library, and what pg_receivewal does that archiving cannot

archive_library is an alternative to archive_command, introduced in PostgreSQL 15. Instead of forking a shell per segment, the server loads a module implementing the archiving callbacks in-process. PostgreSQL ships basic_archive as a working example. The benefits are real — no fork per segment, and the module can implement durability correctly rather than hoping cp did — and the ecosystem of production archive libraries is still small, so archive_command remains what most clusters use.

pg_receivewal is a different mechanism with a different guarantee, and the difference matters for RPO.

archive_command runs when a segment is complete. Until 16 MB has been written, or archive_timeout fires, the segment is not archived at all. Lose the primary’s storage and you lose everything since the last archived segment — up to a full segment, or up to archive_timeout seconds if you set it.

pg_receivewal connects as a replication client and streams WAL continuously, writing it to a local directory as it arrives. With --synchronous it flushes and reports back, and can participate in synchronous commit, which makes the RPO effectively zero for anything that reached commit.

The costs are that it is a process you have to run, supervise and monitor — a stopped pg_receivewal is a stopped archive, and it is easier to overlook than a failing archive_command because nothing on the primary logs anything. If it uses a replication slot for safety, and it should, that slot retains WAL on the primary exactly as lesson XII-03 measured. A pg_receivewal that dies quietly with a slot held is a pg_wal filesystem filling up for a reason nobody is looking at. PostgreSQL 18’s idle_replication_slot_timeout is the mitigation, and it trades one failure mode for the other: the slot is released and the WAL gap becomes real.

Many production setups run both — archive_command for the durable, verifiable archive, pg_receivewal for the tail — which is more moving parts and a genuinely smaller RPO.

What to take from this

archive_command must return non-zero on failure, refuse to overwrite, be durable, be fast enough, and never hang.
Failures retry forever. pg_wal grows at the full generation rate until the command succeeds.
The DETAIL line in the log gives you the exact expanded command.
Alert on pg_stat_archiver.failed_count and on last_archived_time falling behind.
Check pg_settings.source. ALTER SYSTEM was measured writing the file, reporting success, and changing nothing.
Retention is bounded by the oldest backup you keep, not by an age.

Cross-course references

Linux for Production Sysadmins — Part XXXVI (Scheduled operations) covers the archive destination’s own retention, and Part XIV (Filesystems) covers what happens to the primary when that destination stops accepting writes.
Observability for Production Sysadmins — Part LIX (Database observability) covers alerting on failed_count and on archive lag, which is the failure that is silent until the volume is full.