How to Set Up a High Available PostgreSQL Cluster Using Patroni
Patroni is an open source tool which can be used for creating and managing customized high available PostgreSQL cluster. It is coded by Zalando Tech with Python.
Also, it can be used for backup ,replication and restore tasks with some configuration parameters. It is providing high availability with ETCD and HaProxy (which gives to application a single endpoint to connect cluster’s leader).
For further information : https://github.com/zalando/patroni
ETCD Installation
ETCD is an open source distributed key value store and provides a reliable system for cluster-wide coordination and state management.It is using to store the state of PostgreSQL clusters .
yum install gcc python-devel
yum install etcdIt uses consensus algorithm ,during leader election, the first node to become a candidate, all the other nodes have to vote for.The algorithm claims that the distributed systems need an odd number of nodes.
For futher information ; https://github.com/etcd-io/etcd
ETCD Configuration
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://IP:2379" ETCD_ADVERTISE_CLIENT_URLS="http://IP_A:2379"
ETCD_INITIAL_CLUSTER_TOKEN="cluster1"
ETCD_INITIAL_CLUSTER_STATE="new"systemctl start etcd
systemctl status etcd Result ;
etcd.service - etcd - highly-available key value store
Loaded: loaded (/lib/systemd/system/etcd.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2019-02-28 22:27:47 IST; 5s ago
Docs: https://github.com/coreos/etcd
man:etcd
Main PID: 4504 (etcd)
CGroup: /system.slice/etcd.service
└─4504 /usr/bin/etcdSep 21 22:27:47 Node2 etcd[4504]: starting server... [version: 2.2.5, cluster version: to_be_decided]
Sep 21 22:27:47 Node2 systemd[1]: Started etcd - highly-available key value store.
Sep 21 22:27:47 Node2 etcd[4504]: added local member ce2a822cea30bfca [http://localhost:2380 http://localhost:7001] to cluster 7e27652122e8b2ae
Sep 21 22:27:47 Node2 etcd[4504]: set the initial cluster version to 2.2
Sep 21 22:27:48 Node2 etcd[4504]: ce2a822cea30bfca is starting a new election at term 5
Sep 21 22:27:48 Node2 etcd[4504]: ce2a822cea30bfca became candidate at term 6
Sep 21 22:27:48 Node2 etcd[4504]: ce2a822cea30bfca received vote from ce2a822cea30bfca at term 6
Sep 21 22:27:48 Node2 etcd[4504]: ce2a822cea30bfca became leader at term 6
Sep 21 22:27:48 Node2 etcd[4504]: raft.node: ce2a822cea30bfca elected leader ce2a822cea30bfca at term 6
Sep 21 22:27:48 Node2 etcd[4504]: published {Name:hostname ClientURLs:[http://IP_A:2379]} to cluster 7e27652122e8b2ae
HaProxy Installation
HAProxy, which stands for High Availability Proxy, is a popular open source software TCP/HTTP Load Balancer and proxying solution.
yum install haproxy
HaProxy Configuration
You can configure editing this file etc/haproxy/haproxy.cfg as below :
global
maxconn 100
defaults
log global
mode tcp
retries 5
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_pg01_5432 IP:5432 maxconn 100 check port 8008
server postgresql_pg02_5432 IP:5432 maxconn 100 check port 8008
server postgresql_pg03_5432 IP:5432 maxconn 100 check port 8008
systemctl start haproxy && systemctl status haproxy
For further information ; http://www.haproxy.org
Patroni Installation
yum install gcc python-devel
yum install python-psycopg2 python-pip PyYAML
yum install python2-pip
pip install — upgrade pip
pip install — upgrade setuptools
pip install patroni
pip install python-etcd
pip install psycopg2-binary
Patroni uses YAML file to store its configuration.
! It is really important before using Patroni to install PostgreSQL’s RPM repository , client and server packages.
You can use this link to configure your YAML file; https://github.com/zalando/patroni/blob/master/postgres1.yml
!! Another important part is ; if the new Postgres setups are done it won’t be necessary to initialize(initdb) the PostgreSQL , initdb can be done with appropriate configuration by Patroni.
!!! On operating system , you need to set environment variable like this ;
export PATH=/usr/pgsql-$postgres_version/bin/$PATH
Now , you can start patroni process with command ; “patroni postgres0.yml” or you can start patroni process with patroni.service file as below :
Patroni service file ;
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
# Read in configuration file if it exists, otherwise proceed
EnvironmentFile=-/etc/patroni_env.conf
WorkingDirectory=~
# Where to send early-startup messages from the server
# This is normally controlled by the global default set by systemd
#StandardOutput=syslog
# Pre-commands to start watchdog device
# Uncomment if watchdog is part of your patroni setup
#ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
#ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
# Start the patroni process
ExecStart= /bin/patroni postgres0.yml
# Send HUP to reload from patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
# only kill the patroni process, not it’s children, so it will gracefully stop postgres
KillMode=process
#Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=30
# Do not restart the service if it crashes, we want to manually inspect database on failure
Restart=no
[Install]
WantedBy=multi-user.target
systemctl daemon-reload && service patroni start
That’s it.
Note that ;
Every server in cluster you need to install epel repo.
Need to set environment variable to postgres’ binary file path on Postgres servers .
You can create a highly available and scalable PostgreSQL database cluster with Patroni as above. In this way, it is possible to scale your read requests on HAProxy with a proper configuration by expanding to the horizontal under heavy load with zero downtime.
