Re: pg_upgrade and logical replication

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.