Subject: pgsql: Allow upgrades to preserve the full subscription's state.

From: Amit Kapila <[email-masked]>
Date: Tue, 02 Jan 2024 03:07:34 +0000

Allow upgrades to preserve the full subscription's state.

This feature will allow us to replicate the changes on subscriber nodes
after the upgrade.

Previously, only the subscription metadata information was preserved.
Without the list of relations and their state, it's not possible to
re-enable the subscriptions without missing some records as the list of
relations can only be refreshed after enabling the subscription (and
therefore starting the apply worker).  Even if we added a way to refresh
the subscription while enabling a publication, we still wouldn't know
which relations are new on the publication side, and therefore should be
fully synced, and which shouldn't.

To preserve the subscription relations, this patch teaches pg_dump to
restore the content of pg_subscription_rel from the old cluster by using
binary_upgrade_add_sub_rel_state SQL function. This is supported only
in binary upgrade mode.

The subscription's replication origin is needed to ensure that we don't
replicate anything twice.

To preserve the replication origins, this patch teaches pg_dump to update
the replication origin along with creating a subscription by using
binary_upgrade_replorigin_advance SQL function to restore the
underlying replication origin remote LSN. This is supported only in
binary upgrade mode.

pg_upgrade will check that all the subscription relations are in 'i'
(init) or in 'r' (ready) state and will error out if that's not the case,
logging the reason for the failure. This helps to avoid the risk of any
dangling slot or origin after the upgrade.

Author: Vignesh C, Julien Rouhaud, Shlok Kyal
Reviewed-by: Peter Smith, Masahiko Sawada, Michael Paquier, Amit Kapila, Hayato Kuroda
Discussion: https://postgr.es/m/20230217075433.u5mjly4d5cr4hcfe@jrouhaud

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/9a17be1e244a45a77de25ed2ada246fd34e4557d

Modified Files
--------------
doc/src/sgml/ref/pgupgrade.sgml            |  50 +++++
src/backend/catalog/pg_subscription.c      |  16 +-
src/backend/commands/subscriptioncmds.c    |   4 +-
src/backend/utils/adt/pg_upgrade_support.c | 106 ++++++++++
src/bin/pg_dump/common.c                   |  22 ++
src/bin/pg_dump/pg_dump.c                  | 229 ++++++++++++++++++++-
src/bin/pg_dump/pg_dump.h                  |  24 +++
src/bin/pg_dump/pg_dump_sort.c             |  11 +-
src/bin/pg_upgrade/check.c                 | 193 ++++++++++++++++-
src/bin/pg_upgrade/info.c                  |  56 ++++-
src/bin/pg_upgrade/meson.build             |   1 +
src/bin/pg_upgrade/pg_upgrade.h            |   2 +
src/bin/pg_upgrade/t/004_subscription.pl   | 319 +++++++++++++++++++++++++++++
src/include/catalog/catversion.h           |   2 +-
src/include/catalog/pg_proc.dat            |  10 +
src/include/catalog/pg_subscription_rel.h  |   2 +-
src/tools/pgindent/typedefs.list           |   1 +
17 files changed, 1032 insertions(+), 16 deletions(-)

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 15 Feb 2024 14:45:11 +0100

> Allow upgrades to preserve the full subscription's state.

The added test src/bin/pg_upgrade/t/004_subscription.pl does not pass when

export PG_TEST_PG_UPGRADE_MODE=--link

is set.

Could you check whether that could be fixed, or otherwise if the test 
needs to be skipped in that case?

---

From: Amit Kapila <[email-masked]>
Date: Fri, 16 Feb 2024 08:29:38 +0530

> needs to be skipped in that case?

Thanks for the report. We are working on a fix for it [1].

[1] - https://www.postgresql.org/message-id/TYCPR01MB120773E8D02D58F50B24DA6ADF54E2%40TYCPR01MB12077.jpnprd01.prod.outlook.com

-- 
With Regards,
Amit Kapila.