public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Willy-Bas Loos <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: find replication slots that "belong" to a publication
Date: Mon, 7 Apr 2025 17:00:00 -0400
Message-ID: <CALL-XeNKTVhEJ61=MAQgnzTZPV1hEEsScPSWwGWxftWC4pYDTg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHnozTg9sadVff9WSFSBi=f6YhCJ0wPK3+riK441Cdqx-Jj+_g@mail.gmail.com>
<CALL-XeNax53+uja36oX8WS4wX0-MdFJio5SJgpC59XHdC5XAag@mail.gmail.com>
<CAHnozThHzgK1KAMpxPV3r_oonFUEqfXxU3rQ7SDTEAvZRi3t_Q@mail.gmail.com>
<[email protected]>
<CAHnozTi5e4pXo2myty8OOtLOatLwNADh1p8ThNhm3LenUQjzsw@mail.gmail.com>
<[email protected]>
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 <[email protected]>
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 <[email protected]
> > <mailto:[email protected]>> 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-REPLICA...
> <
> https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICA...
> >
> > [2]:
> >
> https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATI...
> <
> https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATI...
> >
> >
> >
> >
> > --
> > Willy-Bas Loos
>
> --
> Adrian Klaver
> [email protected]
>
>
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: find replication slots that "belong" to a publication
In-Reply-To: <CALL-XeNKTVhEJ61=MAQgnzTZPV1hEEsScPSWwGWxftWC4pYDTg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox