Request a Demo
Home   >   mailing lists

Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary - Mailing list pgsql-bugs

From Nazneen Jafri
Subject Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
Date
Msg-id CA+m5N8uciiyzVOBb+ncadQutLoMOFaqN13Phq+r_neZm2vd5gQ@mail.gmail.com
Whole thread Raw
Responses Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary
List pgsql-bugs
Tree view
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


pgsql-bugs by date:

Previous
From: Tender Wang
Date:
Subject: Re: BUG #19046: Incorrect result when using json_array() with column reference in subquery combined with RIGHT JOIN
Next
From: Masahiko Sawada
Date:
Subject: Re: Read Replica termination occurs when its max_active_replication_origins setting is lower than the primary