Azure postgreSQL single server publisher to subscriber replication new slot not getting active.

Question

Hi Experts, Azure PostgreSQL single server is the publisher, and the subscriber is an Azure VM running PostgreSQL. There are already 10 replication slots that are working properly; however, when we tried to create 11 slots, they did not become active. We tried increasing the max_replication_slots and max_wal_senders from 15 to 30, but the problem persisted. Please find the server parameter-related replication slot from both the publisher and subscriber. Pubslisher(Azure postgreSQL single server)

max_logical_replication_workers 4 max_replication_slots 30 max_wal_senders from 30 max_worker_processes 8 subscriber(Azure vm with postgreSQL )

max_logical_replication_workers 12 max_replication_slots 30 max_wal_senders from 30 max_worker_processes 30 Below are the error messages we got in subscriber side. 024-01-22 10:54:00.713 +08 [20508] WARNING: out of logical replication worker slots 2024-01-22 10:54:00.713 +08 [20508] HINT: You might need to increase max_logical_replication_workers. Also ERROR: duplicate key value violates unique constraint "insdet_pk" please guide me with more details, how to resolve the issue, Thanks Veera.

Answer 1

Hi Veera, the errors you are seeing suggest that you have some data on your target server that are in conflict with data on your source, meaning that you probably tried to create the logical replication before. Please do not increase any worker numbers as this will not help, you should stop the replication first, then truncate the data from the target database or remove the database entirely and recreate just schema (without data). Please also make sure that the user you are using for replication on the source server side is able to read all the data you are trying to replicate, if not grant appropriate permissions

Answer 2

Hi Veera •,

Welcome to Microsoft Q&A forum.

As I understand, Azure postgreSQL single server publisher to subscriber replication new slot not getting active.

From the error details mentioned in the question, it seems there could be a conflict situation as mentioned in the below link.

https://www.postgresql.org/docs/current/logical-replication-conflicts.html

Other related links:

https://stackoverflow.com/questions/4448340/postgresql-duplicate-key-violates-unique-constraint

Please check if this helped.

If not, we will further investigate.

Thanks

Answer 3

Hi ShaktiSingh, Thank you for your response and time, we also see this below error when configuring the new slot. could you please give some refence link for this error and how to fix it. 2024-01-22 10:54:00.713 +08 [20508] WARNING: out of logical replication worker slots 2024-01-22 10:54:00.713 +08 [20508] HINT: You might need to increase max_logical_replication_workers. 2024-01-22 10:54:01.109 +08 [21892] WARNING: out of logical replication worker slots 2024-01-22 10:54:01.109 +08 [21892] HINT: You might need to increase max_logical_replication_workers. Thanks & Regards, Veera.

Answer 4

Hi Veera •,

Please refer to the answer by Alicja Kucharczyk • which states that:

the errors you are seeing suggest that you have some data on your target server that are in conflict with data on your source, meaning that you probably tried to create the logical replication before. Please do not increase any worker numbers as this will not help, you should stop the replication first, then truncate the data from the target database or remove the database entirely and recreate just schema (without data). Please also make sure that the user you are using for replication on the source server side is able to read all the data you are trying to replicate, if not grant appropriate permissions

Let us know if you have further queries. Thanks