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:
>>
>>
>>
>> I'd prefer option-3 (preserve origin IDs uniformly, including upgrades
>> from PG16), for two reasons. (a) First, the new-cluster check ("reject
>> if the new cluster already has replication origins") is a restriction
>> on the target, and adding restrictions on a newer target version
>> regardless of source version is well within how pg_upgrade already
>> behaves. In practice the new cluster is a fresh initdb with no
>> origins, so the check effectively never fires; the only scenario it
>> rejects is one where the user manually created subscriptions/origins
>> on the new cluster before upgrading, which we discourage for other
>> object types anyway. So I don't think preserving existing behavior
>> here is worth much. (b) Second, option-2 reintroduces source-version
>> branching at dump time (the skip_subs_origin_creation flag gated on
>> source >= 17). Since pg_commit_ts is only copied from PG19+, the exact
>> roident value only matters from PG19; for PG16-18 the value is
>> don't-care, so preserving it everywhere is harmless where it isn't
>> needed and required where it is. That lets us keep a single
>> always-preserve path instead of branching per source version.
>>
>
> Ok.
>
>>
>> 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.
>

IIUC, the conclusion (and what's implemented in v8) is:

1) The origin-ids are preserved on upgraded version irrespective of
source version.
2) Since origin-ids preservation requires sub-oid preservation due to
the origin-name's dependency on the sub-oid, we preserve sub-oid too
irrespective of source-version.

Let's see what Amit has to say about this.

thanks
Shveta