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>
In-Reply-To: 
 <CAA4eK1KzGSANd=g1S8xpcyiwh3M-c+Uaa4+447v-+N8chX7K3g@mail.gmail.com>
From: Ajin Cherian <itsajin@gmail.com>
Date: Tue, 16 Jun 2026 19:20:01 +1000
Message-ID: 
 <CAFPTHDYgyDE4+8tM4-d3Gcyg1E4VuQJhmNh9Bwis_W9NKxw0tA@mail.gmail.com>
Subject: Re: [PATCH] Preserve replication origin OIDs in pg_upgrade
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>,
 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>
Content-Type: multipart/alternative; boundary="0000000000004a0a7e06545b73f0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFPTHDYgyDE4%2B8tM4-d3Gcyg1E4VuQJhmNh9Bwis_W9NKxw0tA%40mail.gmail.com>
Precedence: bulk

--0000000000004a0a7e06545b73f0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jun 15, 2026 at 9:31=E2=80=AFPM Amit Kapila <amit.kapila16@gmail.co=
m> 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_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

--0000000000004a0a7e06545b73f0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Jun 15,=
 2026 at 9:31=E2=80=AFPM Amit Kapila &lt;<a href=3D"mailto:amit.kapila16@gm=
ail.com">amit.kapila16@gmail.com</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><br>
<br>
I&#39;d prefer option-3 (preserve origin IDs uniformly, including upgrades<=
br>
from PG16), for two reasons. (a) First, the new-cluster check (&quot;reject=
<br>
if the new cluster already has replication origins&quot;) is a restriction<=
br>
on the target, and adding restrictions on a newer target version<br>
regardless of source version is well within how pg_upgrade already<br>
behaves. In practice the new cluster is a fresh initdb with no<br>
origins, so the check effectively never fires; the only scenario it<br>
rejects is one where the user manually created subscriptions/origins<br>
on the new cluster before upgrading, which we discourage for other<br>
object types anyway. So I don&#39;t think preserving existing behavior<br>
here is worth much. (b) Second, option-2 reintroduces source-version<br>
branching at dump time (the skip_subs_origin_creation flag gated on<br>
source &gt;=3D 17). Since pg_commit_ts is only copied from PG19+, the exact=
<br>
roident value only matters from PG19; for PG16-18 the value is<br>
don&#39;t-care, so preserving it everywhere is harmless where it isn&#39;t<=
br>
needed and required where it is. That lets us keep a single<br>
always-preserve path instead of branching per source version.<br>
<br></blockquote><div><br></div><div>Ok.</div><div>=C2=A0</div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex">
Separately, I don&#39;t think we actually need to preserve the<br>
subscription OID to meet the goal here. pg_commit_ts stores the<br>
numeric roident, not the origin name or the sub OID, and conflict<br>
detection compares roidents. So the invariant we must keep is &quot;each<br=
>
subscription owns the same roident after upgrade&quot;, which doesn&#39;t<b=
r>
depend on the name being stable.<br>
<br><br></blockquote><div><br></div><div>Replication origins are global obj=
ects and are therefore dumped by pg_dumpall, while subscriptions are databa=
se-specific and are dumped by pg_dump. In pg_upgrade, pg_dumpall runs befor=
e pg_dump.<br><br>The complication if subscription OID is not preserved is =
that subscription-associated origins derive their name from the subscriptio=
n&#39;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 impo=
ssible to restore the origin independently. For these origins, creation mus=
t happen after CreateSubscription has established the new OID and not upfro=
nt in pg_dumpall.<br><br>Non-subscription origins have no such dependency a=
nd continue to be created separately as before. The subscription code also =
cannot be left untouched: even once the subscription is created, rather tha=
n origin being created inside CreateSubscription, its associated origin mus=
t still be explicitly created via binary_upgrade_create_replication_origin =
to ensure the roident matches the original node, which means CreateSubscrip=
tion needs to be modified anyways. So, there is an advantage of reducing co=
de if subscription OID is preserved and all origins keep the same name as t=
he old node.<br><br>regards,<br>Ajin Cherian<br>Fujitsu Australia=C2=A0</di=
v></div></div>

--0000000000004a0a7e06545b73f0--