Monitor PostgreSQL WAL Archiver Status with pg_stat_archiver

calendar May 23, 2026 / · 8 min read · pg_stat_archiver wal archiving backup recovery administration  ·
Share on: linkedin copy

Monitor PostgreSQL WAL Archiver Status with pg_stat_archiver

pg_stat_archiver is the canonical place to check whether PostgreSQL's continuous archiving is keeping up — a single-row view that exposes last_archived_wal, last_failed_wal, and the rolling failure count without parsing log files. On a healthy cluster the row is uninteresting; on a stalled cluster it is the difference between catching a problem in minutes and finding a full WAL volume on Saturday night.

Purpose and Overview

A PostgreSQL cluster configured for continuous archiving runs the archive_command (or archive_library on PostgreSQL 15+) once per finalized WAL segment. The command's job is to copy the segment somewhere durable — typically S3, NFS, or a backup server — so the WAL stream can later feed point-in-time recovery (PITR) or warm-standby replay. When the archive command fails (target unreachable, disk full, permission revoked, network partition), PostgreSQL retries continuously, pg_wal accumulates segments, and the cluster eventually fills its WAL volume. When the file system holding pg_wal fills, PostgreSQL does a PANIC shutdown; no committed transactions are lost, but the database stays offline until space is freed.

pg_stat_archiver is the operational pulse of this subsystem. It exposes counts of archived and failed segments, the names of the last segment archived and the last that failed, and the timestamps of each — enough to alert on a stalled archiver before the WAL volume becomes an incident. The view returns exactly one row; the row updates as the archiver processes segments and never accumulates history beyond the "last success" and "last failure" pair.

Because the view is single-row and counter-based, watch how the numbers change over time, not any single reading: a growing failed_count while archived_count stays flat is the unmistakable signature of a wedged archiver, and a last_failed_time newer than last_archived_time is the cleanest "broken right now" signal.

Sample Code

 1SELECT
 2    archived_count,
 3    last_archived_wal,
 4    last_archived_time,
 5    failed_count,
 6    last_failed_wal,
 7    last_failed_time,
 8    stats_reset,
 9    NOW() - last_archived_time   AS since_last_archive,
10    CASE
11        WHEN last_failed_time IS NULL
12             THEN 'archiver healthy'
13        WHEN last_archived_time IS NULL
14             THEN 'archiver has never succeeded'
15        WHEN last_failed_time > last_archived_time
16             THEN 'archiver currently failing'
17        ELSE 'archiver healthy'
18    END                          AS status
19FROM pg_stat_archiver;

Notes: Available in all supported PostgreSQL versions when archive_mode is on or always. The view always returns exactly one row. archive_mode can only be set at server start, so changing it needs a restart; archive_command can be set in postgresql.conf or on the server command line and takes effect on a reload.

Code Breakdown

The query is a single-row SELECT with two derived columns. Every counter and timestamp matters; the derived columns translate raw values into the metrics that drive an alert.

The pg_stat_archiver Single-Row View

pg_stat_archiver returns exactly one row per server. Counters accumulate from server start or from the last SELECT pg_stat_reset_shared('archiver') call. The view is global to the cluster (not per-database), which is why the row exists even on databases that perform no work directly — archive_mode is a cluster-level setting.

Success Counters

archived_count is the running total of WAL segments successfully archived. last_archived_wal is the filename of the most recent success (e.g. 000000010000000100000027). last_archived_time is the wall-clock timestamp of that success. On a busy cluster the success counters should advance every few seconds to minutes; on a low-write cluster with archive_timeout set, advancement is bounded by that timeout.

Failure Counters

failed_count, last_failed_wal, and last_failed_time are the parallel trio for failures. failed_count rises by one for every retry of an unsuccessful command — a steady increase while archived_count is flat means the archiver is wedged on a single segment.

The Derived since_last_archive and status Columns

since_last_archive is NOW() - last_archived_time, the interval since the last success. Combined with the cluster's normal WAL generation cadence, it is the primary alerting metric. The status CASE collapses the timestamps into a single verdict: healthy when no failure has been recorded or the last failure predates the last success; currently failing when the last failure is newer than the last success; has never succeeded when a failure is recorded but no segment has ever shipped. A cluster that has archived nothing and has recorded no failure (for example, archive_mode is off) also reports healthy, so check archived_count as well.

Key Archiver Health Signals

Time Since Last Archive

The most direct signal. Set an alerting threshold matched to your cluster's WAL generation rate — alert if no archive in 15 minutes on a system that normally archives every minute, or 24 hours on a low-write system protected by archive_timeout.

Counter Divergence (failed_count Climbing Without archived_count)

A growing failed_count while archived_count is flat is the unmistakable wedged-archiver signature. PostgreSQL retries the same segment until it succeeds, so the count grows linearly with retry rate.

