For those who seek help in different areas of software and hardware platform.

How To Create a Highly Available PostgreSQL Cluster on CentOS/RHEL 8

This tutorial will walk you through the steps to set up a highly available PostgreSQL cluster using Patroni and HAProxy on CentOS/RHEL 8.



Prerequisites

To follow this tutorial, you will need 5 (physical or virtual) machines with CentOS or RHEL 8 server with minimal installed, having sudo non-root user privileges. We have already prepared the following 5 machines with CentOS release 8.1.1911 for this guide. However, if you wish you can add up more machines in your cluster environment.

HOSTNAME IP ADDRESS PURPOSE
node1 192.168.10.1 Postgresql, Patroni
node2 192.168.10.2 Postgresql, Patroni
node3 192.168.10.3 Postgresql, Patroni
node4 192.168.10.4 etcd
node5 192.168.10.5 HAProxy

If you wish, you can watch the below quick video tutorial to set up your Postgres cluster environment:

If you are not comfortable with the video tutorial, please follow the below step by step instruction:

Adding EPEL Repository

It is always recommended to install extra packages for enterprise Linux repository before installing any other packages on your server.

Type below command to add epel repo on (node1, node2, node3) only:
dnf -y install epel-release
dnf config-manager --set-enabled PowerTools
dnf -y install yum-utils

Adding PostgreSQL Repository

PostgreSQL version 12 is not available in CentOS/RHEL 8 default repository, therefore we need to install official repository of Postgres with below command:

Type below add Postgres repo on (node1, node2, node3) only:
dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum-config-manager --enable pgdg12
dnf -qy module disable postgresql

Installing PostgreSQL

We are installing PostgreSQL version 12 for this guide. If you wish, you can install any other version of your choice:

Type below command to install Postgres version 12 on (node1, node2, node3) only:
dnf -y install postgresql12-server postgresql12 postgresql12-devel

Installing Patroni

Patroni is an open-source python package that manages Postgres configuration. It can be configured to handle tasks like replication, backups, and restorations.

Type below command to install patroni on (node1, node2, node3) only:
dnf -y install https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/python3-psycopg2-2.7.7-2.el7.x86_64.rpm
dnf -y install https://github.com/cybertec-postgresql/patroni-packaging/releases/download/1.6.0-1/patroni-1.6.0-1.rhel7.x86_64.rpm

Configuring Patroni

Patroni can be configured using a YAML file which is by default located under /opt/app/patroni/etc/ with appropriate permission.

Type below command on (node1, node2, node3) to copy postgresql.yml.sample to postgresql.yml:
cp -p /opt/app/patroni/etc/postgresql.yml.sample /opt/app/patroni/etc/postgresql.yml
Now you need to edit postgresql.yml file on each node (node1, node2, node3) with any of your preferred editor i.e. vi, vim, nano, etc:
vi /opt/app/patroni/etc/postgresql.yml
Remove everything from this file and add below configuration parameters:
scope: postgres
namespace: /pg_cluster/
name: node1

restapi:
    listen: 192.168.10.1:8008
    connect_address: 192.168.10.1:8008

etcd:
    host: 192.168.10.4:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:

  initdb:
  - encoding: UTF8
  - data-checksums

  pg_hba:
  - host replication replicator 127.0.0.1/32 md5
  - host replication replicator 192.168.10.1/0 md5
  - host replication replicator 192.168.10.2/0 md5
  - host replication replicator 192.168.10.3/0 md5
  - host all all 0.0.0.0/0 md5

  users:
    admin:
      password: admin
      options:
        - createrole
        - createdb

postgresql:
  listen: 192.168.10.1:5432
  connect_address: 192.168.10.1:5432
  data_dir: /var/lib/pgsql/12/data
  bin_dir: /usr/pgsql-12/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: reppassword
    superuser:
      username: postgres
      password: postgrespassword

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
Change the name to something unique, and change listen and connect_address (under postgresql and restapi) to the appropriate values on each node (node1, node2, node3 in our case).

