Home

Awesome

PostgreSQL High-Availability Cluster :elephant: :sparkling_heart:

<img src="https://github.com/vitabaks/postgresql_cluster/workflows/Ansible-lint/badge.svg?branch=master"> <img src="https://github.com/vitabaks/postgresql_cluster/workflows/Yamllint/badge.svg?branch=master"> <img src="https://github.com/vitabaks/postgresql_cluster/workflows/Flake8/badge.svg?branch=master"> <img src="https://github.com/vitabaks/postgresql_cluster/workflows/Molecule/badge.svg?branch=master"> GitHub license GitHub stars

Production-ready PostgreSQL High-Availability Cluster (based on Patroni). Automating with Ansible.

postgresql_cluster automates the deployment and management of highly available PostgreSQL clusters in production environments. This solution is tailored for use on dedicated physical servers, virtual machines, and within both on-premises and cloud-based infrastructures.

You can find a version of this documentation that is searchable and also easier to navigate at postgresql-cluster.org

:trophy: Use the sponsoring program to get personalized support, or just to contribute to this project.

Supported setups of Postgres Cluster

postgresql_cluster postgresql_cluster

You have three schemes available for deployment:

1. PostgreSQL High-Availability only

This is simple scheme without load balancing.

Components of high availability:

2. PostgreSQL High-Availability with Load Balancing

This scheme enables load distribution for read operations and also allows for scaling out the cluster with read-only replicas.

When deploying to cloud providers such as AWS, GCP, Azure, DigitalOcean, and Hetzner Cloud, a cloud load balancer is automatically created by default to provide a single entry point to the database (controlled by the cloud_load_balancer variable).

For non-cloud environments, such as when deploying on Your Own Machines, the HAProxy load balancer is available for use. To enable it, set with_haproxy_load_balancing: true in the vars/main.yml file.

:heavy_exclamation_mark: Note: Your application must have support sending read requests to a custom port 5001, and write requests to port 5000.

if variable "synchronous_mode" is 'true' (vars/main.yml):
Components of HAProxy load balancing:

3. PostgreSQL High-Availability with Consul Service Discovery

To use this scheme, specify dcs_type: consul in variable file vars/main.yml

This scheme is suitable for master-only access and for load balancing (using DNS) for reading across replicas. Consul Service Discovery with DNS resolving is used as a client access point to the database.

Client access point (example):

Besides, it can be useful for a distributed cluster across different data centers. We can specify in advance which data center the database server is located in and then use this for applications running in the same data center.

Example: replica.postgres-cluster.service.dc1.consul, replica.postgres-cluster.service.dc2.consul

It requires the installation of a consul in client mode on each application server for service DNS resolution (or use forward DNS to the remote consul server instead of installing a local consul client).

Compatibility

RedHat and Debian based distros (x86_64)

Supported Linux Distributions:
PostgreSQL versions:

all supported PostgreSQL versions

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

Table of results of daily automated testing of cluster deployment:

DistributionTest result
Debian 11GitHub Workflow Status
Debian 12GitHub Workflow Status
Ubuntu 22.04GitHub Workflow Status
Ubuntu 24.04GitHub Workflow Status
CentOS Stream 9GitHub Workflow Status
Oracle Linux 8GitHub Workflow Status
Oracle Linux 9GitHub Workflow Status
Rocky Linux 8GitHub Workflow Status
Rocky Linux 9GitHub Workflow Status
AlmaLinux 8GitHub Workflow Status
AlmaLinux 9GitHub Workflow Status
Ansible version

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

Requirements

<details><summary>Click here to expand...</summary><p>

This playbook requires root privileges or sudo.

Ansible (What is Ansible?)

if dcs_type: "consul", please install consul role requirements on the control node:

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

Port requirements

List of required TCP ports that must be open for the database cluster:

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

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

</p></details>

Recommenations

<details><summary>Click here to expand...</summary><p>

Update your operating system on your target servers before deploying;

Make sure you have time synchronization is configured (NTP). Specify ntp_enabled:'true' and ntp_servers if you want to install and configure the ntp service.

Fast drives and a reliable network are the most important factors for the performance and stability of an etcd (or consul) cluster.

