Re: pg_upgrade and logical replication

On Mon, Sep 4, 2023 at 12:15 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, Sep 04, 2023 at 11:51:14AM +0530, Amit Kapila wrote:
> > +1 for doing it via function (something like
> > binary_upgrade_create_sub_rel_state). We already have the internal
> > function AddSubscriptionRelState() that can do the core work.
>
> It is one of these patches that I have let aside for too long, and it
> solves a use-case of its own. I think that I could hack that pretty
> quickly given that Julien has done a bunch of the ground work. Would
> you agree with that?
>

Yeah, I agree that could be hacked quickly but note I haven't reviewed
in detail if there are other design issues in this patch. Note that we
thought first to support the upgrade of the publisher node, otherwise,
immediately after upgrading the subscriber and publisher, the
subscriptions won't work and start giving errors as they are dependent
on slots in the publisher. One other point that needs some thought is
that the LSN positions we are going to copy in the catalog may no
longer be valid after the upgrade (of the publisher) because we reset
WAL. Does that need some special consideration or are we okay with
that in all cases? As of now, things are quite safe as documented in
pg_dump doc page that it will be the user's responsibility to set up
replication after dump/restore. I think it would be really helpful if
you could share your thoughts on the publisher-side matter as we are
facing a few tricky questions to be answered. For example, see a new
thread [1].

> > Like the publisher-side upgrade patch [1], I think we should allow
> > upgrading subscriptions by default instead with some flag like
> > --preserve-subscription-state. If required, we can introduce --exclude
> > option for upgrade. Having it just for pg_dump sounds reasonable to
> > me.
> >
> > [1] - https://www.postgresql.org/message-id/TYAPR01MB58664C81887B3AF2EB6B16E3F5939%40TYAPR01MB5866.jpnprd01.prod.outlook.com
>
> In the interface of the publisher for pg_upgrade agreed on and set in
> stone? I certainly agree to have a consistent upgrade experience for
> the two sides of logical replication, publications and subscriptions.
> Also, I'd rather have a filtering option at the same time as the
> upgrade option to give more control to users from the start.
>

The point raised by Jonathan for not having an option for pg_upgrade
is that it will be easy for users, otherwise, users always need to
enable this option. Consider a replication setup, wouldn't users want
by default it to be upgraded? Asking them to do that via an option
would be an inconvenience. So, that was the reason, we wanted to have
an --exclude option and by default allow slots to be upgraded. I think
the same theory applies here.

[1] - https://www.postgresql.org/message-id/CAA4eK1LV3%2B76CSOAk0h8Kv0AKb-OETsJHe6Sq6172-7DZXf0Qg%40mail.gmail.com

--
With Regards,
Amit Kapila.