DigitalOcean
SnapShooter

How to Set Up PostgreSQL Cluster using Patroni on Ubuntu 20.04

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.

Was this page helpful?

Thank you for helping us improve!