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

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(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)albe(at)cybertec(dot)at
> > <mailto:laurenz(dot)albe(at)cybertec(dot)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(dot)klaver(at)aklaver(dot)com
>
>