On Fri, Oct 30, 2026 at 2:46 PM Hayato Kuroda (Fujitsu) <[email protected]> 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 >
