vitabaks.postgresql_cluster

PostgreSQL High-Availability Cluster :elephant: :sparkling_heart:

GitHub stars

Production-Ready PostgreSQL High-Availability Cluster (Using Patroni). Automating with Ansible.

postgresql_cluster helps set up and manage highly available PostgreSQL clusters in production. This setup works well on dedicated servers, virtual machines, or in both on-premises and cloud environments.

For a more user-friendly and searchable version of this documentation, visit postgresql-cluster.org.

:trophy: Use the sponsoring program for personalized support or to help with the project.

Supported PostgreSQL Cluster Setups

postgresql_cluster

You can choose from three deployment schemes:

1. Only PostgreSQL High-Availability

This straightforward option does not include load balancing.

High Availability Components:

Patroni: A Python-based framework to set up a custom high-availability solution. It uses tools like ZooKeeper, etcd, Consul, or Kubernetes for distributed configuration and manages PostgreSQL instances automatically.
etcd: A reliable key-value store for critical data in distributed systems, which Patroni uses to store cluster status and PostgreSQL settings.
vip-manager (optional): Runs on all cluster nodes, providing a Virtual IP (VIP) for database access. It manages VIPs during a node failover.
PgBouncer (optional): A connection pooler for PostgreSQL.

2. PostgreSQL High-Availability with Load Balancing

This setup distributes read requests across replicas and supports cloud environments.

Cloud load balancers are automatically created for services like AWS, GCP, and Azure, simplifying access to the database (controlled by cloud_load_balancer variable).

For non-cloud environments, HAProxy can be used. Set with_haproxy_load_balancing: true in vars/main.yml to enable it.

:heavy_exclamation_mark: Note: Your application must send read requests to port 5001 and write requests to port 5000.

Port 5000: (read/write) master
Port 5001: (read only) all replicas

HAProxy Load Balancing Components:

HAProxy: A fast and reliable solution for high availability, load balancing, and proxying services.
confd: Manages HAProxy configuration using templates and data from etcd or Consul.
Keepalived (optional): Provides a Virtual IP for database access and monitors HAProxy status to manage failovers.

3. PostgreSQL High-Availability with Consul Service Discovery

Specify dcs_type: consul in vars/main.yml.

This option works for master-only access and load balancing for reading from replicas using DNS for client connections.

Example access points:

master.postgres-cluster.service.consul
replica.postgres-cluster.service.consul

It can also facilitate a distributed setup across different data centers.

Compatibility

Works with RedHat and Debian-based distributions (x86_64).

Supported Linux Versions:

Debian: 11, 12
Ubuntu: 22.04, 24.04
CentOS Stream: 9
Oracle Linux: 8, 9
Rocky Linux: 8, 9
AlmaLinux: 8, 9

PostgreSQL Versions:

All supported PostgreSQL versions

:white_check_mark: Tested and works fine: PostgreSQL 10, 11, 12, 13, 14, 15, 16

Distribution	Test result
Debian 11
Debian 12
Ubuntu 22.04
Ubuntu 24.04
CentOS Stream 9
Oracle Linux 8
Oracle Linux 9
Rocky Linux 8
Rocky Linux 9
AlmaLinux 8
AlmaLinux 9

Ansible Version

Minimum required Ansible version: 8.0.0 (ansible-core 2.15.0)

Requirements

Click here to expand...

This playbook needs root access or sudo privileges.

Ansible (What is Ansible?)

If you are using dcs_type: "consul", install consul role requirements on the control node:

ansible-galaxy install -r roles/consul/requirements.yml

Required Ports

Here’s a list of ports that must be open for the database cluster:

5432 (postgresql)
6432 (pgbouncer)
8008 (patroni API)
2379, 2380 (etcd)

For the "[Type A] PostgreSQL High-Availability with Load Balancing":

5000 (HAProxy - master)
5001 (HAProxy - read-only replicas)
5002 (HAProxy - synchronous replicas)
5003 (HAProxy - asynchronous replicas)
7000 (optional, HAProxy stats)

For the "[Type C] PostgreSQL High-Availability with Consul Service Discovery (DNS)":

