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>
 <CAA4eK1KzGSANd=g1S8xpcyiwh3M-c+Uaa4+447v-+N8chX7K3g@mail.gmail.com>
 <CAFPTHDYgyDE4+8tM4-d3Gcyg1E4VuQJhmNh9Bwis_W9NKxw0tA@mail.gmail.com>
In-Reply-To: 
 <CAFPTHDYgyDE4+8tM4-d3Gcyg1E4VuQJhmNh9Bwis_W9NKxw0tA@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Fri, 19 Jun 2026 09:29:47 +0530
Message-ID: 
 <CAJpy0uC6YcujsOC++wAAV4oBL=VFqSCYmTiLjkdUBywE5oX=xw@mail.gmail.com>
Subject: Re: [PATCH] Preserve replication origin OIDs in pg_upgrade
To: Ajin Cherian <itsajin@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>
Cc: Shlok Kyal <shlok.kyal.oss@gmail.com>,
 Dilip Kumar <dilipbalaut@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>,
 shveta malik <shveta.malik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAJpy0uC6YcujsOC%2B%2BwAAV4oBL%3DVFqSCYmTiLjkdUBywE5oX%3Dxw%40mail.gmail.com>
Precedence: bulk

On Tue, Jun 16, 2026 at 2:50=E2=80=AFPM Ajin Cherian <itsajin@gmail.com> wr=
ote:
>
>
>
> On Mon, Jun 15, 2026 at 9:31=E2=80=AFPM Amit Kapila <amit.kapila16@gmail.=
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 >=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.
>>
>
> 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_dum=
pall, 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 subscriptio=
n-associated origins derive their name from the subscription's OID. If the =
subscription OID is not preserved across upgrades, the origin name on the n=
ew cluster will differ from the old one, making it impossible to restore th=
e origin independently. For these origins, creation must happen after Creat=
eSubscription has established the new OID and not upfront in pg_dumpall.
>
> Non-subscription origins have no such dependency and continue to be creat=
ed separately as before. The subscription code also cannot be left untouche=
d: even once the subscription is created, rather than origin being created =
inside CreateSubscription, its associated origin must still be explicitly c=
reated via binary_upgrade_create_replication_origin to ensure the roident m=
atches the original node, which means CreateSubscription needs to be modifi=
ed 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