Horton

Re: pg_upgrade and logical replication

Nikolay Samokhvalov <samokhvalov@gmail.com>

View thread View on postgr.es
From: Nikolay Samokhvalov <samokhvalov@gmail.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, pgsql-hackers@lists.postgresql.org, Vitaliy Kukharik <vitaliy@postgres.ai>
Date: 2023-02-28T16:02:13Z
Lists: pgsql-hackers 
On Fri, Feb 17, 2023 at 7:35 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
>  Any table later added in the
> publication is either already fully replicated until that LSN on the upgraded
> node, so only the delta is needed, or has been created after that LSN.  In the
> latter case, the entirety of the table will be replicated with the logical
> replication as a delta right?


What if we consider a slightly adjusted procedure?

0. Temporarily, forbid running any DDL on the source cluster.
1. On the source, create publication, replication slot and remember
the LSN for it
2. Restore the target cluster to that LSN using restore_target_lsn (PITR)
3. Run pg_upgrade on the target cluster
4. Only now, create subscription to target
5. Wait until logical replication catches up
6. Perform a switchover to the new cluster taking care of lags in sequences, etc
7. Resume DDL when needed

Do you see any data loss happening in this approach?

Commits

  1. pg_upgrade: Retrieve subscription count more efficiently.

  2. Fix random failure in 004_subscription.

  3. Fix 004_subscription.pl to allow its usage in --link mode.

  4. Allow upgrades to preserve the full subscription's state.

  5. Prevent startup of logical replication launcher during pg_upgrade