Lab 19: Configure WAL archiving, break it, and watch pg_wal grow past its limit

Objective

WAL archiving is what makes point-in-time recovery possible. It is also the subsystem most likely to fail silently for weeks and then take the server down with a full disk, because a failing archive does not stop the database — it just stops it from ever deleting WAL again.

By the end of this lab you will have turned archiving on from nothing, broken it on purpose, watched pg_wal grow to 1.4 GB against a 1 GB max_wal_size, repaired it, and watched an 88-segment backlog drain in under half a minute.

You will also see the specific reason every competent archive_command begins with a test: a bare cp silently replaced a 16,777,216-byte archived segment with an 8-byte file and reported success.

Architecture

One cluster, one archive directory, and a deliberately broken path in the middle.

flowchart TD
    W["backends write WAL"] --> S["pg_wal segments"]
    S --> R["archive_status/*.ready\nmarks a segment as archivable"]
    R --> A["archiver process\nruns archive_command"]
    A -->|exit 0| D[".ready becomes .done\nsegment may be recycled"]
    A -->|non-zero| Q["stays .ready\nretried, forever"]
    Q --> G["pg_wal grows past max_wal_size"]
    D --> AR["/archive"]

Requirements

• A PostgreSQL 18 cluster you can restart, with superuser and shell access. archive_mode is a postmaster parameter.
• Roughly 3 GB of free disk. The lab lets pg_wal grow deliberately; on a filesystem with less headroom, this lab is the incident it simulates.
• The lab uses rbpg-lab01, which has no archiving configured.

Scenario

A monitoring alert fires: the database server’s disk is 91% full. It was 60% last week. Nothing has been loaded, no table has grown, and the backup job reports success every night.

Tasks

Task 1 — The starting state

LAB="$HOME/rbpg-lab-19"
mkdir -p "$LAB"

docker exec rbpg-lab01 bash -c "mkdir -p /archive && chown postgres:postgres /archive"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, context FROM pg_settings
  WHERE name IN ('archive_mode','archive_command','archive_library',
                 'archive_timeout','wal_level')
  ORDER BY name;" | tee "$LAB/baseline.txt"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT * FROM pg_stat_archiver;" \
  | tee -a "$LAB/baseline.txt"

$ a pg_settings query, then SELECT * FROM pg_stat_archiver

      name       |  setting   |  context   
-----------------+------------+------------
archive_command | (disabled) | sighup
archive_library |            | sighup
archive_mode    | off        | postmaster
archive_timeout | 0          | s
wal_level       | replica    | postmaster
(5 rows)

archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time |          stats_reset          
----------------+-------------------+--------------------+--------------+-----------------+------------------+-------------------------------
            0 |                   |                    |            0 |                 |                  | 2026-08-28 00:09:02.920701+00
(1 row)

archive_command reads (disabled) rather than empty — that is what PostgreSQL displays when archive_mode is off, and it is a useful distinction from “configured but blank”.

Note the contexts. archive_mode is postmaster — a restart. archive_command is sighup — a reload. That asymmetry is deliberate and Task 5 depends on it.

wal_level = replica is already sufficient; archiving does not require logical.

Task 2 — Turn it on

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET archive_mode = on;"
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET archive_command = 'cp %p /archive/%f';"
docker exec rbpg-lab01 pg_ctlcluster 18 main restart

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab19;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab19 -c \
  "CREATE TABLE t(id int, payload text);
   INSERT INTO t SELECT g, repeat('a',200) FROM generate_series(1,300000) g;"
sleep 3

docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;"
docker exec rbpg-lab01 ls /archive

$ enable archiving, restart, generate WAL, then check the archiver

 archived_count |    last_archived_wal     | failed_count 
----------------+--------------------------+--------------
            5 | 00000001000000010000001F |            0
(1 row)

00000001000000010000001B
00000001000000010000001C
00000001000000010000001D
00000001000000010000001E
00000001000000010000001F

