Friday , March 29 2024

How To Create A PostgreSQL Cluster With Patroni

Today I will tell you How To Create A PostgreSQL Cluster With Patroni. I will be using Centos 7 as the operating system.

First of all, we will need 4 virtual servers with Centos 7 installed. You can access Centos 7 installation from the link below.

2. We install the epel repo on all our servers.

3. Then we restart our servers.

4. Install PostgreSQL 12 on pg_node1 and pg_node2.

5. We install Patroni on pg_node1 and pg_node2.

6. We create a config file for Patroni on pg_node1 and pg_node2.

We enter the configuration file with vi and edit it as follows.

For pg_node1 :

For pg_node2:

7. We install etcd on the etcd server.

8. After the installation is finished, we make the following changes in the configuration file.

9. We start the etcd service.

etcd service status

10. After starting the etcd service, we start the Patroni service in pg_node1 and pg_node2.

pg_node1 :

pg_node1 patroni status
pg_node2 :
pg_node2 patroni status

11. We install HAProxy on the haproxy server.

12. After the installation is finished, the configuration file is backed up and arranged as follows.

13. We start the HAProxy service.

HAProxy service status

If the HAProxy service cannot be started, we can check the errors in the configuration file with the following command.

When we open the address http://192.168.1.21:7000 in the browser, we will see a haproxy panel like the one below. As can be seen here, pg_node1 is the master and pg_node2 is the slave.

We can also check the cluster status, role and members with the following command.

We have completed the cluster setup with Patroni. See you in my other articles.

 

Loading

About Melek Durdu

