Re: pg_upgrade and logical replication

On Sat, Feb 18, 2023 at 09:31:30AM +0530, Amit Kapila wrote:
> On Fri, Feb 17, 2023 at 9:05 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> >
> > I'm concerned about people not coming from physical replication. If you just
> > had some "normal" logical replication, you can't assume that you already have
> > all the data from the upstream subscription. If it was modified and a non
> > empty table is added, you might need to copy the data of part of the tables and
> > keep replicating for the rest. It's hard to be sure from a user point of view,
> > and even if you knew you have no way to express it.
> >
>
> Can't the user create a separate publication for such newly added
> tables and a corresponding new subscription on the downstream node?

Yes that seems like a safe way to go, but it relies on users being very careful
if they don't want to get corrupted logical standby, and I think it's
impossible to run any check to make sure that the subscription is adequate?

> 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.

> >
> > 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';

pg_upgrade already preserves the relation's oid, so we could restore the
exact original state and then enabling the subscription would just work?

We could restrict this form to --binary only so we don't provide a way for
users to mess the data.