Working. %p is the path of the segment to archive, %f its bare filename.

Task 3 — Break it

docker exec -u postgres rbpg-lab01 psql -X -c \
  "ALTER SYSTEM SET archive_command = 'cp %p /nonexistent/%f';"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

for i in 1 2 3 4 5 6; do
  docker exec -u postgres rbpg-lab01 psql -X -d lab19 -c \
    "INSERT INTO t SELECT g, repeat('b',200) FROM generate_series(1,200000) g;" > /dev/null
done
sleep 5

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT archived_count, last_archived_wal, failed_count, last_failed_wal, last_failed_time
  FROM pg_stat_archiver;" | tee "$LAB/failure.txt"
docker exec rbpg-lab01 grep -E "archive command failed|archiving write-ahead" \
  /var/log/postgresql/postgresql-18-main.log | tail -4 | tee -a "$LAB/failure.txt"

$ point archive_command at a nonexistent path, reload, write, then read the archiver and the log

 archived_count |    last_archived_wal     | failed_count |     last_failed_wal      |       last_failed_time       
----------------+--------------------------+--------------+--------------------------+------------------------------
            6 | 000000010000000100000020 |            6 | 000000010000000100000021 | 2026-08-28 05:59:33.94707+00
(1 row)

2026-08-28 05:59:31.941 UTC [14304] LOG:  archive command failed with exit code 1
2026-08-28 05:59:32.944 UTC [14304] LOG:  archive command failed with exit code 1
2026-08-28 05:59:33.947 UTC [14304] LOG:  archive command failed with exit code 1
2026-08-28 05:59:33.947 UTC [14304] WARNING:  archiving write-ahead log file "000000010000000100000021" failed too many times, will try again later

Note what did not happen. No transaction failed. No client saw an error. The database is entirely healthy from the application’s point of view, and it will stay that way until the disk fills.

docker exec rbpg-lab01 bash -c \
  "ls /var/lib/postgresql/18/main/pg_wal/archive_status/*.ready 2>/dev/null | wc -l"
docker exec rbpg-lab01 bash -c "ls /var/lib/postgresql/18/main/pg_wal/archive_status/ | head -4"

$ count the .ready files, then list the archive_status directory

18

00000001000000010000001B.done
00000001000000010000001C.done
00000001000000010000001D.done
00000001000000010000001E.done

Task 4 — Watch pg_wal exceed its limit

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW max_wal_size;"
docker exec rbpg-lab01 bash -c "ls /var/lib/postgresql/18/main/pg_wal/0000* | wc -l"

for i in $(seq 1 12); do
  docker exec -u postgres rbpg-lab01 psql -X -d lab19 -c \
    "INSERT INTO t SELECT g, repeat('c',200) FROM generate_series(1,200000) g;" > /dev/null
done
docker exec -u postgres rbpg-lab01 psql -X -c "CHECKPOINT;"
sleep 3

docker exec rbpg-lab01 bash -c "du -sh /var/lib/postgresql/18/main/pg_wal"
docker exec rbpg-lab01 bash -c \
  "ls /var/lib/postgresql/18/main/pg_wal/archive_status/*.ready | wc -l" | tee "$LAB/retention.txt"

Keep writing, and measure again:

$ generate WAL, CHECKPOINT, then measure pg_wal and the .ready queue

 max_wal_size 
--------------
1GB
(1 row)

segments before: 31
segments after:  63
pg_wal size:     1009M
.ready queued:   56

after more writing and a CHECKPOINT:
segments:    88
pg_wal size: 1.4G  (max_wal_size is 1GB)
.ready:      88

Task 5 — Repair, and watch it drain

docker exec -u postgres rbpg-lab01 psql -X -c \
  "ALTER SYSTEM SET archive_command = 'test ! -f /archive/%f && cp %p /archive/%f';"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"
sleep 25

docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;"
docker exec rbpg-lab01 bash -c \
  "ls /var/lib/postgresql/18/main/pg_wal/archive_status/*.ready 2>/dev/null | wc -l"
