Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u1tZo-009xz8-Fg for pgsql-general@arkaria.postgresql.org; Mon, 07 Apr 2025 21:00:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u1tZm-003vtW-7X for pgsql-general@arkaria.postgresql.org; Mon, 07 Apr 2025 21:00:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u1tZl-003vtN-P2 for pgsql-general@lists.postgresql.org; Mon, 07 Apr 2025 21:00:14 +0000 Received: from mail-io1-xd34.google.com ([2607:f8b0:4864:20::d34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u1tZk-003Xif-0m for pgsql-general@lists.postgresql.org; Mon, 07 Apr 2025 21:00:13 +0000 Received: by mail-io1-xd34.google.com with SMTP id ca18e2360f4ac-85e15dc8035so125003539f.0 for ; Mon, 07 Apr 2025 14:00:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744059611; x=1744664411; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=qQIXURBoTkwgs38tpe9BWZP/+u9KIBWXpnhBzqhNOyM=; b=ZXXy0HpG/FtyxnLnUYEC/fYzdAwYxdXPrVCG/eux5UM8UOw4bKGYQGpF9FIt3MMan7 YQ+kWBXKzkTIjHaGxhF1NDhY8ImiheWmGKrsrBccTzUnsx+S12O+dgU52DmEmyw36MKZ ni0c3u9ddzPJ6kCYliacmeYAUnk6z0YHU+mWBn/HyxoIV0Y7tClpKmtMt2qrTWpOiEWD 3fgc7OWrHu/ivttX1WQ+YudPtpQPrGKBQARYQCO9LpyZ6aEU1rIO9zD95a6mtCBqgwWK +UvUAUGi/9Nexe89ApWVITBsohqhVhmgjZTO2qyzjUj8jeBr6wA7hhkcT8UVH+QIrGYi 9ikA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744059611; x=1744664411; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=qQIXURBoTkwgs38tpe9BWZP/+u9KIBWXpnhBzqhNOyM=; b=exEanNiRv9z33xQNaEyxyfOrgNGIdvDIpNI0ZhinkAOfpUp+6Ovfwzt1Q2hpXfqGYc CQLISPiD9DQjmiIj0RBf4SXhl+JvCUtQW9nU6PmCb8TJpcNuDuCg+efXc2LRt846JPWW dqAsP1kEUMgmeExoqGIFet2IumyLVNz0gwTiWTrkcd/50M4o14yuaLCHqRAMsYlRPkc0 Beeu4XYpG3HOGWsAPIgU5N8WBZbkpOJxYaoeCKye9AN+FB864CMhENZvs8FbpmfJHq2j X0b/wJDEmj91ZuqiJP9F2OdhIiGhVL7hi7r4Y90soNBlZrAxJ2nuoy8oR4FKqyOAS/Ce d7+Q== X-Forwarded-Encrypted: i=1; AJvYcCVriFsLhS1kMSHfH3r2F0P9OFnT7detQP3y4sd9iXKf5tRkQYu9n7ZzIjKcCq9ZzL1BbbFK8RvJjuSxIPju@lists.postgresql.org X-Gm-Message-State: AOJu0Yw1k7RPr3Q0gPuSBFDBJLIfryq5lXTgMuAUm0sLcDiE5XdfEPz+ d0H1D1hNJ8R9W6eA/8RRf1kD66RJn4sYdaczwqIhEqrjUjGhYDLgm/+wCZqDlGd9jm+JEm00pTx 2PR7La9LfTjTzKonnERwV9IOyu/4= X-Gm-Gg: ASbGncs3GZw+EuSKtoNr9dCQNXFXlgFLfwsXgaSxtlQzUXa6MSQWe3+rTaFWb/Ndg+C QOYDDzuyOdWL27r3OXimqND6WPakI6FI2BGZL3yrw8vsB8hJwLXge5BAS70G0ecBJO0V6Xl9acI PuEhhUmR07cVXx+ffdZlTWsUoXEQ== X-Google-Smtp-Source: AGHT+IFXaHrtFzRtp15xskDzQsznlbYtpc2lyxq0ElahHlyFcINTmyJRjYLGUiL7rd+YIo0+0WS7ONu9y0o0w5W4OaY= X-Received: by 2002:a05:6602:3792:b0:85b:4128:1805 with SMTP id ca18e2360f4ac-8611b4e1fd3mr1307116939f.10.1744059611607; Mon, 07 Apr 2025 14:00:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Mon, 7 Apr 2025 17:00:00 -0400 X-Gm-Features: ATxdqUFMSAUnfg5gCniHl7VkcRgpy-GInDXMg7n_gK5-5lLfRQdhKinw9Pe-00w Message-ID: Subject: Re: find replication slots that "belong" to a publication To: Adrian Klaver Cc: Willy-Bas Loos , Laurenz Albe , pgsql-general Content-Type: multipart/alternative; boundary="0000000000009dff340632368524" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009dff340632368524 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Adrian Klaver 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=E2=80=AFPM Laurenz Albe > > 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 vi= ew > > 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#PROTOCO= L-REPLICATION-START-REPLICATION-SLOT-LOGICAL > < > https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCO= L-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 > > --0000000000009dff340632368524 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I can't think of a way to link publication to a replic= ation slot....=C2=A0 I agree using pg_state_activity is the only way to do = that however you don't know if the subscriber is momentary=C2=A0disconn= ected due network error or disconnected due to an error in replication=C2= =A0 such as duplicated key

SELECT true from pg_stat_activity where q= uery ilike (SELECT '%' || pubname::text || '%' from pg_publ= ication);

PG will prevent dropping a=C2=A0publication that are in=C2= =A0use.=C2=A0 How PG knows that I don't know

The publication is = used to publish the list of tables that are published and the subscriber ch= ecks pg_pub_rel to make sure it has the necessary=C2=A0tables to start rece= iving=C2=A0 data=C2=A0 =C2=A0

It is not necessary=C2=A0to have publi= cation to create a logical replication slot, which PG will stream all data = changes.=C2=A0 Several tools create logical replication slots with no publi= cation..=C2=A0


On Mon, Apr 7, 2025 at 4:44=E2= =80=AFPM Adrian Klaver <adr= ian.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.

=C2=A0From 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 [, ...]

=C2=A0 =C2=A0 =C2=A0Names 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=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at
> <mailto:laurenz.albe@cybertec.at>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On Mon, 2025-04-07 at 12:16 +0200, Willy-Bas Loos w= rote:
>=C2=A0 =C2=A0 =C2=A0 > My question is not so much about "can i = drop a certain
>=C2=A0 =C2=A0 =C2=A0replication slot",
>=C2=A0 =C2=A0 =C2=A0 > more about "does this publication still = have any replication slots?".
>=C2=A0 =C2=A0 =C2=A0 > Or, if you will: "what's the publica= tion for this replication slot?".
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I've double checked the views that you su= ggested, and I found
>=C2=A0 =C2=A0 =C2=A0that I can relate
>=C2=A0 =C2=A0 =C2=A0 > the WAL sender processes to replication slots= through
>=C2=A0 =C2=A0 =C2=A0pg_replication_slots.active_pid .
>=C2=A0 =C2=A0 =C2=A0 > I've also looked into replication origins= .
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > But I can't find a link to the publicatio= n. And that's what I
>=C2=A0 =C2=A0 =C2=A0need to know.
>
>=C2=A0 =C2=A0 =C2=A0I don't think that there is a connection betwee= n a publication and a
>=C2=A0 =C2=A0 =C2=A0replication slot.=C2=A0 That connection is only mad= e when a subscriber
>=C2=A0 =C2=A0 =C2=A0connects
>=C2=A0 =C2=A0 =C2=A0and runs the START_REPLICATION command [1] and spec= ifies the "pgoutput"
>=C2=A0 =C2=A0 =C2=A0plugin with the "publication_names" optio= n [2].
>
>=C2=A0 =C2=A0 =C2=A0I don't think you can see that information refl= ected in a system view
>=C2=A0 =C2=A0 =C2=A0on the primary.=C2=A0 You'd have to query "= ;pg_subscription" on the standby.
>
>=C2=A0 =C2=A0 =C2=A0Yours,
>=C2=A0 =C2=A0 =C2=A0Laurenz Albe
>
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0[1]:
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/docs/cu= rrent/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT= -LOGICAL <https://www.postgresql.org/docs/current/p= rotocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICA= L>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0[2]:
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/docs/current/p= rotocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS &l= t;https://www.postgresql.org/docs/current/protocol-logical-replic= ation.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS>
>
>
>
> --
> Willy-Bas Loos

--
Adrian Klaver
adrian.klave= r@aklaver.com

--0000000000009dff340632368524--