Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary

From: Nazneen Jafri <jafrinazneen(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
Date: 2025-09-16 22:52:23
Message-ID: CA+m5N8uciiyzVOBb+ncadQutLoMOFaqN13Phq+r_neZm2vd5gQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)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(at)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Masahiko Sawada 2025-09-16 23:42:39 Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
Previous Message Tender Wang 2025-09-16 10:31:49 Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN