High Availability PostgreSQL with Docker
Budget: €30 – €250 EUR
PostgreSQL High Availability with Docker and HAProxy (Reads and Writes)
Overview: Set up a highly available PostgreSQL system using Docker containers on two computers. The system should ensure:
The Primary Node runs on Computer 1 (handles both read and write operations).
The Secondary Node runs on Computer 2 (read-only replica).
Reads and writes should always be directed to the currently active node—whether that is the original Primary Node or the newly promoted Primary Node after failover.
If the Primary Node goes down, the Secondary Node should automatically promote itself to Primary.
HAProxy should handle the traffic routing so that the application only needs to connect to one host and port for both reads and writes, with the proxy determining which node is currently the Primary.
Automatic failover and failback must occur seamlessly, ensuring no data loss.
1. System Setup
Primary Node: Runs on Computer 1 and handles both read and write operations.
Secondary Node: Runs on Computer 2 and is initially read-only.
Docker Containers: Use Docker Compose to configure the services for the PostgreSQL nodes and HAProxy.
2. PostgreSQL Setup
Streaming Replication: Set up PostgreSQL Streaming Replication between the Primary Node and Secondary Node to ensure data is synchronized.
Failover Mechanism: Use Patroni (or similar) to automatically promote the Secondary Node to Primary if the Primary Node fails.
The failover process should be automatic and transparent, with minimal disruption to the database.
The Secondary Node will become the Primary Node in case of failure, and the new Primary should immediately start accepting writes.
Role Reversion: When the Primary Node comes back online, it should resync with the new Primary and revert to the Secondary role without causing any data loss.
3. HAProxy for Active Node Detection
HAProxy Setup:
HAProxy will be configured to check the role of each PostgreSQL node (Primary or Secondary).
HAProxy will always route read and write traffic to the active node. If the current Primary Node fails and the Secondary Node promotes itself to Primary, HAProxy will seamlessly reroute all traffic (reads and writes) to the newly promoted Primary without requiring changes from the application.
Health Check Configuration:
Set up HAProxy to perform health checks to determine if a node is still the Primary or if the Secondary Node has been promoted.
For this, HAProxy can use e.g. PostgreSQL's replication status or Patroni's API to confirm the role of the node before routing traffic.
Read/Write Traffic Routing:
HAProxy will route write queries to the active Primary Node—whether it's the original Primary Node or the promoted Secondary Node.
In case of failover, HAProxy will automatically detect the role change and route all future writes to the new Primary.
4. Failover & Failback
Promotion: When the Primary Node goes down, the Secondary Node will automatically promote itself to Primary using Patroni or another failover mechanism.
HAProxy will detect the role change and route all write traffic to the new Primary Node.
Failback: When the Primary Node comes back online, it will synchronize with the new Primary Node (which was the Secondary before the failure) and revert to the Secondary role.
HAProxy will reroute write traffic to the Primary Node and leave read traffic (if configured) to the Secondary Node.
5. Docker Configuration
Docker Compose: Use Docker Compose to define the services for both PostgreSQL nodes and the HAProxy container.
Configure HAProxy to listen on a single port (e.g., 5432) and forward traffic to the correct node based on the health check results.
Set up networking between the HAProxy container and the PostgreSQL containers so that HAProxy can perform health checks and traffic routing.
Overview: Set up a highly available PostgreSQL system using Docker containers on two computers. The system should ensure:
The Primary Node runs on Computer 1 (handles both read and write operations).
The Secondary Node runs on Computer 2 (read-only replica).
Reads and writes should always be directed to the currently active node—whether that is the original Primary Node or the newly promoted Primary Node after failover.
If the Primary Node goes down, the Secondary Node should automatically promote itself to Primary.
HAProxy should handle the traffic routing so that the application only needs to connect to one host and port for both reads and writes, with the proxy determining which node is currently the Primary.
Automatic failover and failback must occur seamlessly, ensuring no data loss.
1. System Setup
Primary Node: Runs on Computer 1 and handles both read and write operations.
Secondary Node: Runs on Computer 2 and is initially read-only.
Docker Containers: Use Docker Compose to configure the services for the PostgreSQL nodes and HAProxy.
2. PostgreSQL Setup
Streaming Replication: Set up PostgreSQL Streaming Replication between the Primary Node and Secondary Node to ensure data is synchronized.
Failover Mechanism: Use Patroni (or similar) to automatically promote the Secondary Node to Primary if the Primary Node fails.
The failover process should be automatic and transparent, with minimal disruption to the database.
The Secondary Node will become the Primary Node in case of failure, and the new Primary should immediately start accepting writes.
Role Reversion: When the Primary Node comes back online, it should resync with the new Primary and revert to the Secondary role without causing any data loss.
3. HAProxy for Active Node Detection
HAProxy Setup:
HAProxy will be configured to check the role of each PostgreSQL node (Primary or Secondary).
HAProxy will always route read and write traffic to the active node. If the current Primary Node fails and the Secondary Node promotes itself to Primary, HAProxy will seamlessly reroute all traffic (reads and writes) to the newly promoted Primary without requiring changes from the application.
Health Check Configuration:
Set up HAProxy to perform health checks to determine if a node is still the Primary or if the Secondary Node has been promoted.
For this, HAProxy can use e.g. PostgreSQL's replication status or Patroni's API to confirm the role of the node before routing traffic.
Read/Write Traffic Routing:
HAProxy will route write queries to the active Primary Node—whether it's the original Primary Node or the promoted Secondary Node.
In case of failover, HAProxy will automatically detect the role change and route all future writes to the new Primary.
4. Failover & Failback
Promotion: When the Primary Node goes down, the Secondary Node will automatically promote itself to Primary using Patroni or another failover mechanism.
HAProxy will detect the role change and route all write traffic to the new Primary Node.
Failback: When the Primary Node comes back online, it will synchronize with the new Primary Node (which was the Secondary before the failure) and revert to the Secondary role.
HAProxy will reroute write traffic to the Primary Node and leave read traffic (if configured) to the Secondary Node.
5. Docker Configuration
Docker Compose: Use Docker Compose to define the services for both PostgreSQL nodes and the HAProxy container.
Configure HAProxy to listen on a single port (e.g., 5432) and forward traffic to the correct node based on the health check results.
Set up networking between the HAProxy container and the PostgreSQL containers so that HAProxy can perform health checks and traffic routing.