Re: pg_upgrade and logical replication

On Wed, Mar 1, 2023 at 3:55 PM Julien Rouhaud <rjuju123(at)gmail(dot)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 <rjuju123(at)gmail(dot)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