Contents
- Introduction
- System Architecture
- Overview
- Technologies Used
- Installation Steps
- Preparation
- Common Installation Steps
- Server1 Specific Configuration
- Patroni Configuration
- HAProxy Configuration
- Security Features
- Authentication
- Network Security
- SSL/TLS Support
- Advantages
- High Availability
- Scalability
- Data Consistency
- Easy Management
- Useful Commands
- Recommendations and Improvements
- Security Improvements
- Performance Optimization
- Monitoring and Maintenance
- Conclusion
PostgreSQL High Availability Cluster: Setting Up a Three-Node HA Cluster
Introduction
Uninterrupted service and data security are critically important in modern database systems. PostgreSQL, as an open-source database management system, meets these needs by offering high availability (HA) solutions. This article covers the installation and configuration of a three-node PostgreSQL HA cluster.
System Architecture
Overview
This setup creates a PostgreSQL cluster running on three separate servers:
Server1 (192.168.99.50): Primary node (Master) and etcd coordinator
Server2 (192.168.99.51): Secondary node (Slave)
Server3 (192.168.99.52): Secondary node (Slave) and HAProxy load balancer
Technologies Used
PostgreSQL
An open-source, powerful relational database management system with advanced features. It stands out for its ACID compliance, complex queries and wide data type support.
Patroni
A Python-based tool developed to manage PostgreSQL clusters. It provides automatic failover, configuration management and cluster coordination.
etcd
A key-value store used to securely store configuration information in distributed systems. It provides coordination between the cluster members.
HAProxy
An open-source software used for load balancing and connection pool management. It provides high performance and reliability.
Installation Steps
Preparation
The installation script automatically detects the IP address of each server and applies the appropriate configuration. The system is optimized for Ubuntu/Debian-based distributions.
Common Installation Steps
System Update: System packages are updated on all servers.
PostgreSQL Installation: The main PostgreSQL package is installed, but the default service is disabled because it will be managed by Patroni.
Python Dependencies: Patroni and the required Python packages (psycopg2-binary, python-etcd) are installed.
Creating the Data Directory: The /data/patroni directory is created and the appropriate permissions are given to the PostgreSQL user.
Server1 Specific Configuration
On the primary server, etcd is additionally installed. etcd is critical for cluster coordination and is configured with the following parameters:
- ETCD_LISTEN_CLIENT_URLS: Addresses listened on for client connections
- ETCD_INITIAL_CLUSTER: Initial configuration of the cluster members
- ETCD_ADVERTISE_CLIENT_URLS: Client addresses advertised to the other nodes
Patroni Configuration
A dedicated Patroni configuration file is created for each server. These files contain the following:
Cluster Definitions: Scope, namespace and node names REST API Settings: Network configuration for the Patroni management interface etcd Connection Information: Connection settings for the coordination service PostgreSQL Parameters: Database engine settings and authentication rules
HAProxy Configuration
HAProxy is installed on the third server to provide the following features:
Load Balancing: Distribution of PostgreSQL connections across the nodes Health Checks: Continuous monitoring of node status Statistics Dashboard: Management interface accessible on port 7000 Connection Pooling: Optimization of the number of concurrent connections
Security Features
Authentication
The system uses a multi-user authentication mechanism:
- postgres: Superuser (password: postgres123)
- admin: Administrator user (password: admin123)
- replicator: Replication user (password: replicator123)
Network Security
Network access rules are defined through the pg_hba.conf settings. Connections are only allowed from the cluster IP range and from localhost.
SSL/TLS Support
The system is designed to be ready for SSL/TLS encryption to be added in the future.
Advantages
High Availability
If the primary node fails, service interruption is minimized through automatic failover. Patroni continuously monitors node states and transfers leadership when necessary.
Scalability
System performance can be increased by distributing the read load across the secondary nodes. HAProxy automatically routes read queries to the appropriate nodes.
Data Consistency
The risk of data loss is minimized with synchronous replication. Real-time data synchronization is achieved using PostgreSQL's streaming replication feature.
Easy Management
Cluster operations are simplified with the management tools provided by Patroni. Manual failover and adding or removing nodes can easily be performed with command-line tools.
Useful Commands
Viewing Cluster Status:
bashpatronictl -c /etc/patroni.yml list
Manual Failover:
bashpatronictl -c /etc/patroni.yml failover
HAProxy Statistics:
http://192.168.99.52:7000
PostgreSQL Connection:
bashpsql -h 192.168.99.52 -p 5000 -U postgres
Recommendations and Improvements
Security Improvements
- Password Policy: Default passwords must be replaced with strong passwords
- SSL Encryption: Adding SSL/TLS encryption for network traffic is recommended
- Firewall: Firewall rules should be defined for IP-based access control
Performance Optimization
- Memory Settings: PostgreSQL memory parameters should be optimized according to system resources
- Disk I/O: Using SSD disks and choosing a suitable file system improves performance
- Network Configuration: Gigabit Ethernet or faster network connections are recommended
Monitoring and Maintenance
- Log Management: A centralized log collection system should be set up
- Performance Monitoring: System monitoring with tools such as Grafana and Prometheus
- Backup Strategy: A regular automated backup plan should be created
Conclusion
This three-node PostgreSQL HA cluster provides a reliable and high-performance database infrastructure for critical applications. The integration of Patroni, etcd and HAProxy delivers automatic failover, load balancing and easy management.
The installation script automates the complex configuration process, enabling fast and consistent deployment. By applying the security and performance recommendations, you obtain a system that can be used safely in a production environment.
This solution is an ideal infrastructure option, especially for web applications, e-commerce sites and enterprise systems that must be available 24/7.
For installation, you can reach me through the contact section.