Re: pg_upgrade and logical replication

On Mon, Nov 27, 2023 at 3:18 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Sat, 25 Nov 2023 at 17:50, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Sat, Nov 25, 2023 at 7:21 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> >
> > Few comments on v19:
> > ==================
> > 1.
> > + <para>
> > + The subscriptions will be migrated to the new cluster in a disabled state.
> > + After migration, do this:
> > + </para>
> > +
> > + <itemizedlist>
> > + <listitem>
> > + <para>
> > + Enable the subscriptions by executing
> > + <link linkend="sql-altersubscription"><command>ALTER
> > SUBSCRIPTION ... ENABLE</command></link>.
> >
> > The reason for this restriction is not very clear to me. Is it because
> > we are using pg_dump for subscription and the existing functionality
> > is doing it? If so, I think currently even connect is false.
>
> This was done this way so that the apply worker doesn't get started
> while the upgrade is happening. Now that we have set
> max_logical_replication_workers to 0, the apply workers will not get
> started during the upgrade process. I think now we can create the
> subscriptions with the same options as the old cluster in case of
> upgrade.
>

Okay, but what is your plan to change it. Currently, we are relying on
existing pg_dump code to dump subscriptions data, do you want to
change that? There is a reason for the current behavior of pg_dump
which as mentioned in docs is: "When dumping logical replication
subscriptions, pg_dump will generate CREATE SUBSCRIPTION commands that
use the connect = false option, so that restoring the subscription
does not make remote connections for creating a replication slot or
for initial table copy. That way, the dump can be restored without
requiring network access to the remote servers. It is then up to the
user to reactivate the subscriptions in a suitable way. If the
involved hosts have changed, the connection information might have to
be changed. It might also be appropriate to truncate the target tables
before initiating a new full table copy."

I guess one reason to not enable subscription after restore was that
it can't work without origins, and also one can restore the dump in a
totally different environment, and one may choose not to dump all the
corresponding tables which I don't think is true for an upgrade. So,
that could be one reason to do differently for upgrades. Do we see
reasons similar to pg_dump/restore due to which after upgrade
subscriptions may not work?

--
With Regards,
Amit Kapila.