Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-archive~40 min

The disk filled on a database that had not grown, and the backup job reported success every night

Reported symptoms

  • ●A disk-usage alert fires at 91 per cent on db-prod-07 at 02:40, having been at 44 per cent nine days earlier with no trend in between that anybody noticed
  • ●No table has grown: the sum of pg_total_relation_size across all databases is within two per cent of its value from a fortnight ago
  • ●The nightly logical backup job has reported success every night including the night of the alert
  • ●The team deletes old application logs and reclaims eleven gigabytes, and the usage returns to 91 per cent within four hours
  • ●A VACUUM FULL is proposed on the two largest tables on the theory that the growth is bloat
  • ●du shows pg_wal at 340 gigabytes against a max_wal_size of 4 gigabytes, which is discovered forty minutes into the incident
  • ●By 06:15 the filesystem is at 97 per cent and somebody suggests deleting the oldest WAL segments by hand

Evidence

  • · du -sh on the pg_wal directory reports 340G while max_wal_size is 4GB, so the target is exceeded by a factor of eighty-five
  • · The archive_status directory contains 21406 files ending in .ready and none of them has been renamed to .done since the storage maintenance window nine days earlier
  • · pg_stat_archiver reports failed_count of 118442, last_failed_wal naming a segment from nine days ago, and last_archived_time nine days in the past
  • · The cluster log contains repeated archive command failed with exit code 1 and WARNING: archiving write-ahead log file "..." failed too many times, will try again later
  • · archive_command is cp %p /mnt/wal-archive/%f and the mount is present but mounted read-only, having been remounted during a storage maintenance nine days earlier
  • · pg_replication_slots is empty, so the retention is entirely attributable to archiving rather than to a slot
  • · The nightly backup is pg_dump to a different filesystem, which succeeded every night and is unaffected by the archive failure
  • · The monitoring configuration contains a disk-usage alert and a backup-exit-status alert, and no alert referencing pg_stat_archiver or the archive_status directory
Diagnosis and resolutionclick to reveal

Root cause

WAL archiving had been failing for nine days, and PostgreSQL will not delete a WAL segment it has been told to archive and has not succeeded in archiving. When a segment fills, the server creates a marker file `pg_wal/archive_status/<segment>.ready`. The archiver process runs `archive_command` for each such file and, on **exit status zero**, renames it to `.done`. Only a segment marked `.done` may be recycled or removed at a checkpoint. A storage maintenance nine days earlier had remounted `/mnt/wal-archive` read-only. `cp` began returning non-zero, the server correctly kept retrying, and every segment produced since then stayed `.ready`. Twenty-one thousand of them. The consequence is that `max_wal_size` becomes unreachable. That setting is a soft target: it governs how much WAL may accumulate before a checkpoint is triggered, not how much may exist. A checkpoint cannot remove a segment still marked `.ready`, so the target was simply unattainable and `pg_wal` grew for nine days. Nothing about the database was unhealthy in any way an application could detect. Transactions committed normally, queries ran normally, and the growth was invisible to every check the team had. The nightly `pg_dump` wrote to a different filesystem and reported success truthfully — it had nothing to do with WAL archiving and could not have detected the problem. The deeper failure is that a database with nine days of unarchived WAL had, for those nine days, no point-in-time recovery capability at all. The disk alert is what surfaced it, but the recovery gap had existed since the maintenance window and would have been discovered during a restore.

Remediation

Establish how much time you have before doing anything else. The failure mode is a full filesystem, and a full `pg_wal` is a PANIC and a stopped cluster. ```bash df -h $PGDATA du -sh $PGDATA/pg_wal ls $PGDATA/pg_wal/archive_status/*.ready | wc -l ``` Fix the archive destination. Here the mount is read-only, so remount it read-write and confirm by writing a file as the `postgres` user — not as root, because the archiver runs as `postgres`. Nothing else is required. The archiver retries continuously, so as soon as `archive_command` starts succeeding the backlog drains in segment order without intervention. Watch it: ```sql SELECT archived_count, failed_count, last_archived_wal, last_archived_time FROM pg_stat_archiver; ``` ```bash watch -n5 'ls $PGDATA/pg_wal/archive_status/*.ready 2>/dev/null | wc -l' ``` If the filesystem is too close to full to survive the drain, the only safe intervention is to add space or move something else off that filesystem. Twenty thousand segments at 16 MB each will take time to copy, and the server keeps producing more while it works. **Do not delete WAL segments by hand.** This was proposed at 06:15 and it is the one action that turns a recoverable incident into an unrecoverable one. A segment removed from `pg_wal` before it is archived is gone; it is not in the archive, it is not anywhere, and any point-in-time recovery spanning it is now impossible. If space is genuinely critical and no other option exists, `pg_archivecleanup` against the archive is a different and safe operation — but nothing should remove files from `pg_wal` except PostgreSQL. Once the backlog has drained, expect `pg_wal` to shrink gradually rather than at once. Segments are recycled toward the target over several checkpoints because renaming a file for reuse is cheaper than deleting and recreating it.

