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