docker exec rbpg-lab01 bash -c "ls /archive | wc -l"

$ fix archive_command, reload, wait, then re-check

 archived_count |    last_archived_wal     | failed_count 
----------------+--------------------------+--------------
          113 |    00000001000000010000008B |           21
(1 row)

.ready remaining: 0
files in /archive: 113

No restart, no downtime, no lost segments. Every segment the server had been holding was archived in order, because they were all still on disk waiting.

That is the redeeming property of this failure mode: as long as the disk holds out, nothing is lost. Fixing the archive within the disk’s headroom recovers the full history.

Task 6 — Why the command starts with a test

docker exec rbpg-lab01 bash -c '
  F=$(ls /archive | head -1)
  echo "file: $F"
  echo CORRUPT > /tmp/fake
  cp /tmp/fake /archive/$F && echo "plain cp SUCCEEDED"
  ls -l /archive/$F
' | tee "$LAB/overwrite.txt"

$ cp a small file over an existing archived segment

file: 00000001000000010000001B
plain cp SUCCEEDED
-rw------- 1 postgres postgres 8 Aug 28 06:01 /archive/00000001000000010000001B

docker exec rbpg-lab01 bash -c '
  F=$(ls /archive | sed -n 2p)
  test ! -f /archive/$F && cp /tmp/fake /archive/$F
  echo "exit status with guard: $?"
  ls -l /archive/$F
' | tee -a "$LAB/overwrite.txt"

$ the same overwrite attempt through the test ! -f guard

exit status with guard: 1
-rw------- 1 postgres postgres 16777216 Aug 28 05:59 /archive/00000001000000010000001C

Sixteen megabytes of irreplaceable WAL, gone, with a success exit status. The guarded form refused and returned 1, which the archiver correctly treats as a failure and retries.

Task 7 — archive_timeout

An archive is only as current as the last segment that filled. A quiet database can leave the most recent transactions unarchived indefinitely.

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET archive_timeout = '10s';"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -d lab19 -c "INSERT INTO t VALUES (999999,'one tiny row');"
sleep 15

docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT archived_count, last_archived_wal, last_archived_time FROM pg_stat_archiver;"

$ set archive_timeout to 10s, insert one row, wait

 archived_count |    last_archived_wal     |      last_archived_time       
----------------+--------------------------+-------------------------------
          177 | 0000000100000001000000CB | 2026-08-28 06:01:28.055987+00
(1 row)

archive_timeout forces a segment switch after the given interval if any WAL has been written. That bounds your recovery point objective: with archive_timeout = 60s, a total loss of the primary loses at most one minute of transactions.

The cost is a partly-filled 16 MB segment archived every interval, whether it contains one row or fifteen megabytes. On a quiet database with a 60-second timeout that is 16 MB per minute — roughly 23 GB a day — of mostly empty files. Compression helps enormously here, and it is exactly the case wal_compression from Lab 15 does not address, since these segments are zero-padded rather than full of page images.

Set it from your RPO, and size the archive for the result.

Validation

test -s "$LAB/baseline.txt"  && echo "OK baseline"
test -s "$LAB/failure.txt"   && echo "OK failure"
test -s "$LAB/retention.txt" && echo "OK retention"
test -s "$LAB/overwrite.txt" && echo "OK overwrite"

# The monitoring check this whole lab argues for:
docker exec rbpg-lab01 bash -c \
  "ls /var/lib/postgresql/18/main/pg_wal/archive_status/*.ready 2>/dev/null | wc -l"
docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT failed_count, last_failed_time,
         now() - last_archived_time AS archive_lag
  FROM pg_stat_archiver;"

Questions to answer without looking anything up:

1. Archiving has been failing for three days. Which transactions were lost?
2. max_wal_size is 1 GB and pg_wal is 40 GB. Name two causes.
3. Why must archive_command refuse to overwrite an existing file, given that the server only asks it to archive each segment once?
4. Your archive_command is cp %p /mnt/nfs/%f. The NFS mount goes away and cp returns 0 because the path became a local directory. What happens?
5. Your RPO is five minutes and the database is quiet overnight. Which setting matters, and what does it cost?