Verification

`ls $PGDATA/pg_wal/archive_status/*.ready | wc -l` returns zero, or a small number that is falling. `pg_stat_archiver` shows `last_archived_time` within the last few minutes and `failed_count` no longer increasing. Note that `failed_count` is cumulative and will not reset; watch the rate rather than the value. `du -sh $PGDATA/pg_wal` falls over the following checkpoints toward `max_wal_size`. The archive contains a continuous, unbroken sequence of segments across the nine days. Verify this explicitly rather than assuming, because a gap means the recovery window is still broken: ```bash ls /mnt/wal-archive/ | grep -E '^[0-9A-F]{24}$' | sort | \ awk 'NR>1 && strtonum("0x" substr($0,17)) != prev+1 {print "GAP before " $0} {prev=strtonum("0x" substr($0,17))}' ``` Perform an actual point-in-time recovery to a timestamp inside the affected window, onto a separate host, and confirm it reaches the target. Until that has been done, the recovery capability is asserted rather than demonstrated.

Prevention

**Alert on `pg_stat_archiver`.** Two conditions: `failed_count` increasing, and `now() - last_archived_time` exceeding a threshold. Either fires within minutes of the first failure, where a disk alert fires after nine days. **Alert on the `.ready` count.** `ls $PGDATA/pg_wal/archive_status/*.ready | wc -l` needs no database connection, works when the database is unreachable, and is the cheapest archive health check that exists. **Do not use a bare `cp` as an `archive_command`.** It has three defects: it overwrites an existing file, which silently corrupts the archive when the archiver retries a segment; it can leave a truncated file behind on interruption; and it returns before the data is durable. Use `pgBackRest`, `WAL-G` or `barman`, which handle all three, or at minimum guard it with `test ! -f` and write-then-rename. **Include the archive in the storage maintenance checklist.** The remount was the proximate cause and nobody checked what depended on that export. **Test the recovery, not the backup.** A nightly `pg_dump` that succeeds says nothing about whether point-in-time recovery works. A monthly restore rehearsal to a timestamp — Lab 20's procedure — would have found this within days rather than after nine. **Alert on the recovery window itself**, expressed as the age of the oldest and newest segment in the archive. That is the number the business cares about and it went to nine days without anybody knowing.

Reported symptoms

A disk alert fires at 91% on db-prod-07 at 02:40. Nine days earlier the filesystem was at 44%. Nobody noticed the trend in between.

No table has grown. The sum of pg_total_relation_size across every database is within 2% of its value a fortnight ago. The nightly logical backup has reported success every night, including tonight.

The team deletes old application logs and reclaims 11 GB. Usage is back at 91% within four hours. Somebody proposes VACUUM FULL on the two largest tables, on the theory that this is bloat.

Forty minutes in, somebody runs du on the data directory and finds pg_wal at 340 GB, against a max_wal_size of 4 GB.

By 06:15 the filesystem is at 97% and there is a proposal to delete the oldest WAL segments by hand.

Evidence provided

