MIME-Version: 1.0
References: <CAHnozTg9sadVff9WSFSBi=f6YhCJ0wPK3+riK441Cdqx-Jj+_g@mail.gmail.com>
In-Reply-To: <CAHnozTg9sadVff9WSFSBi=f6YhCJ0wPK3+riK441Cdqx-Jj+_g@mail.gmail.com>
From: Willy-Bas Loos <willybas@gmail.com>
Date: Fri, 4 Apr 2025 13:42:50 +0200
Message-ID: <CAHnozTgTfoSqAR_4e4-gWkrMsK-ETKS3tgDnM_d7FvyckZeGXg@mail.gmail.com>
Subject: Re: find replication slots that "belong" to a publication
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000964b930631f26391"
Archived-At: <https://www.postgresql.org/message-id/CAHnozTgTfoSqAR_4e4-gWkrMsK-ETKS3tgDnM_d7FvyckZeGXg%40mail.gmail.com>
Precedence: bulk

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

postgres 13 BTW

On Fri, Apr 4, 2025 at 10:58=E2=80=AFAM Willy-Bas Loos <willybas@gmail.com>=
 wrote:

> Hi!
>
> I'm looking for a way to find out if there are still replication slots
> active for a publication before dropping the publication in an automated
> way. The idea is that the publication is thought not to be needed any
> longer, but we want to make sure.
>
> I'm having trouble finding a link between a publication, the subscription=
s
> and the replication slots. Especially when you don't want to make
> assumptions about any subscriber nodes, so you are restricted to the
> publisher node.
>
> The best I could find was a query listed in pg_stat_activity that lists
> the slot name and the publication name:
> START_REPLICATION SLOT "my_slot" LOGICAL 5DD1/3E56D360 (proto_version '1'=
,
> publication_names '"my_publication"')
>
> I don't like the idea of using string manipulation on such query strings
> to get the information I need. Postgres must have a way to compose this
> query.
> Can anyone tell me a way to find replication slots that belong to a
> publication?
>
> --
> Willy-Bas Loos
>


--=20
Willy-Bas Loos

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

<div dir=3D"ltr">postgres 13 BTW</div><br><div class=3D"gmail_quote gmail_q=
uote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Apr 4, 2025 a=
t 10:58=E2=80=AFAM Willy-Bas Loos &lt;<a href=3D"mailto:willybas@gmail.com"=
>willybas@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><div dir=3D"ltr"><div>Hi!</div><div><br></div><div>I&#39=
;m looking for a way to find out if there are still replication slots activ=
e for a publication before dropping the publication in an automated way. Th=
e idea is that the publication is thought not to be needed any longer, but =
we want to make sure.</div><div><br></div><div>I&#39;m having trouble findi=
ng a link between a publication, the subscriptions and the replication slot=
s. Especially when you don&#39;t want to make assumptions about any subscri=
ber nodes, so you are restricted to the publisher node.</div><div></div><di=
v><br></div><div>The best I could find was a query listed in pg_stat_activi=
ty that lists the slot name and the publication name:</div><div>START_REPLI=
CATION SLOT &quot;my_slot&quot; LOGICAL 5DD1/3E56D360 (proto_version &#39;1=
&#39;, publication_names &#39;&quot;my_publication&quot;&#39;)</div><div><b=
r></div><div></div><div>I don&#39;t like the idea of using string manipulat=
ion on such query strings to get the information I need. Postgres must have=
 a way to compose this query.</div><div>Can anyone tell me a way to find re=
plication slots that belong to a publication?</div><br><span class=3D"gmail=
_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"=
><div dir=3D"ltr">Willy-Bas Loos<br></div></div></div>
</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>

--000000000000964b930631f26391--