Re: pg_upgrade and logical replication

On Sun, Feb 26, 2023 at 8:35 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Sat, Feb 25, 2023 at 11:24:17AM +0530, Amit Kapila wrote:
> > >
> > > The new command is of the form
> > >
> > > ALTER SUBSCRIPTION name ADD TABLE (relid = X, state = 'Y', lsn = 'Z/Z')
> > >
> > > with the lsn part being optional.
> > >
> >
> > BTW, do we restore the origin and its LSN after the upgrade? Because
> > without that this won't be sufficient as that is required for apply
> > worker to ensure that it is in sync with table sync workers.
>
> We currently don't, which is yet another sign that no one actually tried to
> resume logical replication after a pg_upgrade. That being said, trying to
> pg_upgrade a node that's currently syncing relations seems like a bad idea
> (I didn't even think to try), but I guess it should also be supported. I will
> work on that too. Assuming we add a new option for controlling either plain
> pg_dump and/or pg_upgrade behavior, should this option control both
> pg_subscription_rel and replication origins and their data or do we need more
> granularity?
>

My vote would be to have one option for both. BTW, thinking some more
on this, how will we allow to continue replication after upgrading the
publisher? During upgrade, we don't retain slots, so the replication
won't continue. I think after upgrading subscriber-node, user will
need to upgrade the publisher as well.

--
With Regards,
Amit Kapila.