Thread: find replication slots that "belong" to a publication

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

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:

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 

On Mon, 2025-04-07 at 12:16 +0200, Willy-Bas Loos wrote:
> 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.

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

On 4/7/25 13:32, Willy-Bas Loos wrote:
> Hi Laurenz,
>
> Thanks for answering!
> I find it very strange, because the publication is needed to make a
> subscription, which makes the slot.

 From here:

https://www.postgresql.org/docs/current/logical-replication-subscription.html

"A subscription defines the connection to another database and set of
publications (one or more) to which it wants to subscribe."

and here:

"PUBLICATION publication_name [, ...]

     Names of the publications on the publisher to subscribe to.
"

Finding the subscriptions for a given publication and deleting those
slots may break the subscription on the receiving side if it is looking
for data from more then one publication.

> Thanks for looking into it and helping me understand.
>
> Cheers!
> Willy-Bas Loos
>
>
> On Mon, Apr 7, 2025 at 3:31 PM Laurenz Albe <laurenz.albe@cybertec.at
> <mailto:laurenz.albe@cybertec.at>> wrote:
>
>     On Mon, 2025-04-07 at 12:16 +0200, Willy-Bas Loos wrote:
>      > 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.
>
>     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 <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 <https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS>
>
>
>
> --
> Willy-Bas Loos

--
Adrian Klaver
adrian.klaver@aklaver.com