Re: find replication slots that "belong" to a publication - Mailing list pgsql-general
| From | Justin |
|---|---|
| Subject | Re: find replication slots that "belong" to a publication |
| Date | |
| Msg-id | CALL-XeNKTVhEJ61=MAQgnzTZPV1hEEsScPSWwGWxftWC4pYDTg@mail.gmail.com Whole thread Raw |
| In response to | Re: find replication slots that "belong" to a publication (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
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..
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 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
pgsql-general by date: