Continuous Archiving, archive_command, WAL Retention, and Restore Commands

Learning outcomes

A base backup freezes a physical starting point. Point-in-time recovery becomes possible only when you can also supply every required WAL segment after that starting point. Continuous archiving copies completed WAL segments to durable archive storage before PostgreSQL recycles them. The archive path becomes part of your recovery chain and must be monitored like production storage.

1. Use a separate disposable archival cluster

archive_mode is a server-start setting. Rather than changing the long-lived ServiceHub lab, use a separate local cluster, for example port 55434, with a dedicated archive directory. The exact initdb/pg_ctl paths differ across Windows, Linux, packages, and containers, so run the commands from the PostgreSQL 18 binary directory available on your system.

mkdir -p ./ch13_archive_cluster ./ch13_wal_archiveinitdb -D ./ch13_archive_clusterchmod 700 ./ch13_wal_archive

wal_level = replicaarchive_mode = onarchive_command = 'test ! -f /ABSOLUTE/ch13_wal_archive/%f && cp %p /ABSOLUTE/ch13_wal_archive/%f'logging_collector = on

The example refuses to overwrite an existing archive file. In a production archive, an existing filename should normally be treated as success only after you have proven the existing bytes are identical; blindly overwriting can destroy the original timeline history.

2. Success means zero exit status

PostgreSQL treats archive-command exit status zero as “this completed segment has been archived and may eventually be recycled.” A nonzero exit means failure and the archiver retries later. This makes idempotency essential: the same segment can be requested more than once after retries or restarts.

SELECT archived_count, last_archived_wal, last_archived_time,       failed_count, last_failed_wal, last_failed_time, stats_resetFROM pg_stat_archiver;

SELECT pg_switch_wal();

If the workload is quiet, a segment may otherwise take a long time to fill. archive_timeout can force periodic switching, but setting it very low wastes archive capacity because even early-switched archived segments occupy full segment files.

3. Deliberately break and repair the archive

In the disposable lab, temporarily make the archive directory unwritable or point archive_command to a missing location, then generate/switch WAL. Observe failed_count and server logs. Repair the target and verify that retries eventually advance last_archived_wal.

SELECT * FROM pg_stat_archiver;SELECT pg_size_pretty(sum(size)) AS pg_wal_bytesFROM pg_ls_waldir();

4. restore_command is the reverse contract

During archive recovery PostgreSQL asks restore_command for a specific filename. The command copies it to the path PostgreSQL supplies. A missing requested file must return nonzero. Requests can include timeline-history files ending in .history; not every missing request at the end of recovery is an operator incident.

restore_command = 'cp /ABSOLUTE/ch13_wal_archive/%f %p'

Archive files must be protected like database data: WAL can reveal essentially all database changes. Encryption, access control, off-host copies, retention policy, and monitoring belong to the backup design even though PostgreSQL itself only invokes the command/module.

5. Retention follows the longest supported recovery objective

To recover a base backup, you need an unbroken WAL sequence from at least the backup's start through the target. If you keep seven daily base backups but only one day of WAL, the older backups cannot satisfy a seven-day PITR objective. Conversely, keeping WAL forever without tested retention/cleanup can exhaust storage.

SELECT pg_current_wal_insert_lsn() AS wal_start \gset-- run representative workload for a measured intervalSELECT pg_size_pretty(         pg_wal_lsn_diff(pg_current_wal_insert_lsn(), :'wal_start'::pg_lsn)       ) AS generated_wal;

Use measured WAL generation plus retention duration and safety margin to plan archive capacity; never invent a universal GB/day value.

6. Archive lag is both an RPO and capacity signal

pg_stat_archiver tells you the most recently archived WAL file and success/failure timestamps, but it does not directly tell you “seconds of recoverability.” Correlate those fields with current WAL generation, archive storage, and a known business marker. If the primary has committed important work whose WAL has not yet reached durable off-host archive storage, disaster RPO is worse than the transaction's local commit durability.

Archive backlog also consumes local pg_wal because PostgreSQL cannot recycle segments that still need archiving. If the filesystem fills, PostgreSQL can be forced offline. Therefore alerts should cover archive failure/staleness, local WAL growth rate, free space, and archive destination health together.

archive_command versus archive_library

Core PostgreSQL supports both a shell archive_command and an archive module configured with archive_library. A simple local lab is clearer with a shell command; production systems may use a purpose-built archive library or external backup tool. Either way, PostgreSQL's contract is the same: completed WAL must not be reported as successfully archived until a durable, retrievable copy exists.

Security and immutability

Archived WAL can reconstruct database changes, so archive permissions are sensitive data permissions. Keep the database server's write identity separate from any administrative identity capable of deleting long-term backup retention when practical. An archive that the compromised primary can rewrite or delete without constraint shares the same failure domain as production.

Authoritative references

Backup and recovery behavior is version-, topology-, privilege-, and storage-sensitive. These primary PostgreSQL sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Continuous Archiving and PITR
• PostgreSQL 18 — WAL Configuration / Archiving
• PostgreSQL 18 — pg_stat_archiver
• PostgreSQL 18 — Archive Modules