Read-only / Safea queue twenty-one thousand segments long
$ du -sh $PGDATA/pg_wal && ls $PGDATA/pg_wal/archive_status/*.ready | wc -l
340G	/var/lib/postgresql/18/main/pg_wal
21406

Illustrative output

Read-only / Safethe archiver has not succeeded in nine days
$ psql -c "SELECT archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;"
 archived_count |    last_archived_wal     |      last_archived_time       | failed_count |     last_failed_wal      |       last_failed_time       
----------------+--------------------------+-------------------------------+--------------+--------------------------+------------------------------
      4471209 | 0000000100004E210000003C | 2026-08-19 21:14:07.221115+00 |       118442 | 0000000100004E210000003D | 2026-08-28 06:12:55.03318+00

Illustrative output

Read-only / Safeand it has been saying so, every ten seconds, for nine days
$ grep -E 'archive command failed|failed too many times' /var/log/postgresql/postgresql-18-main.log | tail -3
2026-08-28 06:12:53.028 UTC [1841] LOG:  archive command failed with exit code 1
2026-08-28 06:12:54.031 UTC [1841] LOG:  archive command failed with exit code 1
2026-08-28 06:12:55.033 UTC [1841] WARNING:  archiving write-ahead log file "0000000100004E210000003D" failed too many times, will try again later

Illustrative output

archive_command is cp %p /mnt/wal-archive/%f. The mount is present and is mounted read-only, remounted during a storage maintenance nine days earlier. pg_replication_slots is empty.

The monitoring configuration has a disk-usage alert and a backup-exit-status alert. It has nothing referencing pg_stat_archiver or archive_status.

Work the evidence before reading on

  1. No table has grown and pg_wal is eighty-five times max_wal_size. What can make that happen, and what are the two candidates?
  2. The nightly backup succeeded every night. Was that report wrong?
  3. The database has been completely healthy from the application’s point of view for nine days. What has it not been?
  4. Somebody wants to delete the oldest WAL segments. What exactly does that destroy?

Root cause

A segment marked .ready cannot be removed

When a WAL segment fills, the server creates pg_wal/archive_status/<segment>.ready. The archiver runs archive_command for it, and on exit status zero renames the marker to .done. Only a .done segment may be recycled or deleted at a checkpoint.

The storage maintenance remounted /mnt/wal-archive read-only. cp began returning 1. The server retried, correctly and forever, and every segment produced since then stayed .ready.

The backup report was true and irrelevant

The nightly pg_dump writes to a different filesystem. It succeeded. It has no relationship to WAL archiving and could not have detected this.

That is worth stating plainly because “the backups are fine” was said several times during the incident and was correct each time. A logical dump is a point-in-time snapshot; the WAL archive is what provides recovery to any moment between dumps. They are different capabilities with different failure modes and different monitoring.

Resolution

Establish the time available first, because the failure mode is a PANIC:

df -h $PGDATA
du -sh $PGDATA/pg_wal
ls $PGDATA/pg_wal/archive_status/*.ready | wc -l

Fix the destination. Remount read-write, and confirm by writing a file as postgres — the archiver does not run as root and a root-writable mount proves nothing:

mount -o remount,rw /mnt/wal-archive
su - postgres -c 'touch /mnt/wal-archive/.probe && rm /mnt/wal-archive/.probe && echo writable'

Nothing further is required. The archiver retries continuously; as soon as archive_command succeeds the backlog drains in order. Watch it:

watch -n5 'ls $PGDATA/pg_wal/archive_status/*.ready 2>/dev/null | wc -l'

Once drained, pg_wal shrinks over several checkpoints rather than at once, because segments are recycled by renaming rather than deleted. A still-large pg_wal immediately after the drain is not a failed repair; check the .ready count, which is zero straight away.

Verification

The .ready count is zero or falling.

pg_stat_archiver shows last_archived_time within minutes and failed_count no longer increasing — watch the rate, not the value, since the counter is cumulative.

pg_wal falls toward max_wal_size over subsequent checkpoints.

The archive is continuous. Verify it rather than assuming, because a gap means the recovery window is still broken:

ls /mnt/wal-archive/ | grep -E '^[0-9A-F]{24}$' | sort | \
  awk 'NR>1 && strtonum("0x" substr($0,17)) != prev+1 {print "GAP before " $0}
       {prev=strtonum("0x" substr($0,17))}'

A recovery is performed. Restore a base backup on a separate host, set recovery_target_time to a timestamp inside the affected window, and confirm the recovery reaches it. Until that has been done, the recovery capability is a claim.

Prevention

Alert on pg_stat_archiver. failed_count increasing, and now() - last_archived_time above a threshold. Both fire within minutes of the first failure.

Alert on the .ready count. It needs no database connection and works when the database does not:

ls $PGDATA/pg_wal/archive_status/*.ready 2>/dev/null | wc -l

Replace the bare cp. It overwrites on retry, which silently corrupts the archive; it can leave a truncated file; and it returns before the data is durable. Use a real tool, or at minimum guard it and write-then-rename.

Add the archive to the storage maintenance checklist. The remount was the proximate cause and nobody asked what depended on that export.

Rehearse the recovery monthly. A successful pg_dump says nothing about point-in-time recovery. A restore to a timestamp would have found this in days.

Alert on the recovery window, expressed as the age of the newest segment in the archive. That is the number the business is actually buying, and it silently went to nine days.