MIME-Version: 1.0
References: <CAHnozTg9sadVff9WSFSBi=f6YhCJ0wPK3+riK441Cdqx-Jj+_g@mail.gmail.com>
 <CALL-XeNax53+uja36oX8WS4wX0-MdFJio5SJgpC59XHdC5XAag@mail.gmail.com>
 <CAHnozThHzgK1KAMpxPV3r_oonFUEqfXxU3rQ7SDTEAvZRi3t_Q@mail.gmail.com> <f673c080f33805fdf5e3060f8f3338ebc803eb19.camel@cybertec.at>
In-Reply-To: <f673c080f33805fdf5e3060f8f3338ebc803eb19.camel@cybertec.at>
From: Willy-Bas Loos <willybas@gmail.com>
Date: Mon, 7 Apr 2025 22:32:55 +0200
Message-ID: <CAHnozTi5e4pXo2myty8OOtLOatLwNADh1p8ThNhm3LenUQjzsw@mail.gmail.com>
Subject: Re: find replication slots that "belong" to a publication
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Justin <zzzzz.graf@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cd4534063236247d"
Archived-At: <https://www.postgresql.org/message-id/CAHnozTi5e4pXo2myty8OOtLOatLwNADh1p8ThNhm3LenUQjzsw%40mail.gmail.com>
Precedence: bulk

--000000000000cd4534063236247d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFPM 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 ca=
n
> 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 connect=
s
> 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#PROTOCO=
L-REPLICATION-START-REPLICATION-SLOT-LOGICAL
>  [2]:
> https://www.postgresql.org/docs/current/protocol-logical-replication.html=
#PROTOCOL-LOGICAL-REPLICATION-PARAMS
>


--=20
Willy-Bas Loos

--000000000000cd4534063236247d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Laurenz,</div><div><br></div><div></div><div></div=
><div>Thanks for answering!</div><div>I find it very strange, because the p=
ublication is needed to make a subscription, which makes the slot.</div><di=
v>Thanks for looking into it and helping me understand.</div><div><br></div=
><div>Cheers!</div><div>Willy-Bas Loos</div><div><br></div></div><br><div c=
lass=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_=
attr">On Mon, Apr 7, 2025 at 3:31=E2=80=AFPM Laurenz Albe &lt;<a href=3D"ma=
ilto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">On Mon, 2025-04-07 a=
t 12:16 +0200, Willy-Bas Loos wrote:<br>
&gt; My question is not so much about &quot;can i drop a certain replicatio=
n slot&quot;,=C2=A0<br>
&gt; more about &quot;does this publication still have any replication slot=
s?&quot;.<br>
&gt; Or, if you will: &quot;what&#39;s the publication for this replication=
 slot?&quot;.<br>
&gt; <br>
&gt; I&#39;ve double checked the views that you suggested, and I found that=
 I can relate<br>
&gt; the WAL sender processes to replication slots through pg_replication_s=
lots.active_pid .<br>
&gt; I&#39;ve also looked into replication origins.<br>
&gt; <br>
&gt; But I can&#39;t find a link to the publication. And that&#39;s what I =
need to know.<br>
<br>
I don&#39;t think that there is a connection between a publication and a<br=
>
replication slot.=C2=A0 That connection is only made when a subscriber conn=
ects<br>
and runs the START_REPLICATION command [1] and specifies the &quot;pgoutput=
&quot;<br>
plugin with the &quot;publication_names&quot; option [2].<br>
<br>
I don&#39;t think you can see that information reflected in a system view<b=
r>
on the primary.=C2=A0 You&#39;d have to query &quot;pg_subscription&quot; o=
n the standby.<br>
<br>
Yours,<br>
Laurenz Albe<br>
<br>
<br>
=C2=A0[1]: <a href=3D"https://www.postgresql.org/docs/current/protocol-repl=
ication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL" rel=3D"no=
referrer" target=3D"_blank">https://www.postgresql.org/docs/current/protoco=
l-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL</a><=
br>
=C2=A0[2]: <a href=3D"https://www.postgresql.org/docs/current/protocol-logi=
cal-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS" rel=3D"noreferrer=
" target=3D"_blank">https://www.postgresql.org/docs/current/protocol-logica=
l-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS</a><br>
</blockquote></div><div><br clear=3D"all"></div><br><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr">Willy-Bas Loos<br></div></div>

--000000000000cd4534063236247d--