pg_wal Directory Growth

The downstream symptom. A failing archiver means WAL segments accumulate in pg_wal because PostgreSQL will not recycle a segment until it has been archived (and replication slots, if present, have also moved past it).

ready-vs-done Status File Backlog

The server keeps status files for WAL segments in pg_wal/archive_status, with names ending in .ready (waiting to be archived) or .done (archived). Counting them gives a queue-depth reading that does not depend on the pg_stat_archiver counters. PostgreSQL also ships pg_ls_archive_statusdir() to list that directory.

1SELECT
2    COUNT(*) FILTER (WHERE name LIKE '%.ready')   AS ready_to_archive,
3    COUNT(*) FILTER (WHERE name LIKE '%.done')    AS already_archived
4FROM pg_ls_dir('pg_wal/archive_status') AS files(name);

This query needs a superuser: pg_ls_dir is restricted to superusers by default, and a non-superuser fails with "permission denied for function pg_ls_dir" unless EXECUTE on the function has been granted (PostgreSQL: System Administration Functions).

Standby Archiving with archive_mode = always

archive_mode = always lets a standby archive too — useful when the standby is the durable copy you want shipping WAL to long-term storage. With always, the standby calls the archive command for every WAL segment it receives, whether restored from the archive or streamed from the primary. With archive_mode = on, the archiver is not enabled during recovery or standby mode, so pg_stat_archiver on a standby shows nothing new.

Practical Applications

Early Alerting on a Stalled Archiver

Wire the since_last_archive value into your monitoring system with a threshold tuned to your normal cadence. The metric is cheap to sample (single-row view), updates the moment archiving stalls, and gives you minutes-to-hours of lead time before pg_wal saturates the volume.

PITR Readiness Verification

Before relying on PITR for a recovery scenario, verify the archive is current. A cluster with failed_count > 0 and a recent last_failed_time has segments queued behind a failing archive command. PostgreSQL keeps retrying and holds them in pg_wal, but until they ship, PITR to any target past last_archived_wal cannot complete. Fix the archive command while the segments are still on disk, not at recovery time.

Sizing the WAL Volume Against archive_timeout

archive_timeout forces a WAL segment switch after the set time whenever there has been any database activity (a fully idle cluster does not switch), bounding the recovery-point objective (RPO) but multiplying the segment count under low write load. Segments closed early are still full length, so a very short value bloats archive storage; the PostgreSQL docs call a minute or so usually reasonable. Size the pg_wal volume for the worst case: peak WAL generation rate plus headroom for wal_keep_size, plus the full backlog you can tolerate during an extended archiver outage.

Diagnosing Silent archive_command Failure

A custom archive_command that returns success after a partial copy (truncated S3 upload, half-written NFS file) silently corrupts the archive. PostgreSQL trusts the exit code; once a segment is marked archived, the segment in pg_wal is recyclable. Defensive practice: write to a temporary path, fsync, atomic-rename to the final path, then return success. Dedicated tools such as pgBackRest, barman and wal-g are built for this job; if you hand-roll a script, check each of these steps against what the tool's own documentation describes.

Version Compatibility

pg_stat_archiver was added in PostgreSQL 9.4. The columns shown above have been stable across versions. archive_mode = always (allowing standbys to archive) was added in 9.5. PostgreSQL 15 introduced archive_library — a loadable module alternative to the shell archive_command — and the same pg_stat_archiver view summarizes its activity identically.

pg_stat_reset_shared('archiver') has been available since 9.4 and affects only the archiver counters; it does not touch the WAL segments or interrupt archiving. Resetting after fixing a misconfiguration brings dashboards back to a clean baseline without restarting the cluster.

PostgreSQL 13 introduced wal_keep_size (replacing wal_keep_segments), which interacts with archiving by reserving WAL on top of what the archiver and replication slots require. Account for both when sizing the WAL volume.

Best Practices

  • Alert on since_last_archive not raw counter values — counters reset on pg_stat_reset_shared, intervals are robust
  • Use a dedicated archive tool (pgBackRest, barman, wal-g) — and read its documentation for how it handles atomic writes, retries, and verification
  • Set archive_timeout no higher than the data loss you can tolerate — it forces a segment switch when there has been database activity; a very short value bloats archive storage, so weigh it against your RPO
  • Monitor pg_wal/archive_status ready-file count — independent of pg_stat_archiver counters; needs a superuser or a granted EXECUTE on the listing function
  • Test PITR end-to-end periodically — a never-tested archive is functionally untrustworthy regardless of what the counters say
  • Size pg_wal volume for the worst case — peak WAL rate × tolerated archiver outage + wal_keep_size headroom

References

Posts in this series