Re: pg_upgrade and logical replication

On Sat, Feb 18, 2023 at 04:12:52PM +0530, Amit Kapila wrote:
> On Sat, Feb 18, 2023 at 11:21 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > > Now, I think it would be a bit tricky if the user already has a
> > > publication defined with FOR ALL TABLES. In that case, we probably
> > > need some way to specify FOR ALL TABLES EXCEPT (list of tables) which
> > > we currently don't have.
> >
> > Yes, and note that I rely on FOR ALL TABLES for my original physical to logical
> > use case.
> >
>
> Okay, but if we would have functionality like EXCEPT (list of tables),
> one could do ALTER PUBLICATION .. before doing REFRESH on the
> subscriber-side.

Honestly I'm not a huge fan of this approach.  It feels hacky to have such a
feature, and doesn't even solve the problem on its own as you still lose
records when reactivating the subscription unless you also provide an ALTER
SUBSCRIPTION ENABLE WITH (refresh = true, copy_data = false), which will
probably require different defaults than the rest of the ALTER SUBSCRIPTION
subcommands that handle a refresh.

> > > > Indeed, but it's barely saying "It is then up to the user to reactivate the
> > > > subscriptions in a suitable way" and "It might also be appropriate to truncate
> > > > the target tables before initiating a new full table copy".  As I mentioned, I
> > > > don't think there's a suitable way to reactivate the subscription, at least if
> > > > you don't want to miss some records, so truncating all target tables is the
> > > > only fully safe way to proceed.  It seems quite silly to have to do so just
> > > > because pg_upgrade doesn't retain the list of relation per subscription.
> > > >
> > >
> > > 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.