MIME-Version: 1.0
From: Willy-Bas Loos <willybas@gmail.com>
Date: Fri, 4 Apr 2025 10:58:13 +0200
Message-ID: <CAHnozTg9sadVff9WSFSBi=f6YhCJ0wPK3+riK441Cdqx-Jj+_g@mail.gmail.com>
Subject: find replication slots that "belong" to a publication
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ce94da0631f0161e"
Archived-At: <https://www.postgresql.org/message-id/CAHnozTg9sadVff9WSFSBi%3Df6YhCJ0wPK3%2BriK441Cdqx-Jj%2B_g%40mail.gmail.com>
Precedence: bulk

--000000000000ce94da0631f0161e
Content-Type: text/plain; charset="UTF-8"

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 subscriptions
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

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

<div dir=3D"ltr"><div>Hi!</div><div><br></div><div>I&#39;m looking for a wa=
y 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 sur=
e.</div><div><br></div><div>I&#39;m having trouble finding a link between a=
 publication, the subscriptions and the replication slots. Especially when =
you don&#39;t want to make assumptions about any subscriber nodes, so you a=
re restricted to the publisher node.</div><div></div><div><br></div><div>Th=
e best I could find was a query listed in pg_stat_activity that lists the s=
lot name and the publication name:</div><div>START_REPLICATION SLOT &quot;m=
y_slot&quot; LOGICAL 5DD1/3E56D360 (proto_version &#39;1&#39;, publication_=
names &#39;&quot;my_publication&quot;&#39;)</div><div><br></div><div></div>=
<div>I don&#39;t like the idea of using string manipulation on such query s=
trings to get the information I need. Postgres must have a way to compose t=
his query.</div><div>Can anyone tell me a way to find replication slots tha=
t belong to a publication?</div><br><span class=3D"gmail_signature_prefix">=
-- </span><br><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"=
gmail_signature"><div dir=3D"ltr">Willy-Bas Loos<br></div></div></div>

--000000000000ce94da0631f0161e--