Subject: Re: find replication slots that "belong" to a publication

From: Justin <[email-masked]>
Date: Sun, 6 Apr 2025 09:36:03 -0400

On Fri, Apr 4, 2025 at 4:58 AM Willy-Bas Loos <[email-masked]> 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:
> 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?
>
> --
> 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

https://www.postgresql.org/docs/13/view-pg-replication-slots.html
https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP

Hope this answers your question

Justin

---

From: Willy-Bas Loos <[email-masked]>
Date: Mon, 7 Apr 2025 12:16:13 +0200

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

On Sun, Apr 6, 2025 at 3:36 PM Justin <[email-masked]> wrote:

>> 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.
>>
>> START_REPLICATION SLOT "my_slot" LOGICAL 5DD1/3E56D360 (proto_version
>> '1', publication_names '"my_publication"')
>>

-- 
Willy-Bas Loos

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 07 Apr 2025 15:31:06 +0200

On Mon, 2025-04-07 at 12:16 +0200, Willy-Bas Loos wrote:
> 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 don't think that there is a connection between a publication and a
replication slot.  That connection is only made when a subscriber connects
and runs the START_REPLICATION command [1] and specifies the "pgoutput"
plugin with the "publication_names" option [2].

I don't think you can see that information reflected in a system view
on the primary.  You'd have to query "pg_subscription" on the standby.

Yours,
Laurenz Albe

 [1]: https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL
 [2]: https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS

---

From: Willy-Bas Loos <[email-masked]>
Date: Mon, 7 Apr 2025 22:32:55 +0200

Hi Laurenz,

Thanks for answering!
I find it very strange, because the publication is needed to make a
subscription, which makes the slot.
Thanks for looking into it and helping me understand.

Cheers!
Willy-Bas Loos

On Mon, Apr 7, 2025 at 3:31 PM Laurenz Albe <[email-masked]>
wrote:

> relate
>  [1]:
> https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL
>  [2]:
> https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS
>

-- 
Willy-Bas Loos

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 08 Apr 2025 07:53:09 +0200

On Mon, 2025-04-07 at 22:32 +0200, Willy-Bas Loos wrote:
> I find it very strange, because the publication is needed to make a subscription, which makes the slot.

Right, but that information is only on the subscriber.

Yours,
Laurenz Albe