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>
 <CANhcyEVd7eDKGtsrUqwZO5McTG4dsJ+X8OxO31Xgu6sWmo--hw@mail.gmail.com>
 <CAFPTHDbyVt_ggdi+rOP4=jBmgU=DDgYWuLxc4oQ6ux-T0H-ZBg@mail.gmail.com>
 <CAHWVJhGoi+2bzZDNxtnB8Xc+DuFKB=YvqC0Xoc_HfzcgHAwMUg@mail.gmail.com>
 <CAFPTHDbWKQjv4QfHaNiXosZ1TD0ZHc2hFyFFNB2RvLsJeJoH6g@mail.gmail.com>
 <CAN4CZFPdFHwnew+wxt4UcXyjMYc3aXeR0XsR6_wZ=LEMGWEJFQ@mail.gmail.com>
 <CAFPTHDYfDHQeQshXi7GDOQz3N8YdY6SNSmpoSAbnaQPBkVfv6A@mail.gmail.com>
 <CANhcyEVV5A5qm9g7UVT99NpNqVsuzbzLsoteQ5gYOWvVsZsjCQ@mail.gmail.com>
In-Reply-To: 
 <CANhcyEVV5A5qm9g7UVT99NpNqVsuzbzLsoteQ5gYOWvVsZsjCQ@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Wed, 8 Jul 2026 09:31:59 +0530
Message-ID: 
 <CAJpy0uAsgc5--4dCut2mmQd+Yxe-jfVQR=AY38dzeEe0+yaH4g@mail.gmail.com>
Subject: Re: [PATCH] Preserve replication origin OIDs in pg_upgrade
To: Shlok Kyal <shlok.kyal.oss@gmail.com>
Cc: Ajin Cherian <itsajin@gmail.com>,
 Zsolt Parragi <zsolt.parragi@percona.com>,
	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/CAJpy0uAsgc5--4dCut2mmQd%2BYxe-jfVQR%3DAY38dzeEe0%2ByaH4g%40mail.gmail.com>
Precedence: bulk

On Mon, Jul 6, 2026 at 12:10=E2=80=AFPM Shlok Kyal <shlok.kyal.oss@gmail.co=
m> wrote:
>
> > All the above changes are updated in patch v10.
> >
> I noticed that we can create user defined replication origin even if the
> total count of replication origins exceeds the max_active_replication_ori=
gins
> Example:
> postgres=3D# show max_active_replication_origins;
>  max_active_replication_origins
> --------------------------------
>  1
>
> postgres=3D# select * from pg_replication_origin;
>  roident |  roname
> ---------+----------
>        1 | pg_16387
>
> postgres=3D# SELECT pg_replication_origin_create('origin1');
>  pg_replication_origin_create
> ------------------------------
>                             2
>
> postgres=3D# select * from pg_replication_origin;
>  roident |  roname
> ---------+----------
>        1 | pg_16387
>        2 | origin1
>
> postgres=3D# select * from pg_replication_origin_status;
>  local_id | external_id | remote_lsn | local_lsn
> ----------+-------------+------------+------------
>         1 | pg_16387    | 0/00000000 | 0/00000000
>
> The documentation for max_active_replication_origins says:
> ```
> Specifies how many replication origins (see
> <xref linkend=3D"replication-origins"/>) can be tracked simultaneously,
> effectively limiting how many logical replication subscriptions can
> be created on the server. Setting it to a lower value than the current
> number of tracked replication origins (reflected in
> <link linkend=3D"view-pg-replication-origin-status">pg_replication_origin=
_status</link>)
> will prevent the server from starting. It defaults to 10. This parameter
> can only be set at server start.
> ```
> Since the user-created replication origin above is not
> actively tracked in pg_replication_origin_status, no error is reported.
>
> But with this patch, during pg_upgrade we are checking the
> 'max_active_replication_origins' against 'old_cluster.nrepl_origins'
> + if (old_cluster.nrepl_origins > max_active_replication_origins)
>   pg_fatal("\"max_active_replication_origins\" (%d) must be greater
> than or equal to the number of "
> - "subscriptions (%d) in the old cluster",
> - max_active_replication_origins, old_cluster.nsubs);
> + "replication origins (%d) in the old cluster",
> + max_active_replication_origins, old_cluster.nrepl_origins);
>
> Since old_cluster.nrepl_origins counts all replication origins, including
> user-created ones that may not be tracked, should this check instead comp=
are
> max_active_replication_origins with 'the number of rows in
> pg_replication_origin_status in old cluster' (i.e., the number of
> tracked replication origins)?
>

Thanks Shlok for exploring this scenario.

I had a look at it. Please find my analysis.

I was under the assumption that subscription creation (which
internally creates a replication origin) would fail if
max_active_replication_origins was exceeded. However, that is not the
case. See the following experiment:

postgres=3D# show max_active_replication_origins;
 max_active_replication_origins
--------------------------------
 2

Al these commands succeed:

postgres=3D# create subscription sub1 ...
postgres=3D# create subscription sub2 ..
postgres=3D#  SELECT pg_replication_origin_create('user_origin1');
postgres=3D#  SELECT pg_replication_origin_create('user_origin2');
postgres=3D#  SELECT pg_replication_origin_create('user_origin3');
postgres=3D#  SELECT pg_replication_origin_create('user_origin4');
postgres=3D# create subscription sub3 ..
postgres=3D# create subscription sub4...

postgres=3D# select * from pg_replication_origin_status;
 local_id | external_id | remote_lsn | local_lsn
----------+-------------+------------+------------
        1 | pg_16384    | 0/00000000 | 0/00000000
        2 | pg_16385    | 0/00000000 | 0/00000000
(2 rows)


postgres=3D# select * from pg_replication_origin;
 roident |    roname
---------+--------------
       1 | pg_16384
       2 | pg_16385
       3 | user_origin1
       4 | user_origin2
       5 | user_origin3
       6 | user_origin4
       7 | pg_16387
       8 | pg_16388
(8 rows)

As shown above, sub3 and sub4 are created successfully, but their
replication origins are not tracked because there are no free active
replication origin slots. The apply workers for these subscriptions
also fail to start:

LOG:  logical replication apply worker for subscription "sub3" has started
ERROR:  could not find free replication state slot for replication
origin with ID 7
HINT:  Increase "max_active_replication_origins" and try again.
LOG:  background worker "logical replication apply worker" (PID
277193) exited with exit code 1

This means that if we implement the check suggested by Shlok:

Although pg_upgrade would succeed, it could leave some subscriptions
on the new cluster in non-functional state. I agree that this
situation (non-functional subscriptions) is also possible on the old
cluster. However, this would also deviate from the previous
implementation, where check_new_cluster_subscription_configuration()
compared old_cluster.nsubs against 'max_active_replication_origins' on
the new cluster, rather than comparing the number of entries in
pg_replication_origin_status.

For this reason, my inclination is to revert to the previous
implementation, where we compare old_cluster.nsubs with
max_active_replication_origins. But I'd like to hear what others think
before we make this change.

thanks
Shveta