Find what a replication slot is holding back, and what it costs to release it.
promptread-only tools
replication-slot-review([connection])The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Review replication slots on connection shop_prod and what they are holding back. An unconsumed slot is the failure mode that presents as something else. It holds back the xmin horizon, so vacuum cannot remove dead tuples anywhere in the cluster; and it pins WAL until the disk fills. Both symptoms get diagnosed as bloat, or as a disk problem, and neither diagnosis leads anywhere. 0. Call checkPrivileges: a role without stats access sees less of replicationSlots and of currentActivity than this review needs. pg_stat_replication is restricted PER ROW rather than refused, so without it replicationStats shows the senders existing with their columns null -- which reads like an idle replica and is a permission answer. 0a. Call replicationStats. A slot says what is RETAINED, in bytes; this says how far behind the consumer is, in seconds, and the two answer different questions. A slot with large retained_wal_bytes and a small replay_lag is a consumer that is keeping up through a burst; the same bytes with a growing replay_lag is one that is not, and only the second is a problem you can fix by making the consumer faster. Read the lag as documented: it reverts to NULL a short time after a standby has entirely caught up and WAL activity stops, so null on an idle replica means caught up, and a non-null value there is the last measurement rather than the current state. 1. Call replicationSlots. For each slot read whether it is active, how far its restart_lsn trails the current WAL position, and its wal_status, which is a progression rather than a severity. reserved means the WAL it needs is within max_wal_size. extended means past max_wal_size but still retained -- by this slot OR by wal_keep_size, so check that setting before concluding the slot is the retainer. unreserved means the server has stopped retaining for it: some of the WAL it needs is due to be removed at the next checkpoint, which is what max_slot_wal_keep_size does when it is set. That is the last warning before the slot is unusable, and it is still reversible -- a consumer that catches up returns the slot to reserved or extended. lost means the WAL is already gone and the consumer cannot resume; that slot is dead and only rebuilding its consumer fixes it. safe_wal_size is the budget between here and lost: bytes that can still be written before this slot is in danger. Read it rather than estimating from retained_wal_bytes and a WAL rate. Null means max_slot_wal_keep_size is -1 and nothing bounds the retention at all, which is a different and worse answer than a large number. Three more fields answer questions you would otherwise estimate. inactive_since dates the problem, which beats inferring it from WAL volume: a slot inactive for an hour and one inactive since a deploy three weeks ago are different findings with the same retained_wal_bytes. One caveat that matters in exactly the case you care about: once a slot becomes invalid this value is never updated again, so on an invalidated slot it dates the invalidation and not the problem. Read it against invalidation_reason rather than alone. conflicting and invalidation_reason say the server retired the slot itself rather than it merely falling behind, and they do not cover the same slots: conflicting is always null for a physical slot, while invalidation_reason is set for both kinds -- so on a physical slot invalidation_reason is the only one of the two that answers. wal_removed, rows_removed, wal_level_insufficient and idle_timeout are each a different cause and a different fix, and none of them is repaired by giving the consumer more time. And for a logical slot the spill and stream counters say whether decoding is fitting in memory: heavy spill_txns and spill_bytes mean large transactions are being written to disk before they are sent, which is a logical_decoding_work_mem finding rather than a slow consumer. 2. An inactive slot with growing lag is the finding. Before anything else, establish whose it is: listTopology and verifyTopology for physical replicas, listSubscriptions and listPublications for logical ones. A slot with no owner anybody recognises is the common case and the easy one. If the owner is a subscriber you can reach, call subscriptionStats on THAT connection -- not this one -- to separate a subscriber that is stuck from one that is merely gone: a stuck apply worker shows an error count or a table still copying, while a decommissioned one shows nothing at all and the slot is simply abandoned. 3. Measure how fast it grows before deciding how urgent it is. checkpointStats reports WAL volume, and serverSettings carries max_slot_wal_keep_size -- if that is set, PostgreSQL will invalidate the slot rather than fill the disk, which trades a broken replica for a live primary. If it is unset, nothing bounds the growth. 4. Call wraparoundStatus. The same held xmin horizon also stops freezing, and if wraparound headroom is shrinking that outranks the disk: one ends in a slow server, the other in a cluster that stops accepting writes. 5. Report the slot, its owner, what it is holding, and how long the disk has at the current rate. Dropping a slot is irreversible for its consumer: say exactly what would have to be rebuilt, and prefer fixing or decommissioning the consumer over dropping the slot underneath it.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "replication-slot-review",
"arguments": {
"connection": "shop_prod"
}
}
}