Re: pg_upgrade and logical replication

On Fri, Oct 27, 2023 at 05:05:39PM +0530, Amit Kapila wrote:
> I was analyzing this part and it seems it could be tricky to upgrade
> in FINISHEDCOPY state. Because the system would expect that subscriber
> would know the old slotname from oldcluster which it can drop at
> SYNCDONE state. Now, as sync_slot_name is generated based on subid,
> relid which could be different in the new cluster, the generated
> slotname would be different after the upgrade. OTOH, if the relstate
> is INIT, then I think the sync could be performed even after the
> upgrade.

TBH, I am really wondering if there is any need to go down to being
able to handle anything else than READY for the relation states in
pg_subscription_rel. One reason is that it makes it much easier to
think about how to handle these in parallel of a node with
publications that also need to go through an upgrade, because as READY
relations they don't require any tracking. IMO, this makes it simpler
to think about cases where a node holds both subscriptions and
publications.

FWIW, my take is that it feels natural to do the upgrades of
subscriptions first, creating a similarity with the case of minor
updates with physical replication setups.

> Shouldn't we at least ensure that replication origins do exist in the
> old cluster corresponding to each of the subscriptions? Otherwise,
> later the query to get remote_lsn for origin in getSubscriptions()
> would fail.

You mean in the shape of a pre-upgrade check making sure that
pg_replication_origin_status has entries for all the subscriptions we
expect to see during the upgrade? Makes sense to me.
--
Michael