Re: pg_upgrade and logical replication

On Mon, Feb 20, 2023 at 11:07:42AM +0530, Amit Kapila wrote:
> On Sun, Feb 19, 2023 at 5:31 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> >
> > > >
> > > > 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.

Is there really a use case for dumping the content of pg_subscription_rel
outside of pg_upgrade? I'm not particularly worried about the publisher going
away or changing while pg_upgrade is running , but for a normal pg_dump /
pg_restore I don't really see how anyone would actually want to resume logical
replication from a pg_dump, especially since it's almost guaranteed that the
node will already have consumed data from the publication that won't be in the
dump in the first place.

Are you ok with the suggested syntax above (probably with extra parens to avoid
adding new keywords), or do you have some better suggestion? I'm a bit worried
about adding some O(n) commands, as it can add some noticeable slow-down for
pg_upgrade-ing logical replica, but I don't really see how to avoid that. Note
that if we make this option available to end-users, we will have to use the
relation name rather than its oid, which will make this option even more
expensive when restoring due to the extra lookups.

For the pg_upgrade use-case, do you see any reason to not restore the
pg_subscription_rel by default? Maybe having an option to not restore it would
make sense if it indeed add noticeable overhead when publications have a lot of
tables?