Deploy PostgreSQL HA (Patroni, etcd, HAproxy) on Ubuntu servers using Ansible
Architecture
Verify commands
#etcd
etcdctl --endpoints=http://10.10.10.111:2379,http://10.10.10.112:2379,http://10.10.10.113:2379 member list
etcdctl --endpoints=http://10.10.10.111:2379,http://10.10.10.112:2379,http://10.10.10.113:2379 endpoint health
etcdctl --endpoints=http://10.10.10.111:2379,http://10.10.10.112:2379,http://10.10.10.113:2379 endpoint status
#patroni
patronictl -c /etc/patroni/patroni.yml topology
ls /var/lib/postgresql/18/main -l
/usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main
#haproxy
watch 'echo "show stat" | nc -U /var/lib/haproxy/stats | cut -d "," -f 1,2,5-11,18,24,27,30,36,50,37,56,57,62 | column -s, -t'
#postgresql
export PGPASSWORD=postgres
#primary RW
psql -h 10.10.10.120 -p 5432 -U postgres -c "SELECT inet_server_addr(), inet_server_port(), pg_is_in_recovery();"
#replicas RO
for i in {1..5}; do
psql -h 10.10.10.120 -p 5433 -U postgres -c "SELECT inet_server_addr(), inet_server_port(), pg_is_in_recovery();"
done
etcdctl --endpoints=http://10.10.10.111:2379,http://10.10.10.112:2379,http://10.10.10.113:2379 member list
etcdctl --endpoints=http://10.10.10.111:2379,http://10.10.10.112:2379,http://10.10.10.113:2379 endpoint health
etcdctl --endpoints=http://10.10.10.111:2379,http://10.10.10.112:2379,http://10.10.10.113:2379 endpoint status
#patroni
patronictl -c /etc/patroni/patroni.yml topology
ls /var/lib/postgresql/18/main -l
/usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main
#haproxy
watch 'echo "show stat" | nc -U /var/lib/haproxy/stats | cut -d "," -f 1,2,5-11,18,24,27,30,36,50,37,56,57,62 | column -s, -t'
#postgresql
export PGPASSWORD=postgres
#primary RW
psql -h 10.10.10.120 -p 5432 -U postgres -c "SELECT inet_server_addr(), inet_server_port(), pg_is_in_recovery();"
#replicas RO
for i in {1..5}; do
psql -h 10.10.10.120 -p 5433 -U postgres -c "SELECT inet_server_addr(), inet_server_port(), pg_is_in_recovery();"
done
Ansible inventory file:
[patroni]
pg1 ansible_host=10.10.10.101 node_name=pg1 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
pg2 ansible_host=10.10.10.102 node_name=pg2 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
pg3 ansible_host=10.10.10.103 node_name=pg3 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
[etcd]
etcd1 ansible_host=10.10.10.111 node_name=etcd1 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
etcd2 ansible_host=10.10.10.112 node_name=etcd2 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
etcd3 ansible_host=10.10.10.113 node_name=etcd3 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
[haproxy]
#vip 10.10.120
haproxy1 ansible_host=10.10.10.121 node_name=haproxy1 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
haproxy2 ansible_host=10.10.10.122 node_name=haproxy2 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
pg1 ansible_host=10.10.10.101 node_name=pg1 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
pg2 ansible_host=10.10.10.102 node_name=pg2 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
pg3 ansible_host=10.10.10.103 node_name=pg3 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
[etcd]
etcd1 ansible_host=10.10.10.111 node_name=etcd1 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
etcd2 ansible_host=10.10.10.112 node_name=etcd2 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
etcd3 ansible_host=10.10.10.113 node_name=etcd3 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
[haproxy]
#vip 10.10.120
haproxy1 ansible_host=10.10.10.121 node_name=haproxy1 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
haproxy2 ansible_host=10.10.10.122 node_name=haproxy2 ansible_user=root ansible_ssh_private_key_file=~/.ssh/id_rsa
Ansible playbook:
---
- hosts: all
become: yes
tasks:
##########################################################################
# TOOLS INSTALL
##########################################################################
- name: Install netstat
apt:
name:
- net-tools
- tshark
- termshark
state: present
update_cache: yes
- hosts: etcd
become: yes
vars:
etcd_version: "v3.6.6"
tasks:
##########################################################################
# ETCD INSTALL
##########################################################################
- name: Download etcd release
get_url:
url: "https://github.com/etcd-io/etcd/releases/download/{{ etcd_version }}/etcd-{{ etcd_version }}-linux-amd64.tar.gz"
dest: /tmp/etcd.tar.gz
mode: "0644"
- name: Extract etcd tarball
unarchive:
src: /tmp/etcd.tar.gz
dest: /usr/local/
remote_src: yes
- name: Install etcd and etcdctl
copy:
src: "/usr/local/etcd-{{ etcd_version }}-linux-amd64/{{ item }}"
dest: "/usr/local/bin/{{ item }}"
mode: "0755"
remote_src: yes
loop:
- etcd
- etcdctl
- name: Ensure etcd directories
file:
path: "{{ item }}"
state: directory
loop:
- /var/lib/etcd
- /etc/etcd
- name: Create etcd systemd unit with v3 enabled
copy:
dest: /etc/systemd/system/etcd.service
content: |
[Unit]
Description=etcd key-value store
After=network-online.target
Wants=network-online.target
[Service]
Environment=ETCDCTL_API=3
ExecStart=/usr/local/bin/etcd \
--name {{ inventory_hostname }} \
--data-dir /var/lib/etcd \
--listen-client-urls http://{{ ansible_host }}:2379 \
--advertise-client-urls http://{{ ansible_host }}:2379 \
--listen-peer-urls http://{{ ansible_host }}:2380 \
--initial-advertise-peer-urls http://{{ ansible_host }}:2380 \
--initial-cluster-token patroni-cluster \
--initial-cluster "{% for h in groups['etcd'] %}{{ hostvars[h].inventory_hostname }}=http://{{ hostvars[h].ansible_host }}:2380{% if not loop.last %},{% endif %}{% endfor %}" \
--initial-cluster-state new \
#--enable-v2=true
Restart=always
RestartSec=5
[Install]
WantedBy=multi-user.target
##########################################################################
# ENSURE ETCD AND PATRONI ARE RUNNING
##########################################################################
- name: Ensure etcd is running and enabled
systemd:
name: etcd
state: started
enabled: yes
daemon_reload: yes
- hosts: patroni
become: yes
vars:
patroni_scope: "patroni_cluster"
patroni_pg_version: 18
patroni_data_dir: "/var/lib/postgresql/{{ patroni_pg_version }}/main"
bootstrap_node: "{{ groups['patroni'][0] }}" # first node bootstraps PostgreSQL
tasks:
##########################################################################
# ADD POSTGRESQL REPO
##########################################################################
- name: Add PostgreSQL APT repository key
ansible.builtin.apt_key:
url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
state: present
- name: Add PostgreSQL APT repository
ansible.builtin.apt_repository:
repo: "deb http://apt.postgresql.org/pub/repos/apt {{ ansible_lsb.codename }}-pgdg main"
state: present
filename: "pgdg"
##########################################################################
# POSTGRESQL INSTALL
##########################################################################
- name: Install PostgreSQL
apt:
name:
- postgresql-{{ patroni_pg_version }}
- postgresql-client-{{ patroni_pg_version }}
- python3-psycopg2
state: present
update_cache: yes
- name: Stop default PostgreSQL service
service:
name: postgresql
state: stopped
enabled: no
##########################################################################
# DATA DIRECTORY MANAGEMENT
##########################################################################
- name: Check if Patroni cluster is already running
command: patronictl -c /etc/patroni/patroni.yml list
register: patroni_status
ignore_errors: yes
changed_when: false
- name: Set flag if cluster is bootstrapped
set_fact:
cluster_bootstrapped: "{{ patroni_status.rc == 0 and 'Leader' in patroni_status.stdout }}"
- name: Ensure data directory exists (only if cluster not bootstrapped)
file:
path: "{{ patroni_data_dir }}"
state: directory
owner: postgres
group: postgres
mode: "0700"
when: not cluster_bootstrapped
- name: Remove existing data directory (only if cluster not bootstrapped)
file:
path: "{{ patroni_data_dir }}"
state: absent
when: not cluster_bootstrapped
- name: Recreate empty data directory (only if cluster not bootstrapped)
file:
path: "{{ patroni_data_dir }}"
state: directory
owner: postgres
group: postgres
mode: "0700"
when: not cluster_bootstrapped
##########################################################################
# PATRONI INSTALL
##########################################################################
- name: Install Patroni
apt:
name: patroni
state: present
- name: Create Patroni config directory
file:
path: /etc/patroni
state: directory
- name: Write Patroni config
copy:
dest: /etc/patroni/patroni.yml
mode: 0644
content: |
scope: {{ patroni_scope }}
name: {{ inventory_hostname }}
etcd3:
hosts:
{% for h in groups['etcd'] %}
- {{ hostvars[h].ansible_host }}:2379
{% endfor %}
restapi:
listen: {{ ansible_host }}:8008
connect_address: {{ ansible_host }}:8008
postgresql:
listen: {{ ansible_host }}:5432
connect_address: {{ ansible_host }}:5432
data_dir: {{ patroni_data_dir }}
bin_dir: /usr/lib/postgresql/{{ patroni_pg_version }}/bin
authentication:
superuser:
username: postgres
password: postgres
replication:
username: replicator
password: replicator
parameters:
wal_keep_size: 128MB
max_wal_senders: 10
hot_standby: "on"
shared_buffers: "512MB"
users:
replicator:
password: replicator
options: [replication]
{% if inventory_hostname == bootstrap_node %}
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: replica
hot_standby: "on"
initdb:
- encoding: UTF8
- locale: en_US.UTF-8
- auth-host: md5
# pg_hba entries including replication and superuser
pg_hba:
- host all all 0.0.0.0/0 md5
- host replication replicator 0.0.0.0/0 md5
- host all postgres 0.0.0.0/0 md5
{% else %}
# no bootstrap section for followers
{% endif %}
##########################################################################
# PATRONI SYSTEMD SERVICE
##########################################################################
- name: Create Patroni systemd service
copy:
dest: /etc/systemd/system/patroni.service
content: |
[Unit]
Description=Patroni HA PostgreSQL
After=network.target
[Service]
User=postgres
ExecStart=/usr/bin/patroni /etc/patroni/patroni.yml
Restart=always
[Install]
WantedBy=multi-user.target
##########################################################################
# ENSURE ETCD AND PATRONI ARE RUNNING
##########################################################################
#- name: Wait for...
# pause:
# prompt: "Check logs or Patroni status, then press Enter to continue"
- name: Ensure Patroni is running and enabled
systemd:
name: patroni
state: started
enabled: yes
daemon_reload: yes
- hosts: haproxy
become: yes
vars:
patroni_nodes: "{{ groups['patroni'] }}"
haproxy_port_rw: 5432
haproxy_port_ro: 5433
vip_address: 10.10.10.120
vip_interface: enp1s0
keepalived_state: "{{ 'MASTER' if inventory_hostname == groups['haproxy'][0] else 'BACKUP' }}"
keepalived_priority: "{{ 150 if inventory_hostname == groups['haproxy'][0] else 100 }}"
tasks:
##########################################################################
# INSTALL PACKAGES
##########################################################################
- name: Install HAProxy and Keepalived
apt:
name:
- haproxy
- keepalived
state: present
update_cache: yes
##########################################################################
# HAPROXY CONFIG
##########################################################################
- name: Configure HAProxy for Patroni
copy:
dest: /etc/haproxy/haproxy.cfg
mode: 0644
content: |
global
log /dev/log local0
maxconn 2048
stats socket /var/lib/haproxy/stats mode 660 level admin
stats timeout 30s
defaults
log global
timeout connect 5s
timeout client 30s
timeout server 30s
frontend pg_rw
bind *:{{ haproxy_port_rw }}
default_backend pg_rw_backend
backend pg_rw_backend
option httpchk GET /master
http-check expect status 200
{% for h in patroni_nodes %}
server {{ h }} {{ hostvars[h].ansible_host }}:5432 check port 8008
{% endfor %}
frontend pg_ro
bind *:{{ haproxy_port_ro }}
default_backend pg_ro_backend
backend pg_ro_backend
option httpchk GET /replica
http-check expect status 200
{% for h in patroni_nodes %}
server {{ h }} {{ hostvars[h].ansible_host }}:5432 check port 8008
{% endfor %}
- name: Enable and restart HAProxy
systemd:
name: haproxy
state: restarted
enabled: yes
##########################################################################
# KEEPALIVED CONFIG
##########################################################################
- name: Configure Keepalived
copy:
dest: /etc/keepalived/keepalived.conf
mode: 0644
content: |
vrrp_instance VI_1 {
state {{ keepalived_state }}
interface {{ vip_interface }}
virtual_router_id 51
priority {{ keepalived_priority }}
advert_int 1
authentication {
auth_type PASS
auth_pass patroni_vip
}
virtual_ipaddress {
{{ vip_address }}
}
}
- name: Enable and restart Keepalived
systemd:
name: keepalived
state: restarted
enabled: yes
- hosts: all
become: yes
tasks:
##########################################################################
# TOOLS INSTALL
##########################################################################
- name: Install netstat
apt:
name:
- net-tools
- tshark
- termshark
state: present
update_cache: yes
- hosts: etcd
become: yes
vars:
etcd_version: "v3.6.6"
tasks:
##########################################################################
# ETCD INSTALL
##########################################################################
- name: Download etcd release
get_url:
url: "https://github.com/etcd-io/etcd/releases/download/{{ etcd_version }}/etcd-{{ etcd_version }}-linux-amd64.tar.gz"
dest: /tmp/etcd.tar.gz
mode: "0644"
- name: Extract etcd tarball
unarchive:
src: /tmp/etcd.tar.gz
dest: /usr/local/
remote_src: yes
- name: Install etcd and etcdctl
copy:
src: "/usr/local/etcd-{{ etcd_version }}-linux-amd64/{{ item }}"
dest: "/usr/local/bin/{{ item }}"
mode: "0755"
remote_src: yes
loop:
- etcd
- etcdctl
- name: Ensure etcd directories
file:
path: "{{ item }}"
state: directory
loop:
- /var/lib/etcd
- /etc/etcd
- name: Create etcd systemd unit with v3 enabled
copy:
dest: /etc/systemd/system/etcd.service
content: |
[Unit]
Description=etcd key-value store
After=network-online.target
Wants=network-online.target
[Service]
Environment=ETCDCTL_API=3
ExecStart=/usr/local/bin/etcd \
--name {{ inventory_hostname }} \
--data-dir /var/lib/etcd \
--listen-client-urls http://{{ ansible_host }}:2379 \
--advertise-client-urls http://{{ ansible_host }}:2379 \
--listen-peer-urls http://{{ ansible_host }}:2380 \
--initial-advertise-peer-urls http://{{ ansible_host }}:2380 \
--initial-cluster-token patroni-cluster \
--initial-cluster "{% for h in groups['etcd'] %}{{ hostvars[h].inventory_hostname }}=http://{{ hostvars[h].ansible_host }}:2380{% if not loop.last %},{% endif %}{% endfor %}" \
--initial-cluster-state new \
#--enable-v2=true
Restart=always
RestartSec=5
[Install]
WantedBy=multi-user.target
##########################################################################
# ENSURE ETCD AND PATRONI ARE RUNNING
##########################################################################
- name: Ensure etcd is running and enabled
systemd:
name: etcd
state: started
enabled: yes
daemon_reload: yes
- hosts: patroni
become: yes
vars:
patroni_scope: "patroni_cluster"
patroni_pg_version: 18
patroni_data_dir: "/var/lib/postgresql/{{ patroni_pg_version }}/main"
bootstrap_node: "{{ groups['patroni'][0] }}" # first node bootstraps PostgreSQL
tasks:
##########################################################################
# ADD POSTGRESQL REPO
##########################################################################
- name: Add PostgreSQL APT repository key
ansible.builtin.apt_key:
url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
state: present
- name: Add PostgreSQL APT repository
ansible.builtin.apt_repository:
repo: "deb http://apt.postgresql.org/pub/repos/apt {{ ansible_lsb.codename }}-pgdg main"
state: present
filename: "pgdg"
##########################################################################
# POSTGRESQL INSTALL
##########################################################################
- name: Install PostgreSQL
apt:
name:
- postgresql-{{ patroni_pg_version }}
- postgresql-client-{{ patroni_pg_version }}
- python3-psycopg2
state: present
update_cache: yes
- name: Stop default PostgreSQL service
service:
name: postgresql
state: stopped
enabled: no
##########################################################################
# DATA DIRECTORY MANAGEMENT
##########################################################################
- name: Check if Patroni cluster is already running
command: patronictl -c /etc/patroni/patroni.yml list
register: patroni_status
ignore_errors: yes
changed_when: false
- name: Set flag if cluster is bootstrapped
set_fact:
cluster_bootstrapped: "{{ patroni_status.rc == 0 and 'Leader' in patroni_status.stdout }}"
- name: Ensure data directory exists (only if cluster not bootstrapped)
file:
path: "{{ patroni_data_dir }}"
state: directory
owner: postgres
group: postgres
mode: "0700"
when: not cluster_bootstrapped
- name: Remove existing data directory (only if cluster not bootstrapped)
file:
path: "{{ patroni_data_dir }}"
state: absent
when: not cluster_bootstrapped
- name: Recreate empty data directory (only if cluster not bootstrapped)
file:
path: "{{ patroni_data_dir }}"
state: directory
owner: postgres
group: postgres
mode: "0700"
when: not cluster_bootstrapped
##########################################################################
# PATRONI INSTALL
##########################################################################
- name: Install Patroni
apt:
name: patroni
state: present
- name: Create Patroni config directory
file:
path: /etc/patroni
state: directory
- name: Write Patroni config
copy:
dest: /etc/patroni/patroni.yml
mode: 0644
content: |
scope: {{ patroni_scope }}
name: {{ inventory_hostname }}
etcd3:
hosts:
{% for h in groups['etcd'] %}
- {{ hostvars[h].ansible_host }}:2379
{% endfor %}
restapi:
listen: {{ ansible_host }}:8008
connect_address: {{ ansible_host }}:8008
postgresql:
listen: {{ ansible_host }}:5432
connect_address: {{ ansible_host }}:5432
data_dir: {{ patroni_data_dir }}
bin_dir: /usr/lib/postgresql/{{ patroni_pg_version }}/bin
authentication:
superuser:
username: postgres
password: postgres
replication:
username: replicator
password: replicator
parameters:
wal_keep_size: 128MB
max_wal_senders: 10
hot_standby: "on"
shared_buffers: "512MB"
users:
replicator:
password: replicator
options: [replication]
{% if inventory_hostname == bootstrap_node %}
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: replica
hot_standby: "on"
initdb:
- encoding: UTF8
- locale: en_US.UTF-8
- auth-host: md5
# pg_hba entries including replication and superuser
pg_hba:
- host all all 0.0.0.0/0 md5
- host replication replicator 0.0.0.0/0 md5
- host all postgres 0.0.0.0/0 md5
{% else %}
# no bootstrap section for followers
{% endif %}
##########################################################################
# PATRONI SYSTEMD SERVICE
##########################################################################
- name: Create Patroni systemd service
copy:
dest: /etc/systemd/system/patroni.service
content: |
[Unit]
Description=Patroni HA PostgreSQL
After=network.target
[Service]
User=postgres
ExecStart=/usr/bin/patroni /etc/patroni/patroni.yml
Restart=always
[Install]
WantedBy=multi-user.target
##########################################################################
# ENSURE ETCD AND PATRONI ARE RUNNING
##########################################################################
#- name: Wait for...
# pause:
# prompt: "Check logs or Patroni status, then press Enter to continue"
- name: Ensure Patroni is running and enabled
systemd:
name: patroni
state: started
enabled: yes
daemon_reload: yes
- hosts: haproxy
become: yes
vars:
patroni_nodes: "{{ groups['patroni'] }}"
haproxy_port_rw: 5432
haproxy_port_ro: 5433
vip_address: 10.10.10.120
vip_interface: enp1s0
keepalived_state: "{{ 'MASTER' if inventory_hostname == groups['haproxy'][0] else 'BACKUP' }}"
keepalived_priority: "{{ 150 if inventory_hostname == groups['haproxy'][0] else 100 }}"
tasks:
##########################################################################
# INSTALL PACKAGES
##########################################################################
- name: Install HAProxy and Keepalived
apt:
name:
- haproxy
- keepalived
state: present
update_cache: yes
##########################################################################
# HAPROXY CONFIG
##########################################################################
- name: Configure HAProxy for Patroni
copy:
dest: /etc/haproxy/haproxy.cfg
mode: 0644
content: |
global
log /dev/log local0
maxconn 2048
stats socket /var/lib/haproxy/stats mode 660 level admin
stats timeout 30s
defaults
log global
timeout connect 5s
timeout client 30s
timeout server 30s
frontend pg_rw
bind *:{{ haproxy_port_rw }}
default_backend pg_rw_backend
backend pg_rw_backend
option httpchk GET /master
http-check expect status 200
{% for h in patroni_nodes %}
server {{ h }} {{ hostvars[h].ansible_host }}:5432 check port 8008
{% endfor %}
frontend pg_ro
bind *:{{ haproxy_port_ro }}
default_backend pg_ro_backend
backend pg_ro_backend
option httpchk GET /replica
http-check expect status 200
{% for h in patroni_nodes %}
server {{ h }} {{ hostvars[h].ansible_host }}:5432 check port 8008
{% endfor %}
- name: Enable and restart HAProxy
systemd:
name: haproxy
state: restarted
enabled: yes
##########################################################################
# KEEPALIVED CONFIG
##########################################################################
- name: Configure Keepalived
copy:
dest: /etc/keepalived/keepalived.conf
mode: 0644
content: |
vrrp_instance VI_1 {
state {{ keepalived_state }}
interface {{ vip_interface }}
virtual_router_id 51
priority {{ keepalived_priority }}
advert_int 1
authentication {
auth_type PASS
auth_pass patroni_vip
}
virtual_ipaddress {
{{ vip_address }}
}
}
- name: Enable and restart Keepalived
systemd:
name: keepalived
state: restarted
enabled: yes