Creating a Python System to Turn All PostgreSQL Servers into Masters with Auto-Recovery and Sync – N

Posted by tp-li@reddit | Python | View on Reddit | 6 comments

Hello Python community!I’m currently working on developing a distributed PostgreSQL system using Python, where all servers act as masters. Additionally, I’m adopting a clear separation between servers and clients to create a flexible and efficient architecture.The primary goals of this project are as follows:

Master-Master architecture
All servers operate equally, eliminating single points of failure (SPOF).
Server-Client separation
Clients can seamlessly access the system while the internal operations are optimized for distributed workloads.
Automatic recovery
In case of server failures, other nodes automatically handle recovery to maintain uninterrupted service.
Automatic data synchronization
Efficiently synchronizing data across nodes while ensuring consistency.
Leveraging Python and PostgreSQL
Combining Python's flexibility with PostgreSQL's robust features.

Current Tools

For this project, I’m focusing on the following two key modules:

psycopg3: To enable efficient communication with PostgreSQL, especially with its asynchronous capabilities.
aioquic: For leveraging the QUIC protocol to achieve fast and reliable data synchronization, particularly for server-client communications in a distributed setup.

Challenges and Feedback Needed

Here are some specific points where I’d love to get your insights:

Server-Client Design Approach
What’s the best way to dynamically determine which server the client should connect to in a distributed master-master setup?
Any recommendations for handling automatic failover, where clients detect server failures and switch to another server seamlessly?
Using psycopg3 and aioquic
Any tips on best practices for asynchronous operations with psycopg3 or optimizing aioquic for this use case? Are there other libraries I should consider?
Distributed Database Challenges
In a master-master architecture, what are the best approaches to address consistency and conflict resolution? Are there any recommended algorithms or design patterns?
System Name Suggestions
I’m considering names like “PostMasterSync” or “PolyMaster,” but I’d love to hear any creative suggestions!

The Potential of This Project

This project aims to explore new possibilities in distributed databases by combining high availability and flexibility. With the power of Python and PostgreSQL, I’m excited to see how far this idea can go.I truly value the community’s knowledge and insights, and I’m looking forward to your feedback and ideas!Thank you for your time and support

[-]

Oct8-Danger@reddit

You do realize this is one of the hardest things to do write in CS? Have you considered ACID compliance and how it would change in a distributed system?

Have you looked at Cassandra and Scylla architecture? They are one of the few dbs to actually achieve high write and read with eventual consistency

tp-li@reddit (OP)

Thank you for your comment!I understand that consistency and ACID compliance in distributed systems are some of the most challenging topics in CS. I’ve looked into these concepts to some extent, but I’m still exploring how they apply to my specific project.I haven’t deeply studied Cassandra or Scylla yet, but I’ll definitely take a closer look at their architectures to better understand how they handle masterless designs and eventual consistency. If you have any particular resources or insights about these databases, I’d greatly appreciate your recommendations.Thanks again for pointing me in the right direction!

call_me_cookie@reddit

I would recommend reading up on Patroni (https://patroni.readthedocs.io/en/latest/) their docs have lots of useful information about HA postgres.

Thank you! I’ll take a closer look at the Patroni documentation.

fortunatefaileur@reddit

Are you sure you’ve thought this through, and read about how distributed databases work?

I’ve done some initial research on distributed databases and understand that master-master architectures come with significant challenges, such as consistency and conflict resolution. That said, I’m still in the process of fully grasping these concepts and exploring potential solutions through this project.If you have any recommended resources or insights, I’d greatly appreciate them. Thank you again for engaging with my post!