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
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 |