Re: [PATCH] Preserve replication origin OIDs in pg_upgrade

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.

regards,
Ajin Cherian
Fujitsu Australia