On Wed, Mar 1, 2023 at 3:55 PM Julien Rouhaud <rjuju...@gmail.com> wrote: > > On Wed, Mar 01, 2023 at 11:51:49AM +0530, Amit Kapila wrote: > > On Tue, Feb 28, 2023 at 10:18 AM Julien Rouhaud <rjuju...@gmail.com> wrote: > > > > > > 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. > > Sure. Here are the overly detailed steps: > > 1) setup a normal physical replication cluster (pg_basebackup, restoring > PITR, > whatever), let's call the primary node "A" and replica node "B" > 2) ensure WAL level is "logical" on the primary node A > 3) create a logical replication slot on every (connectable) database (or just > the one you're interested in if you don't want to preserve everything) on > A > 4) create a FOR ALL TABLE publication (again for every databases or just the > one you're interested in) > 5) wait for replication to be reasonably if not entirely up to date > 6) promote the standby node B > 7) retrieve the promotion LSN (from the XXXXXXXX.history file, > pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()...) > 8) call pg_replication_slot_advance() with that LSN for all previously > created > logical replication slots on A > 9) create a normal subscription on all wanted databases on the promoted node > 10) wait for it to catchup if needed on B > 12) stop the node B > 13) run pg_upgrade on B, creating the new node C > 14) start C, run the global ANALYZE and any sanity check needed (hopefully you > would have validated that your application is compatible with that new > version before this point)
I might be missing something but is there any reason why you created a subscription before pg_upgrade? Steps like doing pg_upgrade, then creating missing tables, and then creating a subscription (with copy_data = false) could be an alternative way to support upgrading the server from the physical standby? Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
