Hi Justin, thanks for your anwer!
My question is not so much about "can i drop a certain replication slot",
more about "does this publication still have any replication slots?". Or, if you will: "what's the publication for this replication slot?".
I've double checked the views that you suggested, and I found that I can relate the WAL sender processes to replication slots through pg_replication_slots.active_pid .
I've also looked into replication origins.
But I can't find a link to the publication. And that's what I need to know.
Cheers,
Willy-Bas
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:
START_REPLICATION SLOT "my_slot" LOGICAL 5DD1/3E56D360 (proto_version '1', publication_names '"my_publication"')
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?
--
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
--