8300 (Consul Server RPC)
8301 (Consul Serf LAN)
8302 (Consul Serf WAN)
8500 (Consul HTTP API)
8600 (Consul DNS server)

Recommendations

Click here to expand...

Operating System:

Update your servers before deploying. Ensure proper time sync (NTP) is set up. Specify ntp_enabled: 'true' and ntp_servers if NTP service installation and configuration are desired.

DCS (Distributed Consensus Store):

Fast drives and a stable network are crucial for good performance with etcd (or consul).

Do not store etcd (or consul) data on the same drives as other resource-intensive processes (like databases). Use separate disks for etcd and PostgreSQL data, preferably SSDs. Check hardware recommendations and tuning guides.

It's best to have the DCS cluster on dedicated servers, separated from database servers.

Cluster Member Placement:

For cross-data center setups, avoid placing all etcd members in the primary data center.

Preventing Data Loss during Failover:

By default, synchronous replication is disabled. To minimize data loss on automatic failover, set:

synchronous_mode: 'true'
synchronous_mode_strict: 'true'
synchronous_commit: 'on' (or 'remote_apply')

Getting Started

To launch the PostgreSQL Cluster Console, run:

docker run -d --name pg-console \
  --publish 80:80 \
  --publish 8080:8080 \
  --env PG_CONSOLE_API_URL=http://localhost:8080/api/v1 \
  --env PG_CONSOLE_AUTHORIZATION_TOKEN=secret_token \
  --volume console_postgres:/var/lib/postgresql \
  --volume /var/run/docker.sock:/var/run/docker.sock \
  --volume /tmp/ansible:/tmp/ansible \
  --restart=unless-stopped \
  vitabaks/postgresql_cluster_console:2.0.0

Make sure to run the console on the same network as your database servers and replace localhost with the server’s IP address in PG_CONSOLE_API_URL.

To Open the Console UI

Visit http://localhost/ and use secret_token for access.

If set up on a different server, replace 'localhost' with that server's address.

Click here to expand... for command line lovers.

Command Line Steps

Install Ansible on a control node (like a laptop).

sudo apt update && sudo apt install -y python3-pip sshpass git
pip3 install ansible

Clone this repository.

git clone https://github.com/vitabaks/postgresql_cluster.git

Navigate to the playbook directory.

cd postgresql_cluster/automation

Edit the inventory file.

Specify your server’s IP addresses and settings like `ansible_user`, `ansible_ssh_pass`, or `ansible_ssh_private_key_file`.

nano inventory

Edit the variable file vars/main.yml.

nano vars/main.yml

Essential variables:

proxy_env (if needed for package downloads)
cluster_vip (optional for client database access)
patroni_cluster_name
postgresql_version
postgresql_data_dir
with_haproxy_load_balancing: 'true' (Type A) or 'false' (Default for Type B)
dcs_type: "etcd" (default) or "consul" (Type C)

If using dcs_type: "consul", install Consul requirements on the control node:

ansible-galaxy install -r roles/consul/requirements.yml

Test connecting to hosts:

ansible all -m ping

Run the playbook:

ansible-playbook deploy_pgcluster.yml

To Deploy Cluster with TimescaleDB

To set up a PostgreSQL High-Availability Cluster with TimescaleDB, add the variable enable_timescale.

Example:

ansible-playbook deploy_pgcluster.yml -e "enable_timescale=true"

Starting Fresh

To start over, use the playbook remove_cluster.yml.

Available options:

remove_postgres: stop PostgreSQL and remove data.
remove_etcd: stop ETCD and remove data.
remove_consul: stop Consul and remove data.

Run the following command for specific removals:

ansible-playbook remove_cluster.yml -e "remove_postgres=true remove_etcd=true"

This will delete the specified components, allowing a fresh installation.

:warning: Caution: Be careful with this command in production environments.

Support Us

If our project helps you, consider giving it a star on GitHub! Your support encourages us and helps others find it.

Sponsoring our project helps us improve and maintain it. Sponsors receive personalized support, early feature access, and a voice in project direction.

Become a sponsor today!

Support us via GitHub Sponsors