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: Justin <zzzzz.graf@gmail.com>
Date: Sun, 6 Apr 2025 09:36:03 -0400
Message-ID: <CALL-XeNax53+uja36oX8WS4wX0-MdFJio5SJgpC59XHdC5XAag@mail.gmail.com>
Subject: Re: find replication slots that "belong" to a publication
To: Willy-Bas Loos <willybas@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001d4db306321c3481"
Archived-At: <https://www.postgresql.org/message-id/CALL-XeNax53%2Buja36oX8WS4wX0-MdFJio5SJgpC59XHdC5XAag%40mail.gmail.com>
Precedence: bulk

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

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

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 for,
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-STAT=
-REPLICATION-VIEW

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMI=
N-BACKUP

Hope this answers your question


Justin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Apr 4, 2025 at 4:58=E2=80=AFAM Wi=
lly-Bas Loos &lt;<a href=3D"mailto:willybas@gmail.com">willybas@gmail.com</=
a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x 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 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.</div><div><br></div><div>I=
9;m having trouble finding a link between a publication, the subscriptions =
and the replication slots. Especially when you don&#39;t want to make assum=
ptions about any subscriber nodes, so you are restricted to the publisher n=
ode.</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 nam=
e:</div><div>START_REPLICATION SLOT &quot;my_slot&quot; LOGICAL 5DD1/3E56D3=
60 (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 o=
f using string manipulation 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 replication slots that belong to a publication?</div>=
<br><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" c=
lass=3D"gmail_signature"><div dir=3D"ltr">Willy-Bas Loos<br></div></div></d=
iv></blockquote><div><br></div><div>Hi Willy-Bas,<br><br>Logical replicatio=
n slots=C2=A0appear in the views pg_stat_replication and pg_replication_slo=
ts.=C2=A0 Both views have the information you are looking for, the differen=
ce is pg_stat_replication shows only the active slots.=C2=A0 Keep in mind T=
emporary Slots 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 lo=
gical replication slot is dropped there is no recovery of the slot&#39;s ls=
n position. Probably the best way to decide if a slot has been abandoned is=
 how far behind it is. The=C2=A0pg_wal_lsn_diff=C2=A0 can be used to figure=
 out how far behind a slot is<br><br></div><div><a href=3D"https://www.post=
gresql.org/docs/13/view-pg-replication-slots.html">https://www.postgresql.o=
rg/docs/13/view-pg-replication-slots.html</a><br><a href=3D"https://www.pos=
tgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VI=
EW">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">https://www.=
postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP</a>=
<br><br>Hope this answers your question<br><br><br>Justin=C2=A0</div></div>=
</div>

--0000000000001d4db306321c3481--