Remove a subscription and clean up an orphaned logical slot on the publisher

Command

-- Subscriber: while the publisher is unreachable DROP SUBSCRIPTION cannot drop the remote slot and fails; detach the slot first:
ALTER SUBSCRIPTION sub_app DISABLE;
ALTER SUBSCRIPTION sub_app SET (slot_name = NONE);
DROP SUBSCRIPTION sub_app;
-- Publisher: find and drop orphaned logical slots (including tablesync slots pg_<suboid>_sync_<relid>_<sysid>):
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots WHERE slot_type = 'logical' AND NOT active;
SELECT pg_drop_replication_slot('sub_app');

What it does

Drops the subscription without touching the remote slot (slot_name = NONE); then finds and removes logical slots on the publisher that no longer have a consumer, plus leftover tablesync slots.

When to use it

When a subscription must be removed while the publisher is unreachable, after a migration is finished, or when a logical slot with active = false is accumulating WAL on the publisher.

Caution

An orphaned logical slot retains both WAL and catalog_xmin: the disk fills and system catalogs bloat. However, dropping the slot makes it impossible for that subscription to resume; before dropping, verify that no subscriber (including one on another server) uses this slot. An active slot cannot be dropped; the connected walsender must be terminated first.