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?