Re: pg_upgrade and logical replication

On Sun, Feb 19, 2023 at 5:31 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Sat, Feb 18, 2023 at 04:12:52PM +0530, Amit Kapila wrote:
> > > > >
> > > >
> > > > I also don't know if there is any other safe way for newly added
> > > > tables apart from the above suggestion to create separate publications
> > > > but that can work only in specific cases.
> > >
> > > I might be missing something, but what could go wrong if pg_upgrade could emit
> > > a bunch of commands like:
> > >
> > > ALTER SUBSCRIPTION subname ADD RELATION relid STATE 'x' LSN 'X/Y';
> > >
> >
> > How will we know the STATE and LSN of each relation?
>
> In the pg_subscription_rel catalog of the upgraded server? I didn't look in
> detail on how information are updated but I'm assuming that if logical
> replication survives after a database restart it shouldn't be a problem to also
> fully dump it during pg_upgrade.
>
> > But I think even
> > if know that what is the guarantee that publisher side still has still
> > retained the corresponding slots?
>
> No guarantee, but if you're just doing a pg_upgrade of a logical replica why
> would you drop the replication slot? In any case the warning you mentioned in
> pg_dump documentation would still apply and you would have to reenable it as
> needed, the only difference is that you would actually be able to keep your
> logical replication after a pg_upgrade if you need. If you dropped the
> replication slot on the publisher side, then simply remove the publications on
> the upgraded node too, or create a new one, exactly as you would do with the
> current pg_upgrade workflow.
>

I think the current mechanism tries to provide more flexibility to the
users. OTOH, in some of the cases where users don't want to change
anything in the logical replication (both upstream and downstream
function as it is) after the upgrade then they need to do more work. I
think ideally there should be some option in pg_dump that allows us to
dump the contents of pg_subscription_rel as well, so that is easier
for users to continue replication after the upgrade. We can then use
it for binary-upgrade mode as well.

--
With Regards,
Amit Kapila.