Re: Using failover slots for PG-non_PG logical replication - Mailing list pgsql-hackers

On Wed, Jul 2, 2025 at 5:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Wed, Jul 2, 2025 at 12:36 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> > >
> > > Hi All,
> > >
> > > The failover slots documentation [1] is good for PG - PG logical
> > > replication, but the first two queries require pg_subscription which
> > > may not be present in non-PG downstream. Somebody looking to setup
> > > failover slots for non-PG subscriber may not find the page useful.
> >
> > Okay.  It appears to me that the entire document at [1] is
> > specifically intended for a built-in replication setup, and the
> > corresponding page was written with that context in mind.
> >
> > > However, the third query, when modified to mention the replication
> > > slots relevant to the downstream is useful to them. How to find the
> > > replication slots to be synchronized is a problem specific to the type
> > > of downstream. Such a setup should add those slots to
> > > sync_replication_slots. I think the chapter should mention that the
> > > 3rd query should also include the slots mentioned in
> > > sync_replication_slots for PG-non_PG logical replication setup.
> > >
> >
> > sync_replication_slots is a boolean which enables a physical standby
> > to synchronize logical failover slots. Did you mean something else?
>
> I confused this with the actual list of slots to be synchronized.
> Sorry for that. The slots to be synchronized can be obtained from the
> primary by querying pg_replication_slots with failover = true.
>

Note that primary may have slots corresponding to multiple subscriber
nodes, so querying all slots on primary will give a correct answer may
depend on the use case. For example, say a user wants to do some sort
of load balancing such that some of the subscriber/downstream nodes
are served by a standby, then directly querying all slots from
pg_replication_slots from the primary won't give the correct answer.
In a typical failover case as well, if slots corresponding to a
particular downstream are ready, then that should be sufficient to
continue replication from the standby. Then, also, there is a case
when the primary node is down, then such a query won't work; it can
only work when there is a planned switchover. Considering all these
points, I am not sure if it is a good idea to mention querying the
primary for all slots marked with failover=true. However, I agree that
we should mention something for non-native logical replication
solutions, something on the lines of what Shveta is proposing. OTOH,
if you or Shveta have some clear guidelines for how a downstream can
find the required slots which can work in all or most cases, then it
is okay to mention that as well.

--
With Regards,
Amit Kapila.