ansible-patroni

Ansible playbook for deploying PostgreSQL Patroni cluster

Github: github.com/rom8726/ansible-patroni

Overview

This Ansible playbook automates the deployment of a PostgreSQL high-availability cluster using Patroni and etcd. The setup includes the following components for connection management and high availability:

• Keepalived creates a Virtual IP (VIP) for failover.
• HAProxy acts as a proxy layer for distributing requests.
• PgBouncer operates as a connection pooler.

The connection path:

Prerequisites

• Ubuntu/Debian-based system
• Ansible installed on the control node
• Network connectivity between all nodes

Components

• PostgreSQL 16
• Patroni
• etcd (for distributed configuration)
• PgBouncer (for connection pooling)
• Keepalived (for VIP)
• HAProxy (for load balancing)
• Python 3 and pip
• Node Exporter (system metrics)
• Postgres Exporter (PostgreSQL metrics)

Quick Start

1. Clone this repository.
2. Create config files with the command make init.
3. Update inventory.ini with your servers:

[promoters]
patroni1 ansible_host=192.168.64.2 node_id=1
patroni2 ansible_host=192.168.64.3 node_id=2
patroni3 ansible_host=192.168.64.4 node_id=3

4. Update ansible.cfg with your user (root by default).
5. Set variable cluster_vip_1 in group_vars (some VIP address for keepalived).
6. Set variable keepalived_vip_interface in keepalived default vars.
7. Run the playbook:

# Deploy Patroni cluster
make up

Default Configuration

Network Ports

Keepalived Configuration

• VIP: Used for automatic failover between HAProxy instances.
• Configured to determine the MASTER node based on its node_id in the cluster.

PgBouncer Configuration

• Connection pooling for PostgreSQL to reduce the overhead of establishing frequent connections.
• Default port: 6432.
• Authentication method: Userlist file.
• Configurations stored in /etc/pgbouncer/pgbouncer.ini.

PostgreSQL Settings

• Version: 16
• Encoding: UTF8
• Max Connections: 100
• Shared Buffers: 256MB
• WAL Level: replica
• Shared Preload Libraries: pg_stat_statements, auto_explain

Authentication (Default)

Monitoring Components

Node Exporter

• Version: 1.9.1
• Metrics Port: 9100
• Systemd Service: node_exporter.service
• User: node_exporter

Postgres Exporter

• Version: 0.17.1
• Metrics Port: 9187
• Systemd Service: postgres_exporter.service
• User: postgres
• Config Path: /etc/postgres_exporter/postgres_exporter.yaml

Service Management

# Patroni service
systemctl status patroni
systemctl start patroni
systemctl stop patroni

# etcd service
systemctl status etcd
systemctl start etcd
systemctl stop etcd

# HAProxy service
systemctl status haproxy
systemctl start haproxy
systemctl stop haproxy

# PgBouncer service
systemctl status pgbouncer
systemctl start pgbouncer
systemctl stop pgbouncer

# Keepalived service
systemctl status keepalived
systemctl start keepalived
systemctl stop keepalived

# Monitoring services
systemctl status node_exporter
systemctl status postgres_exporter

Monitoring

Available Metrics

• System metrics (via node_exporter): http://host:9100/metrics
• PostgreSQL metrics (via postgres_exporter): http://host:9187/metrics

PostgreSQL Exporter Metrics

Postgres Exporter provides the following metric groups:

PostgreSQL Settings

• Monitors PostgreSQL configuration parameters
• Collects values from all pg_settings entries
• Includes parameter name, value, unit, and a short description
• Exposed as GAUGE metrics

Replication Status

• Instance replication status monitoring:
  • Master/replica state detection
  • Master status monitoring (is_master metric)
  • Replica status monitoring (is_replica metric)
  • Replication lag monitoring in seconds (lag_seconds metric)
  • All metrics are exposed as a GAUGE type

Connection Statistics

• Detailed connection statistics:
  • Number of connections by state (active, idle, etc.)
  • Grouped by wait event types
  • Total connection count monitoring by different states
  • Exposed as GAUGE metrics with state and wait_event_type labels