Avoid storing etcd (or consul) data on the same drive along with other processes (such as the database) that are intensively using the resources of the disk subsystem! Store the etcd and postgresql data on different disks (see etcd_data_dir, consul_data_path variables), use ssd drives if possible. See hardware recommendations and tuning guides.

It is recommended to deploy the DCS cluster on dedicated servers, separate from the database servers.

If you’d prefer a cross-data center setup, where the replicating databases are located in different data centers, etcd member placement becomes critical.

There are quite a lot of things to consider if you want to create a really robust etcd cluster, but there is one rule: do not placing all etcd members in your primary data center. See some examples.

Due to performance reasons, a synchronous replication is disabled by default.

To minimize the risk of losing data on autofailover, you can configure settings in the following way:

</p></details>

Getting Started

To run the PostgreSQL Cluster Console, execute the following command:

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

Note: It is recommended to run the console in the same network as your database servers to enable monitoring of the cluster status. In this case, replace localhost with your server's IP address in the PG_CONSOLE_API_URL variable.

Open the Console UI

Go to http://localhost/ and use secret_token for authorization.

Note: If you have set up the console on a different server, replace 'localhost' with the server's address. Use the value of your token if you have redefined it in the PG_CONSOLE_AUTHORIZATION_TOKEN variable.

<details><summary>Click here to expand... if you prefer the command line.</summary><p>

Command line

  1. Install Ansible on one control node (which could easily be a laptop)
sudo apt update && sudo apt install -y python3-pip sshpass git
pip3 install ansible
  1. Download or clone this repository
git clone https://github.com/vitabaks/postgresql_cluster.git
  1. Go to the playbook directory
cd postgresql_cluster/automation
  1. Edit the inventory file
Specify (non-public) IP addresses and connection settings (ansible_user, ansible_ssh_pass or ansible_ssh_private_key_file for your environment
nano inventory
  1. Edit the variable file vars/main.yml
nano vars/main.yml
Minimum set of variables:

See the vars/main.yml, system.yml and (Debian.yml or RedHat.yml) files for more details.

if dcs_type: "consul", please install consul role requirements on the control node:

ansible-galaxy install -r roles/consul/requirements.yml
  1. Try to connect to hosts
ansible all -m ping
  1. Run playbook:
ansible-playbook deploy_pgcluster.yml

Deploy Cluster with TimescaleDB

To deploy a PostgreSQL High-Availability Cluster with the TimescaleDB extension, you just need to add the enable_timescale variable.

Example:

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

asciicast

How to start from scratch

If you need to start from the very beginning, you can use the playbook remove_cluster.yml.

Available variables:

Run the following command to remove specific components:

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

This command will delete the specified components, allowing you to start a new installation from scratch.

:warning: Caution: be careful when running this command in a production environment.

</p></details>

Star us

If you find our project helpful, consider giving it a star on GitHub! Your support helps us grow and motivates us to keep improving. Starring the project is a simple yet effective way to show your appreciation and help others discover it.

<a href="https://star-history.com/#vitabaks/postgresql_cluster&Date"> <picture> <source media="(prefers-color-scheme: dark)" srcset="https://api.star-history.com/svg?repos=vitabaks/postgresql_cluster&type=Date&theme=dark" /> <source media="(prefers-color-scheme: light)" srcset="https://api.star-history.com/svg?repos=vitabaks/postgresql_cluster&type=Date" /> <img alt="Star History Chart" src="https://api.star-history.com/svg?repos=vitabaks/postgresql_cluster&type=Date" /> </picture> </a>

Sponsor this project

By sponsoring our project, you directly contribute to its continuous improvement and innovation. As a sponsor, you'll receive exclusive benefits, including personalized support, early access to new features, and the opportunity to influence the project's direction. Your sponsorship is invaluable to us and helps ensure the project's sustainability and progress.

Become a sponsor today and help us take this project to the next level!

Support our work through GitHub Sponsors

GitHub Sponsors

Support our work through Patreon

Support me on Patreon

Support our work through a crypto wallet:

USDT (TRC20): TSTSXZzqDCUDHDjZwCpuBkdukjuDZspwjj

License

Licensed under the MIT License. See the LICENSE file for details.

Author

Vitaliy Kukharik (PostgreSQL DBA)
vitabaks@gmail.com

Feedback, bug-reports, requests, ...

Are welcome!