Save and close the file when you are finished.

For reference, you can see the below screenshots of /opt/app/patroni/etc/postgresql.yml from node1 in our setup:


below is from node2:


and below is from node3:


Make sure you have performed all of the above steps on each node that are designated for postgresql and patroni (node1, node2, node3 in our case) before going to the next step of installing and configuring etcd.

Installing etcd

Etcd is a fault-tolerant, distributed key-value store that is used to store the state of the Postgres cluster. Using Patroni, all of the Postgres nodes make use of etcd to keep the Postgres cluster up and running.

For the sake of this guide, we will use a single-server etcd cluster. However, in production, it may be best to use a larger etcd cluster so that one etcd node fails, it doesn’t affect your Postgres servers.

Type below command to install etcd on a node that is designated for etcd (node4 in our case):
dnf -y install http://mirror.centos.org/centos/7/extras/x86_64/Packages/etcd-3.3.11-2.el7.centos.x86_64.rpm

Configuring etcd

At this point, you need to edit the /etc/etcd/etcd.conf file like below:
vi /etc/etcd/etcd.conf
uncomment by removing # from the following highlighted configuration parameters and make sure you replace IP address of the etcd node with yours:
#[Member]
#ETCD_CORS="" ETCD_DATA_DIR="/var/lib/etcd/default.etcd" #ETCD_WAL_DIR="" ETCD_LISTEN_PEER_URLS="http://192.168.10.4:2380" ETCD_LISTEN_CLIENT_URLS="http://192.168.10.4:2379" #ETCD_MAX_SNAPSHOTS="5" #ETCD_MAX_WALS="5" ETCD_NAME="default" #ETCD_SNAPSHOT_COUNT="100000" #ETCD_HEARTBEAT_INTERVAL="100" #ETCD_ELECTION_TIMEOUT="1000" #ETCD_QUOTA_BACKEND_BYTES="0" #ETCD_MAX_REQUEST_BYTES="1572864" #ETCD_GRPC_KEEPALIVE_MIN_TIME="5s" #ETCD_GRPC_KEEPALIVE_INTERVAL="2h0m0s" #ETCD_GRPC_KEEPALIVE_TIMEOUT="20s" # #[Clustering] ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.10.4:2380" ETCD_ADVERTISE_CLIENT_URLS="http://192.168.10.4:2379" #ETCD_DISCOVERY="" #ETCD_DISCOVERY_FALLBACK="proxy" #ETCD_DISCOVERY_PROXY="" #ETCD_DISCOVERY_SRV="" ETCD_INITIAL_CLUSTER="default=http://192.168.10.4:2380" ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster" ETCD_INITIAL_CLUSTER_STATE="new" #ETCD_STRICT_RECONFIG_CHECK="true" #ETCD_ENABLE_V2="true" # #[Proxy] #ETCD_PROXY="off" #ETCD_PROXY_FAILURE_WAIT="5000" #ETCD_PROXY_REFRESH_INTERVAL="30000" #ETCD_PROXY_DIAL_TIMEOUT="1000" #ETCD_PROXY_WRITE_TIMEOUT="5000" #ETCD_PROXY_READ_TIMEOUT="0" # #[Security] #ETCD_CERT_FILE="" #ETCD_KEY_FILE="" #ETCD_CLIENT_CERT_AUTH="false" #ETCD_TRUSTED_CA_FILE="" #ETCD_AUTO_TLS="false" #ETCD_PEER_CERT_FILE="" #ETCD_PEER_KEY_FILE="" #ETCD_PEER_CLIENT_CERT_AUTH="false" #ETCD_PEER_TRUSTED_CA_FILE="" #ETCD_PEER_AUTO_TLS="false" # #[Logging] #ETCD_DEBUG="false" #ETCD_LOG_PACKAGE_LEVELS="" #ETCD_LOG_OUTPUT="default" # #[Unsafe] #ETCD_FORCE_NEW_CLUSTER="false" # #[Version] #ETCD_VERSION="false" #ETCD_AUTO_COMPACTION_RETENTION="0" # #[Profiling] #ETCD_ENABLE_PPROF="false" #ETCD_METRICS="basic" # #[Auth] #ETCD_AUTH_TOKEN="simple"
Save and close the file when you are finished.

