Re: pg_upgrade and logical replication

On Tue, Feb 28, 2023 at 7:55 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Mon, Feb 27, 2023 at 03:39:18PM +0530, Amit Kapila wrote:
> >
> > BTW, thinking some more
> > on this, how will we allow to continue replication after upgrading the
> > publisher? During upgrade, we don't retain slots, so the replication
> > won't continue. I think after upgrading subscriber-node, user will
> > need to upgrade the publisher as well.
>
> 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?

--
With Regards,
Amit Kapila.