Expected Outcome

You have enabled archiving, broken it, watched pg_wal grow past max_wal_size to 1.4 GB with 88 segments queued, repaired it with a reload, and watched the whole backlog drain with nothing lost.

The three things to carry away:

# The cheapest archive health check, no connection required:
ls $PGDATA/pg_wal/archive_status/*.ready 2>/dev/null | wc -l

-- The database-side version, and the one to alert on:
SELECT failed_count, last_failed_wal, last_failed_time,
       now() - last_archived_time AS archive_lag
FROM pg_stat_archiver;

And the rule: archive_command must never overwrite, must return non-zero on any failure, and must have made the data durable before it returns zero. If you are writing it yourself, you are probably about to get one of those wrong.

Troubleshooting

archive_mode will not change with a reload. It is postmaster context and needs a restart. archive_command is sighup and does not. This asymmetry is why an archiving change is planned as a restart even though most of it is a reload.

pg_stat_archiver.failed_count is climbing and the log says nothing useful. The server records the command’s exit status, not its stderr. Make the command log its own failures, or run it by hand as the postgres OS user on the host to see the real error — usually permissions or a missing directory.

pg_wal grew past max_wal_size and checkpoints look healthy. Correct and expected: segments that have not been archived are retained regardless of max_wal_size, which is a pacing target rather than a cap. This lab drove it to 1.4 GB and 88 queued segments deliberately.

The backlog does not drain after the repair. The archiver retries the oldest .ready file first and works forward. Watch the count in pg_wal/archive_status/ fall rather than watching the directory size, which lags.

The archive contains a corrupt or truncated segment. The command overwrote an existing file. archive_command must test first and refuse — test ! -f dest && cp src dest — because silently overwriting an archived segment destroys the only copy of it.

archive_command returns zero and the data is not durable. A plain cp returns before the data reaches the disk. The documentation’s cp example is illustrative, not a recommendation; a real command syncs, or uses a tool that does.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET archive_mode;"
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET archive_command;"
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET archive_timeout;"
docker exec rbpg-lab01 pg_ctlcluster 18 main restart
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab19;"
docker exec rbpg-lab01 rm -rf /archive /tmp/fake

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW archive_mode;"

Do not skip the restart. archive_mode is a postmaster parameter, so resetting it takes effect only when the server starts again — and a cluster left with archiving enabled and no archive is precisely the condition this lab spent an hour demonstrating.

Production notes

• Alert on failed_count increasing and on archive_lag. A command that fails loudly is easy; a command that succeeds and archives nothing, or archives slower than WAL is produced, is the one that fills the volume.
• The cheapest health check needs no database connection at all: ls $PGDATA/pg_wal/archive_status/*.ready | wc -l. Put it in host monitoring, because it still works when the database does not.
• archive_command must never overwrite, must return non-zero on any failure, and must have made the data durable before returning zero. Getting all three right by hand is difficult, which is the argument for a purpose-built tool.
• archive_timeout bounds the RPO for a quiet cluster, at the cost of a full segment written per interval regardless of how little is in it. Choose it from the recovery point objective, not from taste.
• A failing archive is a disk-full incident on a timer. The moment failed_count starts climbing, the clock to a PANIC on the WAL volume is running.

What You Learned

• archive_mode needs a restart; archive_command does not. One file, two contexts.
• pg_wal grows without limit while archiving fails, past max_wal_size, until the volume is full and the cluster PANICs.
• archive_status/*.ready is the queue, and counting it is the cheapest possible health check.
• The backlog drains by itself once the command works — nothing is lost, provided the volume held out.
• The command must test before it copies. Overwriting an archived segment destroys the only copy.
• A zero exit status is a durability promise. cp returning is not the same as the bytes being on disk.