PostgreSQL is a free, open-source and most advanced database management system around the world. It is a powerful and object-relational database that extends the SQL language with many features that scale the most complicated data workloads.
Patroni is a cluster manager used to automate the deployment and maintenance of PostgreSQL HA clusters. It is written in Python and supports "Distributed Configuration Store" including, Zookeeper, etcd, Consul and Kubernetes. It supports streaming and synchronous replication and provides REST APIs for the dynamic configuration of the PostgreSQL cluster.
In this tutorial, we will explain how to set up PostgreSQL Cluster using Patroni on Ubuntu 20.04
# Prerequisites
- Two servers for Patroni running Ubuntu 20.04.
- One server for ETCD running Ubuntu 20.04.
- One server for HAProxy running Ubuntu 20.04.
- A root password is set on each server.
For the purpose of this tutorial, we will use the following setup:
Servers | Applications | IP Address |
---|---|---|
server1 | Patroni, PostgreSQL | 172.16.0.101 |
server2 | Patroni, PostgreSQL | 172.16.0.102 |
server3 | ETCD | 172.16.0.103 |
server4 | HAProxy | 172.16.0.104 |
# Install PostgreSQL
First, you will need to install the PostgreSQL server on both server1 and server2. You can install it on both servers with the following command:
apt-get install postgresql postgresql-contrib -y
Once the installation has been finished, stop the PostgreSQL service on both servers:
systemctl stop postgresql
Next, create a symlink of /usr/lib/postgresql/12/bin/ to /usr/sbin as it contains some tools used for Patroni.
ln -s /usr/lib/postgresql/12/bin/* /usr/sbin/
# Install Patroni
Before installing Patroni, you will need to install Python and other dependencies on both server1 and server2.
apt-get install python3-pip python3-dev libpq-dev -y
Once all the dependencies are installed, upgrade PIP to the latest version with the following command:
pip3 install --upgrade pip
Next, install the Patroni with the following command:
pip install patroni
Next, install other required dependencies with the following command:
pip install python-etcd
pip install psycopg2
Note: Run all of the above commands on both server1 and server2.
# Install Etcd and HAProxy
Next, you will need to install Etcd to store the data across a cluster of machines. You can install the Etcd on the server3 with the following command:
apt-get install etcd -y
Next, you will need to install the HAProxy to forward the connection from Master to Slave or Slave to Master. You can install it on server4 with the following command:
apt-get install haproxy -y
# Configure Etcd
Next, you will need to configure Etcd on server3. You can configure it by editing the file /etc/default/etcd:
nano /etc/default/etcd
Add the following lines:
ETCD_LISTEN_PEER_URLS="http://172.16.0.103:2380,http://127.0.0.1:7001"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379, http://172.16.0.103:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.16.0.103:2380"
ETCD_INITIAL_CLUSTER="etcd0=http://172.16.0.103:2380,"
ETCD_ADVERTISE_CLIENT_URLS="http://172.16.0.103:2379"
ETCD_INITIAL_CLUSTER_TOKEN="cluster1"
ETCD_INITIAL_CLUSTER_STATE="new"
Save and close the file then restart the Etcd service to apply the changes:
systemctl restart etcd
You can also verify the status of the service using the following command:
systemctl status etcd
Output:
β etcd.service - etcd - highly-available key value store
Loaded: loaded (/lib/systemd/system/etcd.service; enabled; vendor preset: >
Active: active (running) since Fri 2021-02-19 14:24:05 UTC; 4s ago
Docs: https://github.com/coreos/etcd
man:etcd
Main PID: 1461 (etcd)
Tasks: 9 (limit: 1145)
Memory: 20.0M
CGroup: /system.slice/etcd.service
ββ1461 /usr/bin/etcd
Feb 19 14:24:05 etcd etcd[1461]: 8e9e05c52164694d became candidate at term 3
Feb 19 14:24:05 etcd etcd[1461]: 8e9e05c52164694d received MsgVoteResp from 8e9>
Feb 19 14:24:05 etcd etcd[1461]: 8e9e05c52164694d became leader at term 3
Feb 19 14:24:05 etcd etcd[1461]: raft.node: 8e9e05c52164694d elected leader 8e9>
Feb 19 14:24:05 etcd etcd[1461]: published {Name:etcd ClientURLs:[http://45.58.>
Feb 19 14:24:05 etcd systemd[1]: Started etcd - highly-available key value stor>
Feb 19 14:24:05 etcd etcd[1461]: ready to serve client requests
Feb 19 14:24:05 etcd etcd[1461]: serving insecure client requests on 127.0.0.1:>
Feb 19 14:24:05 etcd etcd[1461]: ready to serve client requests
Feb 19 14:24:05 etcd etcd[1461]: serving insecure client requests on 45.58.43.5>
# Configure Patroni
Patroni uses a YML file to store its configuration. You will need to create a patroni.yml file on both server1 and server2:
You can create it on both servers with the following command:
nano /etc/patroni.yml
Add the following lines:
Note: replace listen, connect_address value with each server IP:
scope: postgres
namespace: /db/
name: postgresql0
restapi:
listen: 172.16.0.101:8008
connect_address: 172.16.0.101:8008
etcd:
host: 172.16.0.103:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 172.16.0.101/0 md5
- host replication replicator 172.16.0.102/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: 172.16.0.101:5432
connect_address: 172.16.0.101:5432
data_dir: /data/patroni
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: password
superuser:
username: postgres
password: password
parameters:
unix_socket_directories: '.'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
Save and close the file then create a data directory for Patroni and set proper ownership and permissions on both servers:
mkdir -p /data/patroni
chown postgres:postgres /data/patroni
chmod 700 /data/patroni
# Create a Systemd Service File for Patroni
Next, you will need to create a systemd service file to manage the Patroni service on both servers. You can create it with the following command:
nano /etc/systemd/system/patroni.service
Add the following lines:
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.targ
Save and close the file then reload the systemd daemon with the following command:
systemctl daemon-reload
Next, start the Patroni and PostgreSQL service on both servers:
systemctl start patroni
systemctl start postgresql
You can also check the status of Patroni with the following command:
systemctl status patroni
Output:
β patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: enabled)
Active: active (running) since Fri 2021-02-19 14:31:19 UTC; 12s ago
Main PID: 11883 (patroni)
Tasks: 13 (limit: 2353)
Memory: 93.5M
CGroup: /system.slice/patroni.service
ββ11883 /usr/bin/python3 /usr/local/bin/patroni /etc/patroni.yml
ββ11922 postgres -D /data/patroni --config-file=/data/patroni/postgresql.conf --listen_addresses=172.16.0.101 --port=5432 --clu>
ββ11927 postgres: postgres: checkpointer
ββ11928 postgres: postgres: background writer
ββ11929 postgres: postgres: walwriter
ββ11930 postgres: postgres: autovacuum launcher
ββ11931 postgres: postgres: stats collector
ββ11932 postgres: postgres: logical replication launcher
ββ11934 postgres: postgres: postgres postgres 172.16.0.101(45404) idle
Feb 19 14:31:21 patroni1 patroni[11922]: 2021-02-19 14:31:21.717 UTC [11922] LOG: database system is ready to accept connections
Feb 19 14:31:21 patroni1 patroni[11926]: 172.16.0.101:5432 - accepting connections
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,729 INFO: establishing a new patroni connection to the postgres cluster
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,743 INFO: running post_bootstrap
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,756 WARNING: Could not activate Linux watchdog device: "Can't open watchdog devic>
Feb 19 14:31:21 patroni1 patroni[11883]: 2021-02-19 14:31:21,778 INFO: initialized a new cluster
Feb 19 14:31:31 patroni1 patroni[11883]: 2021-02-19 14:31:31,758 INFO: Lock owner: postgresql0; I am postgresql0
Feb 19 14:31:31 patroni1 patroni[11883]: 2021-02-19 14:31:31,770 INFO: no action. i am the leader with the lock
Feb 19 14:31:31 patroni1 patroni[11883]: 2021-02-19 14:31:31,794 INFO: Lock owner: postgresql0; I am postgresql0
# Configure HAProxy
Next, you will need to configure HAProxy on server4. You can configure it by editing its main configuration file:
nano /etc/haproxy/haproxy.cfg
Remove all the lines and add the following lines:
global
maxconn 100
defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen postgres
bind *:5000
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server postgresql_172.16.0.101_5432 172.16.0.101:5432 maxconn 100 check port 8008
server postgresql_172.16.0.102_5432 172.16.0.102:5432 maxconn 100 check port 8008
Save and close the file then restart the HAProxy service to apply the changes:
systemctl restart haproxy
You can verify the status of the service using the following command:
systemctl status haproxy
Output:
β haproxy.service - HAProxy Load Balancer
Loaded: loaded (/lib/systemd/system/haproxy.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2021-02-19 14:46:54 UTC; 17s ago
Docs: man:haproxy(1)
file:/usr/share/doc/haproxy/configuration.txt.gz
Process: 1620 ExecStartPre=/usr/sbin/haproxy -f $CONFIG -c -q $EXTRAOPTS (code=exited, status=0/SUCCESS)
Main PID: 1631 (haproxy)
Tasks: 2 (limit: 1145)
Memory: 2.0M
CGroup: /system.slice/haproxy.service
ββ1631 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -S /run/haproxy-master.sock
ββ1632 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -S /run/haproxy-master.sock
Feb 19 14:46:54 haproxy haproxy[1631]: [WARNING] 049/144654 (1631) : parsing [/etc/haproxy/haproxy.cfg:26] : 'option httplog' not usable with >
Feb 19 14:46:54 haproxy haproxy[1631]: Proxy stats started.
Feb 19 14:46:54 haproxy haproxy[1631]: Proxy stats started.
Feb 19 14:46:54 haproxy haproxy[1631]: Proxy postgres started.
# Verify PostgreSQL Cluster
At this point, the PostgreSQL cluster is ready. Now, open your web browser and use the HAProxy server IP at http://172.16.0.104:7000 to access the HAProxy web interface. You should see the cluster status in the following image:
In the postgresql section, you should see that the first row is highlighted in green that means it is acting as the master, and the other row highlighted in red is acting as a slave.
# Conclusion
In the above guide, you learned how to set up a high availability PostgreSQL cluster with Patroni on Ubuntu 20.04. You can now add more nodes to increase availability.