On Tue, Feb 28, 2023 at 08:56:37AM +0530, Amit Kapila wrote: > On Tue, Feb 28, 2023 at 7:55 AM Julien Rouhaud <rjuju...@gmail.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.
