Message-ID: <f673c080f33805fdf5e3060f8f3338ebc803eb19.camel@cybertec.at>
Subject: Re: find replication slots that "belong" to a publication
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Willy-Bas Loos <willybas@gmail.com>, Justin <zzzzz.graf@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Date: Mon, 07 Apr 2025 15:31:06 +0200
In-Reply-To: <CAHnozThHzgK1KAMpxPV3r_oonFUEqfXxU3rQ7SDTEAvZRi3t_Q@mail.gmail.com>
References: 
	<CAHnozTg9sadVff9WSFSBi=f6YhCJ0wPK3+riK441Cdqx-Jj+_g@mail.gmail.com>
	 <CALL-XeNax53+uja36oX8WS4wX0-MdFJio5SJgpC59XHdC5XAag@mail.gmail.com>
	 <CAHnozThHzgK1KAMpxPV3r_oonFUEqfXxU3rQ7SDTEAvZRi3t_Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/f673c080f33805fdf5e3060f8f3338ebc803eb19.camel%40cybertec.at>
Precedence: bulk

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",=
=C2=A0
> more about "does this publication still have any replication slots?".
> Or, if you will: "what's the publication for this replication slot?".
>=20
> 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_slot=
s.active_pid .
> I've also looked into replication origins.
>=20
> But I can't find a link to the publication. And that's what I need to kno=
w.

I don't think that there is a connection between a publication and a
replication slot.  That connection is only made when a subscriber connects
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#PRO=
TOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL
 [2]: https://www.postgresql.org/docs/current/protocol-logical-replication.=
html#PROTOCOL-LOGICAL-REPLICATION-PARAMS