find replication slots that "belong" to a publication

postgres 13 BTW

On Fri, Apr 4, 2025 at 10: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

--
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

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 <zzzzz.graf@gmail.com> wrote:

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

--
Willy-Bas Loos

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]https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL and specifies the "pgoutput"
plugin with the "publication_names" option [2]https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS.

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

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 <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
[2]:
https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS

--
Willy-Bas Loos

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&gt;
 [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&gt;

--
Willy-Bas Loos

--
Adrian Klaver
adrian.klaver@aklaver.com

I can't think of a way to link publication to a replication slot.... I
agree using pg_state_activity is the only way to do that however you don't
know if the subscriber is momentary disconnected due network error or
disconnected due to an error in replication such as duplicated key

SELECT true from pg_stat_activity where query ilike (SELECT '%' ||
pubname::text || '%' from pg_publication);

PG will prevent dropping a publication that are in use. How PG knows that
I don't know

The publication is used to publish the list of tables that are published
and the subscriber checks pg_pub_rel to make sure it has the
necessary tables to start receiving data

It is not necessary to have publication to create a logical replication
slot, which PG will stream all data changes. Several tools create logical
replication slots with no publication..

On Mon, Apr 7, 2025 at 4:44 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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