On Fri, Apr 4, 2025 at 4:58 AM Willy-Bas Loos <willybas@gmail.com> wrote:
Hi!
I'm looking for a way to find out if there are still replication slots active for a publication before dropping the publication in an automated way. The idea is that the publication is thought not to be needed any longer, but we want to make sure.
I'm having trouble finding a link between a publication, the subscriptions and the replication slots. Especially when you don't want to make assumptions about any subscriber nodes, so you are restricted to the publisher node.
The best I could find was a query listed in pg_stat_activity that lists the slot name and the publication name:
I don't like the idea of using string manipulation on such query strings to get the information I need. Postgres must have a way to compose this query.
Can anyone tell me a way to find replication slots that belong to a publication?
--
Willy-Bas Loos
Hi Willy-Bas,
Logical replication slots appear in the views pg_stat_replication and pg_replication_slots. Both views have the information you are looking for, the difference is pg_stat_replication shows only the active slots. Keep in mind Temporary Slots only live for the length of the session that created it; the slot will appear in both views.
The bigger issue I think you are trying to address is when can a slot be dropped safely. Once a logical replication slot is dropped there is no recovery of the slot's lsn position. Probably the best way to decide if a slot has been abandoned is how far behind it is. The pg_wal_lsn_diff can be used to figure out how far behind a slot is
