PostgreSQL replica stops streaming WAL after pg_repack on primary; replication slot reports missing WAL

Environment

Primary PostgreSQL 13.9 on Ubuntu 20.04 with streaming replication to a hot standby using physical replication slots. pg_repack 1.4.9 run on large table during low-traffic window. WAL archiving to file system enabled. Replica on same LAN with identical PostgreSQL minor version.

Symptoms

After pg_repack completed, replica's recovery process logs 'requested WAL segment ... has already been removed', replication slot on primary shows 'confirmed_flush_lsn' lagging and pg_stat_replication shows replication state 'streaming' then 'replication terminated'. Replica stops applying new WAL and falls behind. No manual base backup was performed after pg_repack.

Observations

Primary's pg_wal still contains newer segments, but some earlier WAL files referenced by the replica's last received LSN are no longer present in pg_wal and not in archive directory. pg_repack was run with default options (not using --preserve-row-order) and completed successfully. Archive_command reported some 'permission denied' messages during the repack window (log entries show copy to archive failed for a few files). pg_repack does not alter physical replication state but does create lots of WAL while rebuilding tables.

Already attempted

Checked archive directory for missing WALs and filesystem permissions; ensured archive_command is functional; tried restarting replica, which then fails to fetch older WAL segments. Considered promoting replica but it's behind. Restarts of primary didn't re-create removed WAL segments. No base backup taken post-repack.

Question

How can I recover the replica that stopped applying WAL because referenced WAL segments were removed, and what precautions should I take when running heavy WAL-producing operations like pg_repack to avoid replication slot invalidation?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Archive_command failures (permissions or transient I/O errors) prevented missing WALs from being stored in archive during the heavy WAL generation by pg_repack. 90%

WAL segments required by replica were recycled by primary because wal_keep_size/wal_keep_segments and replication slot retention thresholds were exceeded. 75%

Disk pressure on primary triggered WAL cleanup even though replica had not consumed earlier segments (insufficient WAL retention configuration). 50%

Summary

Replica failure occurs when the replica requests WAL segments that are no longer available on the primary or in archive — commonly caused by WAL removal due to retention settings, disk-space-triggered cleanup, or failed archiving while the replica was slow. pg_repack can produce large volumes of WAL, temporarily accelerating WAL recycling; if archive_command failed for some WALs and hot standby lagged, the required WAL bytes can be lost. Recovery requires a new base backup or restoring the missing WALs from archive; simply restarting streaming won't resurrect removed segments.

Suggested Fixes

Diagnostic step 1

Confirm which WAL segments are missing by inspecting primary's pg_wal and archive directory and checking replica's last WAL LSN in recovery logs; do not attempt to manipulate WAL files on disk without backups.

Diagnostic step 2

If missing WALs are irrecoverable, take a fresh base backup from primary (pg_basebackup) and reinitialize the replica; this is the safest recovery path when required WALs are gone.

Diagnostic step 3

Before future heavy WAL operations: ensure archive_command is reliable and monitored, increase wal_keep_size (or enable sufficient retention), and consider running pg_repack with chunking and lower maintenance_work_mem to spread WAL over a longer period.