Join Log in
Score:0
RedNet avatar Server

PostgreSQL Subscribers and Publishers (Multi-Master Scenario)

cg flag
RedNet

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

74
0 + 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.