PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Database PR and DR replication/synchronization

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.

Tags:

2

Answers


  1. 0

    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/

    Login or Signup to reply.
  2. 0

    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:

    • Sequence ID mismatch: This occurs when sequences are not properly coordinated across nodes.
    • Constraint violations: These happen when the same data is inserted on both sites without proper conflict resolution mechanisms.

    Recommended Approaches

    1. Conflict-Free Replication Strategies:

      • Sequence Coordination: Use a different sequence range for each site to avoid conflicts. For example, one site could use odd-numbered sequences, and the other even-numbered sequences.
      • Conflict Resolution Policies: Implement conflict resolution strategies like last-write-wins, or application-level logic to handle duplicates.

    2. Alternative Solutions:

      • Logical Replication with Conflict Handling: PostgreSQL’s built-in logical replication might work if you set up custom conflict handling. However, it’s primarily designed for one-way replication, so custom scripting and triggers might be necessary.
      • Third-Party Tools: Consider using third-party tools that are specifically designed for multi-master replication:
        • Citus: For distributed PostgreSQL clusters.
        • PostgreSQL-XL: For horizontally scaling PostgreSQL.
      • Avoiding Full Active/Active: Depending on your use case, you might reconsider whether full Active/Active is necessary. Often, a robust Active/Passive setup with quick failover is simpler and more reliable.

    3. Architecture Considerations:

      • Application Layer Coordination: Sometimes, handling conflicts and coordination at the application layer is more efficient than trying to manage them purely within the database.
      • Partitioned Data: If possible, partition the data such that each site only writes to its partition, then replicate the partitions.

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search