Re: [PATCH] Preserve replication origin OIDs in pg_upgrade

On Tue, Jun 16, 2026 at 2:50 PM Ajin Cherian <itsajin(at)gmail(dot)com> wrote:
>
> On Mon, Jun 15, 2026 at 9:31 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
>>
>> Separately, I don't think we actually need to preserve the
>> subscription OID to meet the goal here. pg_commit_ts stores the
>> numeric roident, not the origin name or the sub OID, and conflict
>> detection compares roidents. So the invariant we must keep is "each
>> subscription owns the same roident after upgrade", which doesn't
>> depend on the name being stable.
>>
>>
>
> Replication origins are global objects and are therefore dumped by pg_dumpall, while subscriptions are database-specific and are dumped by pg_dump. In pg_upgrade, pg_dumpall runs before pg_dump.
>
> The complication if subscription OID is not preserved is that subscription-associated origins derive their name from the subscription's OID. If the subscription OID is not preserved across upgrades, the origin name on the new cluster will differ from the old one, making it impossible to restore the origin independently. For these origins, creation must happen after CreateSubscription has established the new OID and not upfront in pg_dumpall.
>
> Non-subscription origins have no such dependency and continue to be created separately as before. The subscription code also cannot be left untouched: even once the subscription is created, rather than origin being created inside CreateSubscription, its associated origin must still be explicitly created via binary_upgrade_create_replication_origin to ensure the roident matches the original node, which means CreateSubscription needs to be modified anyways. So, there is an advantage of reducing code if subscription OID is preserved and all origins keep the same name as the old node.
>

I think here the key is not reducing the code foot print in
CreateSubscription rather the non-subscription origins anyway needs to
be created separately by pg_dumpall as they don't have any binding
with subscription. So, if we don't preserve sub OIDs, we will never be
able to match the names of origins created with pg_dumpall and then
later used by subscriptions. I feel this reasoning should be captured
in the commit message.

--
With Regards,
Amit Kapila.