Search code examples
databasepostgresqlreplicationdatabase-replication

PostgreSQL Subscribers and Publishers (Multi-Master Scenario)

(Yes, it needs to be PostgreSQL)

I'm currently trying to develop a solution that would be trying to focus on Multi-Master Databases

So here is how it's supposed to operate

I want so that whenever an Agent makes a change to a database, changes are stored in the agent DB, and then propagated throughout the other DBs. As follows in the image.

enter image description here

But I'm having some issues with this. I'm using PostgreSQL and the publisher/subscriber model.

What works:

  • HQ is a publisher and Agent 1 & 2 are subscribers

This works fine, direct changes in the HQ (If I updated the table manually) are propagated throughout the Agencies

  • Agent 1 is a publisher and HQ subscribes to it

This also works fine, changes in Agent 1 are sent to the HQ, and sends it to Agent 2

BREAKING POINT

  • Agent 2 is a publisher and HQ subscribes to it

As I do this and make a change in Agent 2, the HQ server starts to die due to concurrency issues, giving me the following error:

2022-10-26 16:57:01.311 BST [9208] LOG:  concurrent update, retrying

And so it keeps on retrying and never finishing. Bringing CPU to 100% and tables are never updated, some even change to previous values that were not in this operation, I'm assuming it's trying to find a common ground to apply the change.

Is there any way to fix it? Keeping in mind this would have 20+ Agents in a network with a single HQ database whose job is to propagate the changes. Am I doing this correctly?

Solution

  • This issue has been solved: symmetricds.org was the ideal software for the problem, it's open source and open to be used by enterprise, the developers seem to be on the website answering questions, it's very versatile and customizable. Would recommend it.