One comment

  1. I have referred above documentation with 4 machines having CentOS7, please refer below installed packages/ libraries as mentioned below
    Strange part is both partroni nodes are having different status logs. The file coming in logs of node1 “/var/lib/pgsql/15/data/postgresql.conf” is not present in node2, even folder “/var/lib/pgsql/15/data” is not present.

    node 1
    patroni.x86_64 1.6.5-1.rhel7
    postgresql15.x86_64 15.2-1PGDG.rhel7
    postgresql15-devel.x86_64 15.2-1PGDG.rhel7
    postgresql15-libs.x86_64 15.2-1PGDG.rhel7
    postgresql15-server.x86_64 15.2-1PGDG.rhel7

    node 2
    patroni.x86_64 1.6.5-1.rhel7
    postgresql15.x86_64 15.2-1PGDG.rhel7
    postgresql15-devel.x86_64 15.2-1PGDG.rhel7
    postgresql15-libs.x86_64 15.2-1PGDG.rhel7
    postgresql15-server.x86_64 15.2-1PGDG.rhel7

    node 3
    etcd.x86_64 3.3.11-2.el7.centos

    node 4
    haproxy.x86_64 1.5.18-9.el7_9.1

    —————————————
    —————————————
    Refer below status from each node:

    node 1 status

    patroni.service – PostgreSQL high-availability manager
    Loaded: loaded (/usr/lib/systemd/system/patroni.service; enabled; vendor preset: disabled)
    Active: active (running) since Mon 2023-08-21 17:33:52 IST; 6min ago
    Main PID: 236874 (python3.6)
    Tasks: 5
    Memory: 19.8M
    CGroup: /system.slice/patroni.service
    └─236874 python3.6 /opt/app/patroni/bin/patroni /opt/app/patroni/etc/postgresql.yml

    Aug 21 17:40:43 patroni[236874]: Mock authentication nonce: 46384283d89d7a56320d5d1c488e90a400e61e45ef9ed7d6f081c0574e57a893
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 17:40:43,146 INFO: Lock owner: None; I am pg_node1
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 17:40:43,156 INFO: Lock owner: None; I am pg_node1
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 17:40:43,159 INFO: starting as a secondary
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 12:10:43.421 GMT [237946] LOG: unrecognized configuration parameter “wal_keep_segments” in file “/var/lib/pgsql/15/data/postgresql.conf” line 16
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 12:10:43.421 GMT [237946] FATAL: configuration file “/var/lib/pgsql/15/data/postgresql.conf” contains errors
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 17:40:43,432 INFO: postmaster pid=237946
    Aug 21 17:40:43 patroni[236874]: 172.27.55.31:5432 – no response
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 17:40:43,444 INFO: Lock owner: None; I am pg_node1
    Aug 21 17:40:43 patroni[236874]: 2023-08-21 17:40:43,447 INFO: failed to start postgres
    —————————————
    —————————————
    node 2 status

    patroni.service – PostgreSQL high-availability manager
    Loaded: loaded (/usr/lib/systemd/system/patroni.service; enabled; vendor preset: disabled)
    Active: active (running) since Fri 2023-08-18 12:28:51 IST; 3 days ago
    Main PID: 5924 (python3.6)
    Tasks: 5
    Memory: 21.6M
    CGroup: /system.slice/patroni.service
    └─5924 python3.6 /opt/app/patroni/bin/patroni /opt/app/patroni/etc/postgresql.yml

    Aug 21 17:40:51 patroni[5924]: 2023-08-21 17:40:51,778 INFO: Lock owner: None; I am pg_node2
    Aug 21 17:40:51 patroni[5924]: 2023-08-21 17:40:51,780 INFO: waiting for leader to bootstrap
    Aug 21 17:41:01 patroni[5924]: 2023-08-21 17:41:01,779 INFO: Lock owner: None; I am pg_node2
    Aug 21 17:41:01 patroni[5924]: 2023-08-21 17:41:01,780 INFO: waiting for leader to bootstrap
    Aug 21 17:41:11 patroni[5924]: 2023-08-21 17:41:11,779 INFO: Lock owner: None; I am pg_node2
    Aug 21 17:41:11 patroni[5924]: 2023-08-21 17:41:11,781 INFO: waiting for leader to bootstrap
    Aug 21 17:41:21 patroni[5924]: 2023-08-21 17:41:21,778 INFO: Lock owner: None; I am pg_node2
    Aug 21 17:41:21 patroni[5924]: 2023-08-21 17:41:21,780 INFO: waiting for leader to bootstrap
    Aug 21 17:41:31 patroni[5924]: 2023-08-21 17:41:31,779 INFO: Lock owner: None; I am pg_node2
    Aug 21 17:41:31 patroni[5924]: 2023-08-21 17:41:31,780 INFO: waiting for leader to bootstrap
    —————————————
    —————————————

    node 3 status

    etcd.service – Etcd Server
    Loaded: loaded (/usr/lib/systemd/system/etcd.service; enabled; vendor preset: disabled)
    Active: active (running) since Fri 2023-08-18 11:35:05 IST; 3 days ago
    Main PID: 46856 (etcd)
    Tasks: 18
    Memory: 118.3M
    CGroup: /system.slice/etcd.service
    └─46856 /usr/bin/etcd –name=default –data-dir= –listen-client-urls=http://:2379,http://localhost:2379

    Aug 20 12:40:54 etcd[46856]: saved snapshot at index 400004
    Aug 20 12:40:54 etcd[46856]: compacted raft log at 395004
    Aug 20 17:27:08 etcd[46856]: segmented wal file default.etcd/member/wal/0000000000000001-000000000006b47b.wal is created
    Aug 21 00:47:09 etcd[46856]: start to snapshot (applied: 500005, lastsnap: 400004)
    Aug 21 00:47:09 etcd[46856]: saved snapshot at index 500005
    Aug 21 00:47:09 etcd[46856]: compacted raft log at 495005
    Aug 21 12:53:25 etcd[46856]: start to snapshot (applied: 600006, lastsnap: 500005)
    Aug 21 12:53:25 etcd[46856]: saved snapshot at index 600006
    Aug 21 12:53:25 etcd[46856]: compacted raft log at 595006
    Aug 21 12:53:36 etcd[46856]: purged file default.etcd/member/snap/0000000000000002-00000000000186a1.snap successfully
    —————————————
    —————————————

    node 4 status

    haproxy.service – HAProxy Load Balancer
    Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
    Active: active (running) since Thu 2023-08-17 20:16:50 IST; 3 days ago
    Main PID: 133274 (haproxy-systemd)
    Tasks: 3
    Memory: 1.9M
    CGroup: /system.slice/haproxy.service
    ├─133274 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
    ├─133275 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
    └─133276 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds

    Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.
    —————————————
    —————————————

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories