Re: pg_upgrade and logical replication

On Tue, Feb 28, 2023 at 10:18 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Tue, Feb 28, 2023 at 08:56:37AM +0530, Amit Kapila wrote:
> > On Tue, Feb 28, 2023 at 7:55 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> > >
> > >
> > > The scenario I'm interested in is to rely on logical replication only for the
> > > upgrade, so the end state (and start state) is to go back to physical
> > > replication. In that case, I would just create new physical replica from the
> > > pg_upgrade'd server and failover to that node, or rsync the previous publisher
> > > node to make it a physical replica.
> > >
> > > But even if you want to only rely on logical replication, I'm not sure why you
> > > would want to keep the publisher node as a publisher node? I think that doing
> > > it this way will lead to a longer downtime compared to doing a failover on the
> > > pg_upgrade'd node, make it a publisher and then move the former publisher node
> > > to a subscriber.
> > >
> >
> > I am not sure if this is usually everyone follows because it sounds
> > like a lot of work to me. IIUC, to achieve this, one needs to recreate
> > all the publications and subscriptions after changing the roles of
> > publisher and subscriber. Can you please write steps to show exactly
> > what you have in mind to avoid any misunderstanding?
>
> Well, as I mentioned I'm *not* interested in a logical-replication-only
> scenario. Logical replication is nice but it will always be less efficient
> than physical replication, and some workloads also don't really play well with
> it. So while it can be a huge asset in some cases I'm for now looking at
> leveraging logical replication for the purpose of major upgrade only for a
> physical replication cluster, so the publications and subscriptions are only
> temporary and trashed after use.
>
> That being said I was only saying that if I had to do a major upgrade of a
> logical replication cluster this is probably how I would try to do it, to
> minimize downtime, even if there are probably *a lot* difficulties to
> overcome.
>

Okay, but it would be better if you list out your detailed steps. It
would be useful to support the new mechanism in this area if others
also find your steps to upgrade useful.

--
With Regards,
Amit Kapila.