Re: pg_upgrade and logical replication

On Mon, Sep 25, 2023 at 11:43 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, Sep 25, 2023 at 10:05:41AM +0530, Amit Kapila wrote:
> > I also don't think that this patch has to solve the problem of
> > publishers in any way but as per my understanding, if due to some
> > reason we are not able to do the upgrade of publishers, this can add
> > more steps for users than they have to do now for logical replication
> > set up after upgrade. This is because now after restoring the
> > subscription rel's and origin, as soon as we start replication after
> > creating the slots on the publisher, we will never be able to
> > guarantee data consistency. So, they need to drop the entire
> > subscription setup including truncating the relations, and then set it
> > up from scratch which also means they need to somehow remember or take
> > a dump of the current subscription setup. According to me, the key
> > point is to have a mechanism to set up slots correctly to allow
> > replication (or subscriptions) to work after the upgrade. Without
> > that, it appears to me that we are restoring a subscription where it
> > can start from some random LSN and can easily lead to data consistency
> > issues where it can miss some of the updates.
>
> Sure, that's assuming that the publisher side is upgraded.
>

At some point, user needs to upgrade publisher and subscriber could
itself have some publications defined which means the downstream
subscribers will have the same problem.

> FWIW, my
> take is that there's room to move forward with this patch anyway in
> favor of cases like rollover upgrades to the subscriber.
>
> > This is the primary reason why I prioritized to work on the publisher
> > side before getting this patch done, otherwise, the solution for this
> > patch was relatively clear. I am not sure but I guess this could be
> > the reason why originally we left it in the current state, otherwise,
> > restoring subscription rel's or origin doesn't seem to be too much of
> > an additional effort than what we are doing now.
>
> By "additional effort", you are referring to what the patch is doing,
> with the binary dump of pg_subscription_rel, right?
>

Yes.

--
With Regards,
Amit Kapila.