Re: [PATCH] Preserve replication origin OIDs in pg_upgrade

On Fri, Oct 30, 2026 at 2:46 PM Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> Hi,
>
> While discussing internally we found a corner case when the source is PG16-.
> To avoid the case, I want to propose that pg_upgrade can reject the code while the
> tablesync is on-going, even for PG16- node.
>
> Found issue
> ========
> Assuming that source server is PG16-, it has subscriptions and tablesync is on-going.
> In this case replication origins created by tablesync workers also exist. If the
> pg_upgrade is done at that timing, these replication origins would be additionally
> migrated to the new node.
> E.g., if source node has 1 subscription and 2 tables are synchronizing, three
> origins will be migrated to the new node.
>
> This replication origin cannot be dropped automatically even if users run
> DROP SUBSCRIPTION [1] and ALTER SUBSCRIPTION REFRESH PUBLICATION WITH (copy_data = off).
> For DROP command, origins for tablesync can be dropped when the corresponding
> table is listed in the pg_subscription_rel, but entries cannot be migrated from PG16- node.
> Users must run ALTER SUBSCRIPTION REFRESH PUBLICATION WITH (copy_data = on),
> or drop the origin manually. First one invokes whole copy of the table and second
> one can be easily forgotten.

Thanks for the detailed explanation.

> Proposed fix
> =======
> Origins for tablesync workers can exist only while the synchronization is on-going,
> so my idea is does not allow upgrading during the synchronization. For now,
> the check function check_old_cluster_subscription_state() ensures all sync are done,
> but it's called when the source is PG17+, which supports migrating the pg_subscription_rel.
> I think we can do for the same all cases.
>
> Note
> ====
> check_old_cluster_subscription_state() also ensures that all subscriptions have
> the respective replication origins. For now upgrading PG16- node can be done
> even if subscriptions do not have origins, but such a case would be additionally
> rejected after the fix.
> I felt it's harmless because there are no benefits for origin-missing subscriptions,
> but please post here if you have any doubts.

I have the same understanding. I agree with the proposed solution.

> How do you feel?
>
> [1]
> ```
> postgres=# SELECT * FROM pg_replication_origin;
> roident | roname
> ---------+----------------
> 1 | pg_16389 // origin for subscription
> 2 | pg_16389_16384 // origin for table synchronization
> (2 rows)
>
> postgres=# DROP SUBSCRIPTION regress_sub ;
> NOTICE: dropped replication slot "regress_sub" on publisher
> DROP SUBSCRIPTION
> postgres=# SELECT * FROM pg_replication_origin;
> roident | roname
> ---------+----------------
> 2 | pg_16389_16384
> (1 row)
> ```
>
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
>