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

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

Hi Justin, thanks for your anwer!
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.

Cheers,
Willy-Bas

On Sun, Apr 6, 2025 at 3:36=E2=80=AFPM Justin <zzzzz.graf@gmail.com> wrote:

> On Fri, Apr 4, 2025 at 4: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
>> subscriptions and the replication slots. Especially when you don't want =
to
>> make assumptions about any subscriber nodes, so you are restricted to th=
e
>> 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
>>
>
> Hi Willy-Bas,
>
> Logical replication slots appear in the views pg_stat_replication and
> pg_replication_slots.  Both views have the information you are looking fo=
r,
> the difference is pg_stat_replication shows only the active slots.  Keep =
in
> mind Temporary Slots only live for the length of the session that created
> it; the slot will appear in both views.
>
> The bigger issue I think you are trying to address is when can a slot be
> dropped safely.  Once a logical replication slot is dropped there is no
> recovery of the slot's lsn position. Probably the best way to decide if a
> slot has been abandoned is how far behind it is. The pg_wal_lsn_diff  can
> be used to figure out how far behind a slot is
>
> https://www.postgresql.org/docs/13/view-pg-replication-slots.html
>
> https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-ST=
AT-REPLICATION-VIEW
>
>
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-AD=
MIN-BACKUP
>
> Hope this answers your question
>
>
> Justin
>


--=20
Willy-Bas Loos

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

<div dir=3D"ltr"><div>Hi Justin, thanks for your anwer!=C2=A0</div><div>My =
question is not so much about &quot;can i drop a certain replication slot&q=
uot;,=C2=A0</div><div>more about &quot;does this publication still have any=
 replication slots?&quot;. Or, if you will: &quot;what&#39;s the publicatio=
n for this replication slot?&quot;.</div><div><br></div><div>I&#39;ve doubl=
e checked the views that you suggested, and I found that I can relate the W=
AL sender processes to replication slots through pg_replication_slots.activ=
e_pid .</div><div>I&#39;ve also looked into replication origins.</div><div>=
<br></div><div>But I can&#39;t find a link to the publication. And that&#39=
;s what I need to know.</div><div><br></div><div>Cheers,</div><div>Willy-Ba=
s</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Sun, Apr 6, 2025 at 3:36=E2=80=AFPM Justin=
 &lt;<a href=3D"mailto:zzzzz.graf@gmail.com">zzzzz.graf@gmail.com</a>&gt; w=
rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div dir=3D"ltr">On Fri, Apr 4, 2025 at 4:58=E2=80=AFAM Willy-Bas =
Loos &lt;<a href=3D"mailto:willybas@gmail.com" target=3D"_blank">willybas@g=
mail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(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 replicati=
on slots active for a publication before dropping the publication in an aut=
omated way. The idea is that the publication is thought not to be needed an=
y longer, but we want to make sure.</div><div><br></div><div>I&#39;m having=
 trouble finding a link between a publication, the subscriptions and the re=
plication slots. Especially when you don&#39;t want to make assumptions abo=
ut any subscriber nodes, so you are restricted to the publisher node.</div>=
<div></div><div><br></div><div>The best I could find was a query listed in =
pg_stat_activity that lists the slot name and the publication name:</div><d=
iv>START_REPLICATION SLOT &quot;my_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 st=
ring manipulation on such query strings to get the information I need. Post=
gres must have a way to compose this query.</div><div>Can anyone tell me a =
way to find replication slots that belong to a publication?</div><br><span =
class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gm=
ail_signature"><div dir=3D"ltr">Willy-Bas Loos<br></div></div></div></block=
quote><div><br></div><div>Hi Willy-Bas,<br><br>Logical replication slots=C2=
=A0appear in the views pg_stat_replication and pg_replication_slots.=C2=A0 =
Both views have the information you are looking for, the difference is pg_s=
tat_replication shows only the active slots.=C2=A0 Keep in mind Temporary S=
lots only live for the length of the session that created it; the slot will=
 appear in both views.=C2=A0<br><br>The bigger issue I think you are trying=
 to address is when can a slot be dropped safely.=C2=A0 Once a logical repl=
ication slot is dropped there is no recovery of the slot&#39;s lsn position=
. Probably the best way to decide if a slot has been abandoned is how far b=
ehind it is. The=C2=A0pg_wal_lsn_diff=C2=A0 can be used to figure out how f=
ar behind a slot is<br><br></div><div><a href=3D"https://www.postgresql.org=
/docs/13/view-pg-replication-slots.html" target=3D"_blank">https://www.post=
gresql.org/docs/13/view-pg-replication-slots.html</a><br><a href=3D"https:/=
/www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-REPLIC=
ATION-VIEW" target=3D"_blank">https://www.postgresql.org/docs/13/monitoring=
-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW</a>=C2=A0 =C2=A0<br><a href=
=3D"https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-=
ADMIN-BACKUP" target=3D"_blank">https://www.postgresql.org/docs/current/fun=
ctions-admin.html#FUNCTIONS-ADMIN-BACKUP</a><br><br>Hope this answers your =
question<br><br><br>Justin=C2=A0</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>

--00000000000047c0a006322d879d--