MIME-Version: 1.0
References: 
 <CAFPTHDa5aP8ixTZBygGAe48E1N=DvuPzcXikw7bNKixLMN3pVg@mail.gmail.com>
 <OS9PR01MB12149E188221BED862E4BE9E0F5342@OS9PR01MB12149.jpnprd01.prod.outlook.com>
 <CALDaNm2-uwpbJ8fnrssp+hORvOutsqRoZAsa05xVVzXe5Bt3bw@mail.gmail.com>
 <CAFPTHDaNLRqPAda1RUDVfSDH7eLTaONv0UEmc9H5sdMW1Li2Bg@mail.gmail.com>
 <CAN4CZFOb3urMsLPsEyVrYR7-7yA+BC5kDgQQd0nAQ8xj2zyRcA@mail.gmail.com>
 <CAFPTHDYqpuZ6o2-HuCJDYqJ7GY3+zV+Xo-gT7PAgi4Bkz+oTxw@mail.gmail.com>
 <CAN4CZFN4oLdxLwUXHUPV-5mFmK+4dcnppP00fV3i4qmMYCAkGA@mail.gmail.com>
 <CAFPTHDbBiTfjYkQwYTNA2a4LRp8Sp7_zB59fmV0R7977ztxgmg@mail.gmail.com>
 <CAFPTHDaftSwzGTGbFEw8rwDBsub0XqcDm1wxQcquj-Y3PC2qrg@mail.gmail.com>
 <CANhcyEVercsNbgC5CTkMNzh_w_Jv1uK7RQg9vEvAeNQhoBHCKQ@mail.gmail.com>
 <CAJpy0uAXynzWr4w_9GkJC3i=LL9WsRCZYVWAV0PgKmgs8riWzg@mail.gmail.com>
 <CAFPTHDavPyxsWjK0cRO3yOaP4u8FGYrOJuXJB-4wzneAY3H3Ug@mail.gmail.com>
 <CANhcyEVFSutXXdt_0XMCD37NE7Yd7ROdDVEE06D8YVyPNEBFHg@mail.gmail.com>
 <CAJpy0uD-yDM_ViZm5O+ok3HHMGMnqtdW=oOsuU7SHm9QpNx01w@mail.gmail.com>
In-Reply-To: 
 <CAJpy0uD-yDM_ViZm5O+ok3HHMGMnqtdW=oOsuU7SHm9QpNx01w@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Mon, 15 Jun 2026 17:01:21 +0530
Message-ID: 
 <CAA4eK1KzGSANd=g1S8xpcyiwh3M-c+Uaa4+447v-+N8chX7K3g@mail.gmail.com>
Subject: Re: [PATCH] Preserve replication origin OIDs in pg_upgrade
To: shveta malik <shveta.malik@gmail.com>
Cc: Shlok Kyal <shlok.kyal.oss@gmail.com>,
 Dilip Kumar <dilipbalaut@gmail.com>,
	Ajin Cherian <itsajin@gmail.com>, Zsolt Parragi <zsolt.parragi@percona.com>,
	vignesh C <vignesh21@gmail.com>,
 "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1KzGSANd%3Dg1S8xpcyiwh3M-c%2BUaa4%2B447v-%2BN8chX7K3g%40mail.gmail.com>
Precedence: bulk

On Thu, Jun 11, 2026 at 12:08=E2=80=AFPM shveta malik <shveta.malik@gmail.c=
om> wrote:
>
> Ajin, Kuroda-san, and I discussed several possible approaches to address =
this:
>
> 1)
> Add a new subscription option, for example 'skip_origin_creation'.
> When set to true, CREATE SUBSCRIPTION would skip replication-origin
> creation. During binary upgrade, pg_dump would emit
> skip_origin_creation =3D true for source clusters running PG17 or later,
> and false otherwise.
>
> However, this approach requires introducing a new user-visible CREATE
> SUBSCRIPTION option.
>
> 2)
> Another idea could be to have a
> 'binary_upgrade_skip_subs_origin_creation' variable. We populate it
> during dumpSubscription if 'source_version >=3D PG17' and
> 'IsBinaryUpgrade' is true. So this will be dumped
>
> SELECT pg_catalog.binary_upgrade_set_skip_subs_origin_creation(true);
> CREATE SUBSCRIPTION ...
> SELECT pg_catalog.binary_upgrade_set_skip_subs_origin_creation(false);
>
> First call will set 'binary_upgrade_skip_subs_origin_creation' which
> will be used by Create Subscription to decide to skip origin. This is
> similar to how dumpACl decides whether to record priveleges into
> 'pg_init_privs' for objectes created by extensions. See
> binary_upgrade_set_record_init_privs() which sets
> binary_upgrade_record_init_privs which is then used by
> recordExtensionInitPriv().
>
> 3)
> Create origins and preserve origin IDs regardless of source version
>
> The current patch preserves origin IDs for upgrades from PG17 and later.
>
> One reason for choosing PG17 as the starting point for preserving
> origin-ids is that complete logical-replication setup migration was
> introduced in PG17. Prior to PG17, subscription state
> (pg_subscription_rel) and replication slots were not preserved during
> upgrade, so limiting origin-ID preservation to PG17 and later aligns
> with the broader logical-replication migration support.
>
> OTOH, replication-origin migration itself is supported for PG16
> upgrades. For PG16- upgrades (without this patch), subscriptions
> itself can be migrated, and corresponding origins can be created at
> that time. Therefore, even on versions prior to Pg17, it may also be
> reasonable to consider preserving origin IDs independently of the rest
> of the logical-replication configuration migration, and not tie it to
> the PG17 boundary.
>
> If we choose that approach, check_new_cluster_replication_origins()
> would likely need to run for upgrades from PG16 (and prior) as well.
> Subscription-ids also need to be preserved for the same versions. For
> reference, check_new_cluster_replication_slots() and
> check_new_cluster_subscription_configuration() currently run only for
> PG17 and later.
> ~~
>
> Given the need for a new user-visible parameter, I would rule out
> option 1. Between options 2 and 3, I prefer option 2, but I would be
> interested in hearing others' opinions.
>

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 >=3D 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.

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.

--=20
With Regards,
Amit Kapila.