Hello hackers, The idea for this patch came up during discussions in the thread [1] on migration of the pg_commit_ts directory as part of pg_upgrade. There was a problem raised by Sawada-san in that thread which this patch addresses. [2]
The problem: The pg_commit_ts directory stores commit-timestamp records for each transaction, and each record embeds the replication origin ID (roident) that identifies which subscription wrote that transaction. When pg_upgrade migrates a subscriber, the pg_commit_ts directory is copied directly from the old cluster to the new cluster. This means those embedded roidents must remain valid in the new cluster. When pg_upgrade migrates a subscriber, CREATE SUBSCRIPTION on the new cluster calls replorigin_create() which assigns fresh roidents to each subscription's replication origin. Because subscription OIDs are not stable across upgrades, the origin names change (e.g. pg_16392 becomes pg_16403), and consequently the roidents can be assigned differently — or in the worst case, swapped between subscriptions. Consider two subscriptions subA and subB with roidents 1 and 2 respectively before upgrade. After upgrade, due to OID reassignment, subA might get roident 2 and subB might get roident 1. The commit-timestamp records copied from the old cluster still say roident 1 for rows written by subA, but the new cluster now thinks roident 1 belongs to subB. This causes spurious update_origin_differs conflicts — the new cluster incorrectly thinks a row was last modified by a different subscription than it actually was. This patch attempts to fix this by replicating the roident of the replication origins of each subscription on migration. This patch also migrates all replication origins as part of pg_upgrade. Sequence of Events During Upgrade 1. pg_dumpall dumps all non-subscription replication origins from the old cluster with their roidents and LSN positions. 2. pg_dump dumps each subscription, but now records the old roident alongside the subscription info. 3. During restore, pg_dumpall's output recreates non-subscription origins on the new cluster with their original roidents via binary_upgrade_create_replication_origin(). 4. During per-database restore, CREATE SUBSCRIPTION runs but skips origin creation. 5. binary_upgrade_set_next_replorigin_oid() creates the origin for each subscription with the preserved roident. 6. binary_upgrade_replorigin_advance() restores the LSN position for each subscription. 7. Subscriptions that were running before upgrade are re-enabled. Please let me know your feedback regarding this patch [1] - https://www.postgresql.org/message-id/flat/182311743703924%40mail.yandex.ru [2] - https://www.postgresql.org/message-id/CAD21AoDG8zQpHHfw7OvaEy7W0ZSyP%3D_dS-hrcquJ3C_ctMDmMQ%40mail.gmail.com regards, Ajin Cherian Fujitsu Australia
v2-0001-Preserve-replication-origin-OIDs-in-pg_upgrade.patch
Description: Binary data
