Re: pg_upgrade and logical replication

On Wed, Mar 1, 2023 at 12:25 PM Julien Rouhaud <rjuju123@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 <rjuju123@gmail.com> wrote:
> > >
> >
> > 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
>

How are these slots used? Do subscriptions use these slots?

>  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)
> 15) re-enable the subscription on C.  This is currently not possible without
>     losing data, the patch fixes that
> 16) wait for it to catchup if needed
> 17) create any missing relation and do the ALTER SUBSCRIPTION ... REFRESH if
>     needed
> 18) trash B
> 19) create new nodes D, E... as physical replica from C if needed, possibly
> using cheaper approach like pg_start_backup() / rsync / pg_stop_backup if
> needed
> 20) switchover to C and trash A (or convert it to another replica if you want)
> 21) trash the publications on C on all databases
>
> As noted the step 15 is currently problematic, and is also problematic in any
> variation of that scenario that doesn't require you to entirely recreate the
> node C from scratch using logical replication, which is what I want to avoid.
>
> This isn't terribly complicated but requires to be really careful if you don't
> want to end up with an incorrect node C.  This approach is also currently not
> entirely ideal, but hopefully logical replication of sequences and DDL will
> remove the main sources of downtime when upgrading using logical replication.
>

I think there are good chances that one can make mistakes following
all the above steps unless she is an expert.

> My ultimate goal is to provide some tooling to do that in a much simpler way.
> Maybe a new "promote to logical" action that would take care of steps 2 to 9.
> Users would therefore only have to do this "promotion to logical", and then run
> pg_upgrade and create a new physical replication cluster if they want.
>

Why don't we try to support the direct upgrade of logical replication
nodes? Have you tried to analyze what are the obstacles and whether we
can have solutions for those? For example, one of the challenges is to
support the upgrade of slots, can we copy (from the old cluster) and
recreate them in the new cluster by resetting LSNs? We can also reset
origins during the upgrade of subscribers and recommend to first
upgrade the subscriber node.

--
With Regards,
Amit Kapila.