Slots that say why they died and when

Two different bad states that looked like one

A replication slot can go wrong in two unrelated ways, and before 17 the view reported them with the same vocabulary.

The first is invalidation. The server decided the slot could no longer be honoured and cut it loose, which on a supported version can happen because the write-ahead log it needed was removed under max_slot_wal_keep_size, or because a standby’s recovery conflicted with what a logical slot still needed. The slot survives as a row, permanently unusable. The wal_status column reports lost and says nothing about which of those happened.

The second is abandonment. The consumer has simply gone away. The slot is still perfectly valid, it is still holding log segments and, if it is logical, still holding back the catalog cleanup horizon, and it will go on doing that until the disk fills. Before 17 the only evidence was active being false, which is also true of a consumer that reconnects every few seconds and happened to be between connections when you looked.

PostgreSQL 17 added a column for each. invalidation_reason names the cause when a slot has been invalidated, and inactive_since records when the slot last had a consumer attached. Both are listed under Streaming Replication and Recovery in the PostgreSQL 17 release notes. Nothing was renamed, so queries written for 16 keep working; what they cannot do is ask the new questions.

Killing the same slot on both servers

The fixture creates a physical slot that reserves log immediately, then generates more log than the cluster is willing to keep for it. Both servers are configured identically, with a small retention ceiling so the invalidation happens in seconds rather than hours.

SELECT slot_name FROM pg_create_physical_replication_slot('shipping_standby', true);
CREATE TABLE wal_churn (churn_id bigint, filler text);
INSERT INTO wal_churn SELECT g, repeat('w', 200) FROM generate_series(1, 400000) AS g;
SELECT pg_switch_wal() IS NOT NULL AS switched;
SELECT pg_switch_wal() IS NOT NULL AS switched;
SELECT pg_switch_wal() IS NOT NULL AS switched;
SELECT pg_switch_wal() IS NOT NULL AS switched;
CHECKPOINT;
CHECKPOINT;

    slot_name     
------------------
 shipping_standby
(1 row)

CREATE TABLE
INSERT 0 400000
 switched 
----------
 t
(1 row)

 switched 
----------
 t
(1 row)

 switched 
----------
 t
(1 row)

 switched 
----------
 t
(1 row)

CHECKPOINT
CHECKPOINT

On 16 the slot is dead and the view is not forthcoming about it.

SELECT slot_name, slot_type, active, wal_status, conflicting
FROM pg_replication_slots;

    slot_name     | slot_type | active | wal_status | conflicting 
------------------+-----------+--------+------------+-------------
 shipping_standby | physical  | f      | lost       | 
(1 row)

conflicting is null rather than false, because on 16 that column only ever has a value for logical slots on a standby. So the row says the slot is lost, does not say why, and offers a column that looks like it might explain and does not apply. The reason is in the server log, which is a different system, usually a different retention policy, and rarely in the hands of whoever is looking at this view.

Asking 16 for the columns that would answer it:

SELECT slot_name, invalidation_reason, inactive_since FROM pg_replication_slots;

ERROR:  column "invalidation_reason" does not exist
LINE 1: SELECT slot_name, invalidation_reason, inactive_since FROM p...
                          ^

On 17 the same dead slot explains itself.

SELECT slot_name,
       active,
       wal_status,
       invalidation_reason,
       date_trunc('second', now() - inactive_since) AS idle_for
FROM pg_replication_slots;

    slot_name     | active | wal_status | invalidation_reason | idle_for 
------------------+--------+------------+---------------------+----------
 shipping_standby | f      | lost       | wal_removed         | 00:00:10
(1 row)

wal_removed is the answer, and it took one query rather than a log search. Note that inactive_since has a value even though this slot has never had a consumer at all: it is set when the slot becomes inactive, and a slot created and never used is inactive from birth. That is the right behaviour for the alert you want, and it is worth knowing before you write a rule that assumes a null means “never been used”.

What the reasons tell you to do

The value of the column is that its three answers point at three different problems, and a null at none.

• wal_removed means the retention ceiling did its job and the consumer was too far behind. The slot cannot be resumed; the consumer has to be rebuilt from a fresh copy. The question worth asking afterwards is whether the ceiling is too low or the consumer too slow.
• rows_removed means rows the slot still needed have been removed, and it is set on logical slots only. In practice that is a decoder on a standby losing a race with the primary’s vacuum, and the lever is hot_standby_feedback on the standby, with the bloat cost that implies upstream.
• wal_level_insufficient means the primary’s wal_level is not high enough for logical decoding, and it too is logical-only. That is a configuration change somebody made, and the slot is collateral damage from it.

A null in the column on an otherwise healthy row is the ordinary case and means nothing is wrong. Read wal_status first and the reason second; the reason only ever has a value once the slot is already beyond saving.

Two alerts this makes writable

Abandonment is the one worth adding first, because it is the failure that takes a cluster down rather than a replica. A slot whose inactive_since is older than a threshold you choose, and whose retained log is growing, is the classic disk-filling slot, and on 17 the first half of that sentence is a column rather than an inference from repeated polling.

Invalidation is the second, and it deserves to be a separate rule rather than a severity on the first. A slot that has been invalidated is already broken and nothing is going to get worse; what it needs is somebody to decide whether to rebuild the consumer or drop the slot. Paging for it at three in the morning is how a team learns to ignore slot alerts.

Both rules read better next to safe_wal_size, which counts down the headroom before invalidation happens and is the number that gives you warning rather than news. The replication lag and slot health guide works through the full set of columns and what each one is for.

What the columns cost

Nothing gates them. pg_replication_slots is assembled from the slots in shared memory when you query it, there is no setting to switch on, and no restart is involved. Both new columns are values the server was already tracking internally and had no field to publish.

The one operational decision nearby is max_slot_wal_keep_size itself, which is what turns an unbounded disk risk into a broken consumer. Unset, a slot can consume the filesystem and stop the primary. Set, the slot is invalidated instead and, from 17 onward, the row tells you that is what happened.