skip to content

On a PostgreSQL primary, pg_wal keeps growing and pg_stat_archiver.failed_count keeps rising; what is going on, and how do you fix it safely?

level: middleimportance: should knowfreq 50%

answer

  1. a stuck queue, not a slow one
  2. the oldest .ready segment blocks the rest
  3. last_failed_time versus last_archived_time
  4. never delete files from pg_wal

basics

~20 s

archive_command is exiting non-zero, so the archiver keeps retrying the oldest unarchived segment and nothing behind it can be recycled. Read the server log and pg_stat_archiver, fix the destination, and let the backlog drain in order.

solid answer

~40 s

Each completed segment gets a `.ready` marker in `pg_wal/archive_status`, and the archiver works through them oldest first. When `archive_command` exits non-zero, it tries that same file three times, a second apart, logs a warning that archiving it *failed too many times, will try again later*, and comes back to the same file later; it never skips ahead. Unarchived segments cannot be removed or recycled whatever `max_wal_size` says, so `pg_wal` grows until the filesystem fills and the server does a PANIC shutdown. Diagnose with `pg_stat_archiver` (`last_failed_time` newer than `last_archived_time`), the log line `archive command failed with exit code N`, and the count of `.ready` files. Fix the cause (permissions, a full or missing mount, an existing file with different contents), reload a corrected command if needed, and never hand-delete files in `pg_wal`.

code

sql · 7 lines
sql
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

SELECT count(*) AS segments_waiting
FROM pg_ls_archive_statusdir()
WHERE name LIKE '%.ready';

go deeper

for a junior

Recall that a failing archive_command makes pg_wal grow, and that pg_stat_archiver and the server log are the first places to look.

for a middle

Explain why one failing segment blocks every newer one, and why the checkpointer cannot recycle unarchived WAL whatever max_wal_size says.

for a senior

Show the runbook: alert on archive age and the .ready backlog, fix the destination, reload a corrected command, and take a new base backup after any gap in the archive.

for a principal

Decide who owns archive health, how its alerts tie into the recovery point the business was promised, and when a hand-written command should give way to a backup manager.

## How the archiver walks through pg_wal When a WAL segment fills, PostgreSQL drops an empty marker named after it with a `.ready` suffix into `pg_wal/archive_status/`. The **archiver** background process picks the oldest `.ready` segment, runs `archive_command` (or the `archive_library` module) on it, and on success renames the marker to `.done`. Only segments marked done may be removed or recycled at a later checkpoint. The order is strict: the archiver does not skip a segment it cannot archive. ## What a failing command does to a primary 1. The command exits non-zero. The failure is counted in `pg_stat_archiver`, and the log shows `archive command failed with exit code N` next to whatever the command printed on stderr. 2. The archiver tries the same file three times, a second apart, then logs a WARNING that archiving the file *failed too many times, will try again later*. It wakes again when another segment becomes ready or after about a minute, and starts with the same oldest file. 3. Every later segment stays `.ready`. The checkpointer may not remove or recycle them, regardless of `max_wal_size`, so `pg_wal/` grows by every byte of WAL the workload writes. 4. If the filesystem holding `pg_wal/` fills, the server performs a **PANIC** shutdown. Committed transactions are not lost, but the database stays down until space is freed. Meanwhile the newest point the archive can restore to is frozen at the last archived segment, even though the primary itself looks healthy. ## Diagnosing it | Signal | Where | What it tells you | |---|---|---| | `failed_count`, `last_failed_wal`, `last_failed_time` | `pg_stat_archiver` | failures are happening, and on which segment | | `last_archived_wal`, `last_archived_time` | `pg_stat_archiver` | how stale the archive is | | count of `*.ready` names | `pg_ls_archive_statusdir()` | the size of the backlog | | `archive command failed with exit code N` | server log | the command's own error output | | `"archive_mode" enabled, yet archiving is not configured` | server log | archiving is on but no command or library is set | Typical root causes: the archive mount is gone or read-only, the target is full, the server's operating-system user lost write permission, a credential for remote storage expired, or a file with the same name and **different contents** already exists, for example because a second cluster was pointed at the same directory. A correct command refuses that last case on purpose. One blind spot matters for alerting. If the command is killed by a signal, the shell exits with a status above 125 (*command not found*), or an archive module raises ERROR or FATAL, the archiver process aborts and is restarted, and that failure is **not** counted in `pg_stat_archiver`. So alert on the age of `last_archived_time` and on the `.ready` backlog, not on `failed_count` alone. ## Fixing it without breaking the chain 1. Run the command by hand, as the server's operating-system user, against the segment named in `last_failed_wal`, and fix whatever it reports. 2. If the command itself must change, edit `archive_command` and reload; `archive_mode` stays on and no restart is needed. 3. Watch `last_archived_wal` advance and the `.ready` count fall; the backlog drains oldest first. 4. If the filesystem is about to fill, add space to it rather than deleting anything from `pg_wal/`. ## Traps - **Never delete files from `pg_wal/` by hand.** A segment that is not yet archived, or is still needed for crash recovery, is gone for good. - **`/bin/true` is not a pause button.** It frees `pg_wal/` because every segment now reports success, but those segments never reach the archive. Recovery cannot cross that hole, so take a fresh base backup as soon as a real command is back. - **Emptying `archive_command`** is the documented temporary pause: segments accumulate safely until a command returns, for as long as the disk lasts. - **Lowering `max_wal_size`** does nothing for unarchived segments; it steers checkpoint frequency and is not a cap on `pg_wal/`. - **Other WAL holders look similar.** Replication slots and `wal_keep_size` also retain WAL but leave `pg_stat_archiver` clean; they are covered with replication slots and streaming replication.

  • Why is setting archive_command to /bin/true a dangerous way to relieve a full pg_wal?
    It does free `pg_wal`, because every segment now reports success and is recycled, but those segments never reach the archive. The archive now has a hole, so no base backup taken before it can roll forward past it. If it is ever done in an emergency, restore a real command and take a new base backup immediately; point-in-time recovery is only possible from that backup onward.
  • How can PostgreSQL's archiving be broken while pg_stat_archiver.failed_count stays flat?
    If the command is killed by a signal other than `SIGTERM`, the shell reports an exit status above 125 such as command not found, or an archive module raises ERROR or FATAL, the archiver process aborts and the postmaster restarts it. Those failures are not reported in `pg_stat_archiver`, so watch the age of `last_archived_time` and the `.ready` count as well.
  • pg_wal is growing but pg_stat_archiver shows recent successes; what else retains WAL on a PostgreSQL primary?
    A replication slot whose consumer has stopped holds WAL from its `restart_lsn`, and `wal_keep_size` keeps a fixed amount for standbys. Neither shows in `pg_stat_archiver`; check `pg_replication_slots` first. Slot retention and its limits are covered with replication slots.

A single post-office counter that must ship parcels strictly in order: when the first parcel is refused, the clerk keeps retrying it, and every later parcel piles up in the lobby, which is pg_wal, until the lobby is full.

  • The archiver skips a failing segment and keeps archiving the newer ones
  • Lowering max_wal_size makes PostgreSQL remove the unarchived segments
  • Deleting old files from pg_wal by hand is a safe way to free space
  • A flat failed_count in pg_stat_archiver proves archiving is working
  • When pg_wal fills the disk, recently committed transactions are lost