pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRead Replica termination occurs when its max_active_replication_origins setting is lower than the primary
8+ messages / 4 participants
[nested] [flat]
* Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-16 22:52 Nazneen Jafri <jafrinazneen@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nazneen Jafri @ 2025-09-16 22:52 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
The parameter max_active_replication_origins should be added to the list of
mandatory settings that must match between primary and replica during
creation
In our three-node setup:
The Publisher (Primary) is the source database
The Subscriber receives data via logical replication from the Publisher
The Read Replica is created as a hot standby of the Subscriber node
Issue: When the Read Replica's max_active_replication_origins setting is
lower than the Publisher's, replica termination occurs.
The max_active_replication_origins parameter should be added to mandatory
configuration checks
Repro
Publisher Node
wal_level logical
max_active_replication_origins 10
[postgres@ip-~]$ psql
psql (19devel)
Type "help" for help.
postgres=# select * from test_table;
id | data | created_at
----+---------------------+----------------------------
7 | Initial test data 1 | 2025-09-16 19:51:24.658967
8 | Initial test data 2 | 2025-09-16 19:51:24.658967
9 | Initial test data 3 | 2025-09-16 19:51:24.658967
10 | Initial test data 4 | 2025-09-16 20:32:51.567525
11 | Initial test data 5 | 2025-09-16 20:32:51.567525
12 | Initial test data 6 | 2025-09-16 20:32:51.567525
(6 rows)
Subscriber Node
wal_level logical
max_active_replication_origins 10
We have 2 subscriptions
postgres=# select * from pg_stat_subscription;
subid | subname | worker_type | pid | leader_pid | relid | received_lsn
| last_msg_send_time | last_msg_receipt_time |
latest_end_lsn | latest_end_time
-------+---------+-------------+-------+------------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
16413 | sub1 | apply | 67783 | | | 0/0485A188
| 2025-09-16 20:37:57.948905+00 | 2025-09-16 20:37:57.949141+00 |
0/0485A188 | 2025-09-16 20:37:57.948905+00
16414 | sub2 | apply | 67785 | | | 0/0485A188
| 2025-09-16 20:37:57.948896+00 | 2025-09-16 20:37:57.949146+00 |
0/0485A188 | 2025-09-16 20:37:57.948896+00
16415 | sub3 | apply | 67788 | | | 0/0485A188
| 2025-09-16 20:37:57.948929+00 | 2025-09-16 20:37:57.949178+00 |
0/0485A188 | 2025-09-16 20:37:57.948929+00
(3 rows)
postgres=# select * from test_table;
id | data | created_at
----+---------------------+----------------------------
7 | Initial test data 1 | 2025-09-16 19:51:24.658967
8 | Initial test data 2 | 2025-09-16 19:51:24.658967
9 | Initial test data 3 | 2025-09-16 19:51:24.658967
10 | Initial test data 4 | 2025-09-16 20:32:51.567525
11 | Initial test data 5 | 2025-09-16 20:32:51.567525
12 | Initial test data 6 | 2025-09-16 20:32:51.567525
(6 rows)
Hot_standby (Read replica) node
max_active_replication_origins 10
postgres@ip-]$ psql
psql (19devel)
Type "help" for help.
postgres=#
postgres=# select * from test_table;
id | data | created_at
----+---------------------+----------------------------
7 | Initial test data 1 | 2025-09-16 19:51:24.658967
8 | Initial test data 2 | 2025-09-16 19:51:24.658967
9 | Initial test data 3 | 2025-09-16 19:51:24.658967
10 | Initial test data 4 | 2025-09-16 20:32:51.567525
11 | Initial test data 5 | 2025-09-16 20:32:51.567525
12 | Initial test data 6 | 2025-09-16 20:32:51.567525
(6 rows)
Now, when we lower the setting for max_active_replication_origins to 1
(less than the number of subscriptions) on the replica and attempting a
reload, the replica aborts and fails to start
2025-09-16 20:38:26.457 UTC [59579] PANIC: could not find free replication
state, increase "max_active_replication_origins"
2025-09-16 20:38:34.329 UTC [59563] LOG: startup process (PID 59579) was
terminated by signal 6: Aborted
2025-09-16 20:38:34.329 UTC [59563] LOG: aborting startup due to startup
process failure
2025-09-16 20:38:34.330 UTC [59563] LOG: database system is shut down
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-16 23:42 Masahiko Sawada <sawada.mshk@gmail.com>
parent: Nazneen Jafri <jafrinazneen@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Masahiko Sawada @ 2025-09-16 23:42 UTC (permalink / raw)
To: Nazneen Jafri <jafrinazneen@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Tue, Sep 16, 2025 at 3:52 PM Nazneen Jafri <jafrinazneen@gmail.com> wrote:
>
> The parameter max_active_replication_origins should be added to the list of mandatory settings that must match between primary and replica during creation
>
> In our three-node setup:
>
> The Publisher (Primary) is the source database
> The Subscriber receives data via logical replication from the Publisher
> The Read Replica is created as a hot standby of the Subscriber node
> Issue: When the Read Replica's max_active_replication_origins setting is lower than the Publisher's, replica termination occurs.
> The max_active_replication_origins parameter should be added to mandatory configuration checks
>
> Repro
>
> Publisher Node
> wal_level logical
> max_active_replication_origins 10
>
> [postgres@ip-~]$ psql
> psql (19devel)
> Type "help" for help.
>
> postgres=# select * from test_table;
> id | data | created_at
> ----+---------------------+----------------------------
> 7 | Initial test data 1 | 2025-09-16 19:51:24.658967
> 8 | Initial test data 2 | 2025-09-16 19:51:24.658967
> 9 | Initial test data 3 | 2025-09-16 19:51:24.658967
> 10 | Initial test data 4 | 2025-09-16 20:32:51.567525
> 11 | Initial test data 5 | 2025-09-16 20:32:51.567525
> 12 | Initial test data 6 | 2025-09-16 20:32:51.567525
> (6 rows)
>
> Subscriber Node
>
> wal_level logical
> max_active_replication_origins 10
>
> We have 2 subscriptions
>
> postgres=# select * from pg_stat_subscription;
> subid | subname | worker_type | pid | leader_pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
> -------+---------+-------------+-------+------------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
> 16413 | sub1 | apply | 67783 | | | 0/0485A188 | 2025-09-16 20:37:57.948905+00 | 2025-09-16 20:37:57.949141+00 | 0/0485A188 | 2025-09-16 20:37:57.948905+00
> 16414 | sub2 | apply | 67785 | | | 0/0485A188 | 2025-09-16 20:37:57.948896+00 | 2025-09-16 20:37:57.949146+00 | 0/0485A188 | 2025-09-16 20:37:57.948896+00
> 16415 | sub3 | apply | 67788 | | | 0/0485A188 | 2025-09-16 20:37:57.948929+00 | 2025-09-16 20:37:57.949178+00 | 0/0485A188 | 2025-09-16 20:37:57.948929+00
> (3 rows)
>
> postgres=# select * from test_table;
> id | data | created_at
> ----+---------------------+----------------------------
> 7 | Initial test data 1 | 2025-09-16 19:51:24.658967
> 8 | Initial test data 2 | 2025-09-16 19:51:24.658967
> 9 | Initial test data 3 | 2025-09-16 19:51:24.658967
> 10 | Initial test data 4 | 2025-09-16 20:32:51.567525
> 11 | Initial test data 5 | 2025-09-16 20:32:51.567525
> 12 | Initial test data 6 | 2025-09-16 20:32:51.567525
> (6 rows)
>
> Hot_standby (Read replica) node
> max_active_replication_origins 10
>
> postgres@ip-]$ psql
> psql (19devel)
> Type "help" for help.
>
>
> postgres=#
> postgres=# select * from test_table;
> id | data | created_at
> ----+---------------------+----------------------------
> 7 | Initial test data 1 | 2025-09-16 19:51:24.658967
> 8 | Initial test data 2 | 2025-09-16 19:51:24.658967
> 9 | Initial test data 3 | 2025-09-16 19:51:24.658967
> 10 | Initial test data 4 | 2025-09-16 20:32:51.567525
> 11 | Initial test data 5 | 2025-09-16 20:32:51.567525
> 12 | Initial test data 6 | 2025-09-16 20:32:51.567525
> (6 rows)
>
>
>
> Now, when we lower the setting for max_active_replication_origins to 1 (less than the number of subscriptions) on the replica and attempting a reload, the replica aborts and fails to start
>
> 2025-09-16 20:38:26.457 UTC [59579] PANIC: could not find free replication state, increase "max_active_replication_origins"
> 2025-09-16 20:38:34.329 UTC [59563] LOG: startup process (PID 59579) was terminated by signal 6: Aborted
> 2025-09-16 20:38:34.329 UTC [59563] LOG: aborting startup due to startup process failure
> 2025-09-16 20:38:34.330 UTC [59563] LOG: database system is shut down
>
Thank you for the report!
As reported, the standby could not continue the recovery (especially
replaying XLOG_REPLORIGIN_ records) if its
max_active_replication_origins is less than the primary's setting. One
idea to fix this issue is to require for standbys to have at least the
same max_active_replication_origins value as the primary as we do for
other GUC parameters such as max_worker_processes and max_wal_senders.
It needs to add max_active_replication_origins to the control file and
bumps the PG_CONTROL_VERSION. Given that we've released 18RC1 and
probably are close to 18 release, I'd like to hear opinions whether
such a fix is acceptable or not.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-17 02:45 Nathan Bossart <nathandbossart@gmail.com>
parent: Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2025-09-17 02:45 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Nazneen Jafri <jafrinazneen@gmail.com>; pgsql-bugs@lists.postgresql.org
On Tue, Sep 16, 2025 at 04:42:39PM -0700, Masahiko Sawada wrote:
> On Tue, Sep 16, 2025 at 3:52 PM Nazneen Jafri <jafrinazneen@gmail.com> wrote:
>> The parameter max_active_replication_origins should be added to the list
>> of mandatory settings that must match between primary and replica during
>> creation
>>
>> [...]
>
> Thank you for the report!
>
> As reported, the standby could not continue the recovery (especially
> replaying XLOG_REPLORIGIN_ records) if its
> max_active_replication_origins is less than the primary's setting. One
> idea to fix this issue is to require for standbys to have at least the
> same max_active_replication_origins value as the primary as we do for
> other GUC parameters such as max_worker_processes and max_wal_senders.
> It needs to add max_active_replication_origins to the control file and
> bumps the PG_CONTROL_VERSION. Given that we've released 18RC1 and
> probably are close to 18 release, I'd like to hear opinions whether
> such a fix is acceptable or not.
I haven't tried reproducing it on older versions (with
max_replication_slots instead of max_active_replication_origins), but after
looking at the code for a bit, I'm growing skeptical that this is new to
v18. In any case, the PANIC provides a clear error message, which is
roughly the same as what we'd say with the control file approach, right?
--
nathan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-17 04:05 Masahiko Sawada <sawada.mshk@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Masahiko Sawada @ 2025-09-17 04:05 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Nazneen Jafri <jafrinazneen@gmail.com>; pgsql-bugs@lists.postgresql.org
On Tue, Sep 16, 2025 at 7:45 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Tue, Sep 16, 2025 at 04:42:39PM -0700, Masahiko Sawada wrote:
> > On Tue, Sep 16, 2025 at 3:52 PM Nazneen Jafri <jafrinazneen@gmail.com> wrote:
> >> The parameter max_active_replication_origins should be added to the list
> >> of mandatory settings that must match between primary and replica during
> >> creation
> >>
> >> [...]
> >
> > Thank you for the report!
> >
> > As reported, the standby could not continue the recovery (especially
> > replaying XLOG_REPLORIGIN_ records) if its
> > max_active_replication_origins is less than the primary's setting. One
> > idea to fix this issue is to require for standbys to have at least the
> > same max_active_replication_origins value as the primary as we do for
> > other GUC parameters such as max_worker_processes and max_wal_senders.
> > It needs to add max_active_replication_origins to the control file and
> > bumps the PG_CONTROL_VERSION. Given that we've released 18RC1 and
> > probably are close to 18 release, I'd like to hear opinions whether
> > such a fix is acceptable or not.
>
> I haven't tried reproducing it on older versions (with
> max_replication_slots instead of max_active_replication_origins), but after
> looking at the code for a bit, I'm growing skeptical that this is new to
> v18.
Right, it's actually not a new behavior to v18 as we can reproduce it
with max_replication_slots. I guess that the reason why we didn't
require standbys to set max_replication_slots no smaller than the
primary's value is that in principle the maximum number of replication
slots is not related to the recovery work. max_replication_slots juse
used to be re-used for the maximum number of active replication
origins for the sake of simplicity. Now that we have separated the
maximum number of active replication origins from
max_replication_slots, it seems to me that
max_active_replication_origins is now clearly related to the recovery.
> In any case, the PANIC provides a clear error message, which is
> roughly the same as what we'd say with the control file approach, right?
Yes. With the control file approach, we raise a FATAL (or pause the
recovery with a WARNING) instead of PANIC.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-17 04:23 Nathan Bossart <nathandbossart@gmail.com>
parent: Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Nathan Bossart @ 2025-09-17 04:23 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Nazneen Jafri <jafrinazneen@gmail.com>; pgsql-bugs@lists.postgresql.org
On Tue, Sep 16, 2025 at 09:05:33PM -0700, Masahiko Sawada wrote:
> On Tue, Sep 16, 2025 at 7:45 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>> I haven't tried reproducing it on older versions (with
>> max_replication_slots instead of max_active_replication_origins), but after
>> looking at the code for a bit, I'm growing skeptical that this is new to
>> v18.
>
> Right, it's actually not a new behavior to v18 as we can reproduce it
> with max_replication_slots. I guess that the reason why we didn't
> require standbys to set max_replication_slots no smaller than the
> primary's value is that in principle the maximum number of replication
> slots is not related to the recovery work. max_replication_slots juse
> used to be re-used for the maximum number of active replication
> origins for the sake of simplicity. Now that we have separated the
> maximum number of active replication origins from
> max_replication_slots, it seems to me that
> max_active_replication_origins is now clearly related to the recovery.
Given that it's existing behavior, I'm not seeing a strong reason to try to
do anything about this for v18. But I could be misunderstanding the nuance
here.
>> In any case, the PANIC provides a clear error message, which is
>> roughly the same as what we'd say with the control file approach, right?
>
> Yes. With the control file approach, we raise a FATAL (or pause the
> recovery with a WARNING) instead of PANIC.
I drafted up what that would look like. One very small nitpick is that it
messes up the alignment of the pg_controldata output. Otherwise, it seems
pretty straightforward.
--
nathan
Attachments:
[text/plain] v1-0001-max_active_replication_origins-fix.patch (0B, ../../aMo3tsESKXh6MCVp@nathan/2-v1-0001-max_active_replication_origins-fix.patch)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-17 04:32 Masahiko Sawada <sawada.mshk@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Masahiko Sawada @ 2025-09-17 04:32 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Nazneen Jafri <jafrinazneen@gmail.com>; pgsql-bugs@lists.postgresql.org
On Tue, Sep 16, 2025 at 9:23 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Tue, Sep 16, 2025 at 09:05:33PM -0700, Masahiko Sawada wrote:
> > On Tue, Sep 16, 2025 at 7:45 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> >> I haven't tried reproducing it on older versions (with
> >> max_replication_slots instead of max_active_replication_origins), but after
> >> looking at the code for a bit, I'm growing skeptical that this is new to
> >> v18.
> >
> > Right, it's actually not a new behavior to v18 as we can reproduce it
> > with max_replication_slots. I guess that the reason why we didn't
> > require standbys to set max_replication_slots no smaller than the
> > primary's value is that in principle the maximum number of replication
> > slots is not related to the recovery work. max_replication_slots juse
> > used to be re-used for the maximum number of active replication
> > origins for the sake of simplicity. Now that we have separated the
> > maximum number of active replication origins from
> > max_replication_slots, it seems to me that
> > max_active_replication_origins is now clearly related to the recovery.
>
> Given that it's existing behavior, I'm not seeing a strong reason to try to
> do anything about this for v18. But I could be misunderstanding the nuance
> here.
>
> >> In any case, the PANIC provides a clear error message, which is
> >> roughly the same as what we'd say with the control file approach, right?
> >
> > Yes. With the control file approach, we raise a FATAL (or pause the
> > recovery with a WARNING) instead of PANIC.
>
> I drafted up what that would look like. One very small nitpick is that it
> messes up the alignment of the pg_controldata output. Otherwise, it seems
> pretty straightforward.
Thank you for drafting the patch. It looks good to me.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-17 16:00 Masahiko Sawada <sawada.mshk@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Masahiko Sawada @ 2025-09-17 16:00 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Nazneen Jafri <jafrinazneen@gmail.com>; pgsql-bugs@lists.postgresql.org
On Tue, Sep 16, 2025 at 9:23 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Tue, Sep 16, 2025 at 09:05:33PM -0700, Masahiko Sawada wrote:
> > On Tue, Sep 16, 2025 at 7:45 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> >> I haven't tried reproducing it on older versions (with
> >> max_replication_slots instead of max_active_replication_origins), but after
> >> looking at the code for a bit, I'm growing skeptical that this is new to
> >> v18.
> >
> > Right, it's actually not a new behavior to v18 as we can reproduce it
> > with max_replication_slots. I guess that the reason why we didn't
> > require standbys to set max_replication_slots no smaller than the
> > primary's value is that in principle the maximum number of replication
> > slots is not related to the recovery work. max_replication_slots juse
> > used to be re-used for the maximum number of active replication
> > origins for the sake of simplicity. Now that we have separated the
> > maximum number of active replication origins from
> > max_replication_slots, it seems to me that
> > max_active_replication_origins is now clearly related to the recovery.
>
> Given that it's existing behavior, I'm not seeing a strong reason to try to
> do anything about this for v18. But I could be misunderstanding the nuance
> here.
>
After reviewing the issue again, I agree that we don't have a strong
reason to have such a change for v18. While it would probably be safer
to require standbys to set max_active_replication_origins no smaller
than the primary, it's not an item for v18. We can discuss it
separately for v19 or later.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
@ 2025-09-17 22:34 Euler Taveira <euler@eulerto.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Euler Taveira @ 2025-09-17 22:34 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Nazneen Jafri <jafrinazneen@gmail.com>; pgsql-bugs@lists.postgresql.org
On Wed, Sep 17, 2025, at 1:23 AM, Nathan Bossart wrote:
> I drafted up what that would look like. One very small nitpick is that it
> messes up the alignment of the pg_controldata output. Otherwise, it seems
> pretty straightforward.
>
As Masahiko said this behavior is not new in v18 so I wouldn't consider fix it
to v18.
I don't think your patch works for one detail: StartupReplicationOrigin() is
called earlier than CheckRequiredParameterValues(). The
StartupReplicationOrigin() uses max_active_replication_origins to emit the
PANIC message. I didn't check the implications to postpone the
StartupReplicationOrigin(). Maybe an ugly solution is to check the
max_active_replication_origins inside or even before calling
StartupReplicationOrigin().
I'm attaching a script that I used to simulate the issue. Change the GUC name
to simulate the issue in a pre v18.
--
Euler Taveira
EDB https://www.enterprisedb.com/
Attachments:
[application/x-shellscript] test-maro.sh (3.8K, ../../fb6cbb5a-43e0-4da0-9008-3e583b41a230@app.fastmail.com/2-test-maro.sh)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-09-17 22:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-16 22:52 Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary Nazneen Jafri <jafrinazneen@gmail.com>
2025-09-16 23:42 ` Masahiko Sawada <sawada.mshk@gmail.com>
2025-09-17 02:45 ` Nathan Bossart <nathandbossart@gmail.com>
2025-09-17 04:05 ` Masahiko Sawada <sawada.mshk@gmail.com>
2025-09-17 04:23 ` Nathan Bossart <nathandbossart@gmail.com>
2025-09-17 04:32 ` Masahiko Sawada <sawada.mshk@gmail.com>
2025-09-17 16:00 ` Masahiko Sawada <sawada.mshk@gmail.com>
2025-09-17 22:34 ` Euler Taveira <euler@eulerto.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox