Re: pg_createsubscriber: drop pre-existing subscriptions from the converted node

On Fri, Jun 21, 2024 at 4:51 PM Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> This is a follow-up thread for pg_createsubscriber [1]. I started a new thread
> since there is no activity around here.
>
> ## Problem
>
> Assuming that there is a cascading replication like below:
>
> node A --(logical replication)--> node B --(streaming replication)--> node C
>
> In this case, subscriptions exist even on node C, but it does not try to connect
> to node A because the logical replication launcher/worker won't be launched.
> After the conversion, node C becomes a subscriber for node B, and the subscription
> toward node A remains. Therefore, another worker that tries to connect to node A
> will be launched, raising an ERROR [2]. This failure may occur even during the
> conversion.
>
> ## Solution
>
> The easiest solution is to drop pre-existing subscriptions from the converted node.
> To avoid establishing connections during the conversion, slot_name is set to NONE
> on the primary first, then drop on the standby. The setting will be restored on the
> primary node.
>

It seems disabling subscriptions on the primary can make the primary
stop functioning for some duration of time. I feel we need some
solution where after converting to subscriber, we disable and drop
pre-existing subscriptions. One idea could be that we use the list of
new subscriptions created by the tool such that any subscription not
existing in that list will be dropped.

Shouldn't this be an open item for PG17?

--
With Regards,
Amit Kapila.