Now start etcd service on (node4 in our case) to make changes into effect with below command:
systemctl enable etcd
systemctl start etcd
systemctl status etcd
If everything goes well, you will see the output similar to like below screenshot:


Starting Patroni At this point, you need to start patroni service on your first node (node1 in our case):
systemctl enable patroni
systemctl start patroni
systemctl status patroni
If everything was set up correctly, you will see the output similar to like below screenshot.


When starting patroni on subsequent nodes, (node2, node3 in our case) the output will look similar to like below:

The output from node2:


The output from node3:


Make sure patroni service is running on each node (node1, node2, node3 in our case) before going to the next step of installing and configuring haproxy.

Installing HAProxy

When developing an application that uses a database, it can be cumbersome to keep track of the database endpoints if they keep changing. Using HAProxy simplifies this by giving a single endpoint to which you can connect the application.

HAProxy forwards the connection to whichever node is currently the master. It does this using a REST endpoint that Patroni provides. Patroni ensures that, at any given time, only the master Postgres node will appear as online, forcing HAProxy to connect to the correct node.

Type the below command to install HAProxy on (node5 in our case):
dnf -y install haproxy

Configuring HAProxy

With the Postgres cluster set up, you need a method to connect to the master regardless of which of the servers in the cluster is the master. This is where HAProxy steps in.

All Postgres clients (your applications, psql, etc.) will connect to HAProxy which will make sure you connect to the master node in the cluster.

Take the backup of the original file first with below command:
cp -p /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig
vi /etc/haproxy/haproxy.cfg
Remove everything from this file, add below configuration parameters but make sure you replace highlighted text with yours:
global
maxconn 100 log 127.0.0.1 local2 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 node1 192.168.10.1:5432 maxconn 100 check port 8008 server node2 192.168.10.2:5432 maxconn 100 check port 8008 server node3 192.168.10.3:5432 maxconn 100 check port 8008
Save and close the file when you are finished. 

Now start HAProxy to take changes into effect with the below command:
systemctl enable haproxy
systemctl start haproxy
systemctl status haproxy
You will see the output similar to like below screenshot.

haproxy -c -V -f /etc/haproxy/haproxy.cfg

Testing Postgres HA Cluster Setup

Connect Postgres clients to the HAProxy IP address of the node on which you installed HAProxy (in this guide, 192.168.10.5) on port 5000 to verify your HA Cluster setup.


As you can see in the above screenshot, the client machine is able to make a connection to the Postgres server via haproxy.

You can also access HAProxy node (192.168.10.5 in our case) on port 7000 using any of your preferred web browsers to see your HA Cluster status on HAProxy dashboard like below:


As you can see in the above screenshot, the (node1) row is highlighted in green. This indicates that (node1 192.168.10.1) is currently acting as the master.

If you kill the primary node with systemctl stop patroni command or by completely shutting down the server, the dashboard will look similar to like below:


In the Postgres section in the above screenshot, the (node1) row is now red and the (node2) row is highlighted in green. This indicates that (node2 192.168.10.2) is currently acting as the master.

Note: In this case, it just so happens that the second Postgres server is promoted to master. This might not always be the case if you have more than two nodes in the cluster. It is equally likely that the third server may be promoted to master.

When you bring up the first server, it will rejoin the cluster as a slave and will sync up with the master.

Wrapping up

You now have a robust, highly available Postgres cluster ready for use. While the setup in this tutorial should go far in making your Postgres deployment highly available, here are a few more steps you can take to improve it further:

Use a larger etcd cluster to improve availability.
Use PgBouncer to pool connections.
Add another HAProxy server and configure IP failover to create a highly available HAProxy cluster.
Share:

0 comments:

Post a Comment

Comments with links will not be published.

Video Tutorials