We have a requirement on production environment to run PR and DR as Active/Active. We have four nodes of redhat server. The request will be entertained from both sites means on both sites there should be a server who is master, could read&write the things in database and the data of both sites should be synced and replicated on all nodes. I used Pglogical/BDR for this porpose having master/slave on both sites but it is not helping, one way it work fine, not working on two way replication means PR to DR is working fine but DR to PR it is making constraints, sequence id mismatch and try to add the data where data is already existed.
How can we do it. Is there any better approach than this? IF Yes please provide.
2
Answers
You can try to use Bucardo, which an open-source replication system for PostgreSQL that allows you to replicate data between multiple databases in a bidirectional and asynchronous manner.
https://bucardo.org/Bucardo/
Here’s how you can structure your response for Stack Overflow:
Active/Active Setup for PR and DR with PostgreSQL:
You have a complex requirement involving a four-node Active/Active setup across two sites where both sites need to operate as masters, handling read/write operations, and ensuring data synchronization across all nodes.
Challenges with Pglogical/BDR
Pglogical and BDR are popular choices for multi-master replication, but as you’ve experienced, they can face issues with:
Recommended Approaches
Conflict-Free Replication Strategies:
Alternative Solutions:
Architecture Considerations:
Conclusion
While true multi-master replication is complex and challenging with PostgreSQL, careful planning and configuration can help mitigate the issues. If Pglogical/BDR isn’t meeting your needs, exploring other tools or reconsidering the architecture might be the best path forward.
This approach should guide you in refining your setup or exploring alternative solutions.