Surveiller le retard de réplication et la fragmentation des emplacements

Logical replication slots make subscribers durable: the primary will retain WAL until every active slot has confirmed it consumed the changes. That guarantee is the same mechanism that can fill your data disk.

• A slot that is inactive (consumer down, network split, slow apply) pins WAL forever.
• WAL accumulates in pg_wal/, the partition fills, and the primary can stop accepting writes.

This lesson is about observing that retention early, before it becomes an outage. The core question is always: how far behind is each slot, in bytes?

Every slot is visible in pg_replication_slots. The columns that matter for bloat are active, restart_lsn, and on PostgreSQL 13+ the retention bookkeeping columns wal_status and safe_wal_size.

• restart_lsn — the oldest LSN the slot still needs; WAL before it can be recycled.
• active — whether a consumer is currently connected.
• wal_status — reserved, extended, unreserved, or lost.

The single most useful number is the gap between the current WAL write position and each slot's restart_lsn. That difference is the WAL the primary is forced to keep just for that slot.

LSNs are pg_lsn values; subtracting two of them yields bytes. Wrap it with pg_size_pretty() for human-readable output.

Two different lags are often confused:

• Replication lag — how far behind the consumer is right now. Visible on the primary in pg_stat_replication as the gap between sent_lsn, flush_lsn, and replay_lsn.
• Slot bloat — how much WAL is physically retained because of a slot, whether or not a consumer is connected.

A healthy, connected replica can have near-zero bloat but transient lag. A disconnected slot has zero live lag (no row in pg_stat_replication) yet unbounded bloat. Always check both views.

For currently connected standbys and logical subscribers, pg_stat_replication exposes per-connection lag in bytes. Compute the write/flush/replay distances against the sender's current WAL position.

The state column (streaming, catchup) and the time-based write_lag/flush_lag/replay_lag intervals tell you whether the consumer is keeping up.

Since PostgreSQL 13, max_slot_wal_keep_size caps how much WAL a slot may force the primary to retain. The wal_status column reports where each slot sits relative to that cap:

• reserved — within max_wal_size, safe.
• extended — exceeding max_wal_size but still under max_slot_wal_keep_size.
• unreserved — past the cap; WAL is being removed and the slot may soon be invalidated.
• lost — required WAL was already removed; the slot is permanently broken and its consumer must reseed.

safe_wal_size tells you how many bytes can still be written before the slot risks becoming lost. A small or negative value is an urgent alert.

The defensive trade-off in logical replication architectures: do you protect the subscriber's durability or the primary's uptime? Setting max_slot_wal_keep_size chooses primary uptime — a stuck slot gets invalidated instead of filling the disk.

• Default -1 means unlimited retention (the dangerous default).
• A concrete value (e.g. 10GB) bounds worst-case bloat per slot.

If a slot is invalidated, its subscriber loses its place and must be recreated and resynced — acceptable for ephemeral pipelines, not for a critical replica.

Logical slots carry two LSNs worth distinguishing:

• restart_lsn — the point from which decoding would have to restart; this is what pins WAL retention.
• confirmed_flush_lsn — the LSN the subscriber has acknowledged as durably applied.

The gap between confirmed_flush_lsn and restart_lsn exists because decoding must restart from the beginning of the oldest running transaction. A long-running transaction on the primary holds restart_lsn back even when the subscriber is fully caught up — a classic cause of stubborn bloat with no apparent lag.

For dashboards and alert rules, combine retention, activity, and WAL status into one row per slot. Flag any slot that is inactive while retaining significant WAL, or whose wal_status has left reserved.

Wire this query's severity output into your monitoring system (Prometheus exporter, cron + alert, etc.).

When bloat persists on a logical slot with a caught-up subscriber, the culprit is usually the oldest in-progress transaction holding the catalog xmin. Inspect catalog_xmin on the slot, then hunt the offending backend.

pg_replication_slots.catalog_xmin is the oldest transaction whose catalog changes must remain decodable. Long-lived transactions (forgotten BEGIN, idle-in-transaction sessions) keep it pinned.

When a subscriber is gone for good (decommissioned pipeline, failed-over replica), its slot must be dropped or it will retain WAL indefinitely. A slot can only be dropped while inactive.

• Verify active = false first; dropping an active slot errors out.
• Use pg_drop_replication_slot() on the primary that owns the slot.

This is the fastest way to recover a disk that is filling because of a dead consumer — but it permanently breaks that subscriber, so confirm it is truly abandoned.

A logical replication slot is retaining 40 GB of WAL. The subscriber is connected, pg_stat_replication shows replay_lag near zero, and state is streaming. What is the most likely cause of the retained WAL?

You now have a full monitoring playbook for replication slot bloat:

• Retained WAL in bytes = pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) per slot — the number to alert on.
• Two distinct signals: live lag in pg_stat_replication vs physical retention in pg_replication_slots. A dead slot has no live lag but unbounded bloat.
• wal_status (reserved → extended → unreserved → lost) and safe_wal_size show how close a slot is to invalidation.
• max_slot_wal_keep_size bounds worst-case bloat, trading subscriber durability for primary uptime.
• Stubborn bloat with zero lag usually means a long-running transaction pinning restart_lsn/catalog_xmin — find it in pg_stat_activity.
• Orphaned slots must be dropped with pg_drop_replication_slot() once confirmed inactive.