Sunday, November 22, 2026
TR
Linux

PostgreSQL High Availability Cluster: Setting Up a Three-Node HA Cluster

PostgreSQL High Availability Cluster: Setting Up a Three-Node HA Cluster
Contents
  1. Introduction
  2. System Architecture
  3. Overview
  4. Technologies Used
  5. Installation Steps
  6. Preparation
  7. Common Installation Steps
  8. Server1 Specific Configuration
  9. Patroni Configuration
  10. HAProxy Configuration
  11. Security Features
  12. Authentication
  13. Network Security
  14. SSL/TLS Support
  15. Advantages
  16. High Availability
  17. Scalability
  18. Data Consistency
  19. Easy Management
  20. Useful Commands
  21. Recommendations and Improvements
  22. Security Improvements
  23. Performance Optimization
  24. Monitoring and Maintenance
  25. 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

  1. Password Policy: Default passwords must be replaced with strong passwords
  2. SSL Encryption: Adding SSL/TLS encryption for network traffic is recommended
  3. Firewall: Firewall rules should be defined for IP-based access control

Performance Optimization

  1. Memory Settings: PostgreSQL memory parameters should be optimized according to system resources
  2. Disk I/O: Using SSD disks and choosing a suitable file system improves performance
  3. Network Configuration: Gigabit Ethernet or faster network connections are recommended

Monitoring and Maintenance

  1. Log Management: A centralized log collection system should be set up
  2. Performance Monitoring: System monitoring with tools such as Grafana and Prometheus
  3. 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.

Kerim Koç

Systems and infrastructure engineer. I write about Linux, open source, high availability and security.