Re: pg_upgrade and logical replication

On Wed, Nov 1, 2023 at 8:33 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> 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.
>

But that poses needless restrictions for the users. For example, there
appears no harm in upgrading even when the relation is in
SUBREL_STATE_INIT state. Users should be able to continue replication
after the upgrade.

> 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?
>

Yes.

--
With Regards,
Amit Kapila.