Stormatics
(678) 345-3456
380 Albert St, Melbourne, Australia

Blog Details

  • Stormatics
  • Blog
  • Setting Up a PostgreSQL 3-Node HA Cluster using pg_cirrus

Setting Up a PostgreSQL 3-Node HA Cluster using pg_cirrus

What is a 3-node cluster and why it is needed for enterprises

A 3-node database cluster is a setup in which 3 hosts are configured to run database server. There are 2 standby nodes and one primary node. Data in the primary node is replicated to standby nodes, if the primary node goes down one of the standby nodes is promoted to primary node to ensure that the database is highly available. 

 

Enterprises that can only afford to bear minimal loss use 3-node cluster primarily for the following reasons:

High availability: A 3-node cluster provides high availability and redundancy, ensuring that the database is always available. If the primary node fails, a standby node will take over, hence downtime will be minimized.

Load balancing: A 3-node cluster can distribute the workload across the nodes, allowing for better performance and resource utilization. The primary node will handle write requests whereas the read requests will be handled by the standby nodes.

Disaster recovery: In the event of a disaster or system failure, a 3-node cluster can ensure that data loss is minimal and can be restored from backups.

Steps to configure pg_cirrus

Here are the few terminologies that are used in pg_cirrus configuration

pgpool node – This is the host where pg_cirrus will be run and pgpool will be installed
Primary node – This is the host where primary server will be installed
Standby1 node – This is the host where standby server 1 will be installed
Standby2 node – This is the host where standby server 2 will be installed

Prerequisites

These are the steps that must be performed on different nodes before running pg_cirrus.

On Primary, Standby1 and Standby2 Nodes

Please perform following steps on primary, Standby1 and Standby2 nodes in your cluster

Update local apt package manager
sudo apt-get update -y
Install OpenSSH Server
sudo apt-get install openssh-server -y
Create postgres user and give it sudo privileges 
sudo adduser postgres
sudo usermod -aG sudo postgres

On pgpool Node

Update local apt package manager
sudo apt-get update -y
Install OpenSSH Server
sudo apt-get install openssh-server -y
Install Ansible
sudo apt-get install ansible -y
Install Git
sudo apt-get install git -y
Generate SSH key pair

Generate ssh key-pair for root user if key-pair is not already generated

sudo su -
ssh-keygen 
Setup passwordless access from pgpool node to all nodes in cluster

Copy public key of root user on pgpool host to standby1, standby2 and primary hosts for ssh passwordless access

sudo su -
ssh-copy-id postgres@$PRIMARY_IP
ssh-copy-id postgres@$STANDBY1_IP
ssh-copy-id postgres@$STANDBY2_IP
Clone github repository
git clone https://github.com/stormatics/pg_cirrus 
Go into the directory 
cd pg_cirrus/highly-available-cluster/ 
Rename hosts.yml.in file to hosts.yml and update
mv hosts.yml.in hosts.yml  
Update hosts.yml file

Update hosts.yml according to your configurations i.e.
PG_PRIMARY_HOST stands for Primary Node
PG_SB1_HOST stands for Standby1 Node
PG_SB2_HOST stands for Standby2 Node

Replace ansible_host value from xxx.xxx.xxx.xxx to IP address of your respective nodes.
Replace ansible_become_pass from xxxxxxxx to sudo password of postgres user on your respective nodes.

NOTE: You can find your ip address on primary, standby1 and standby2 nodes using ifconfig command.

Execute pg_cirrus

sudo ansible-playbook -i hosts.yml setup.yml

Once you have successfully executed the above mentioned command your 3-node cluster must now be running smoothly.

Verify your 3-node cluster

To verify your cluster you can connect to your cluster via the pgpool node from a new machine. 

While you are on the pgpool node stop the pgpool service and start again using pgpool command so that logs can be displayed on pgpool node.

Stop pgpool service
sudo service pgpool2 stop
Start pgpool
sudo pgpool -n &

On New machine

Install postgresql-client 

Create the file repository configuration

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Update the package lists
sudo apt-get update
Install postgresql-client
sudo apt-get -y install postgresql-client
Connect to 3-node cluster via pgpool node
psql -h <pgpool_node_IP> -p 9999 -d postgres -U postgres

Now you are connected to pgpool host and you can execute your queries

Thank you for taking the time to learn about the alpha release of pg_cirrus. We’re thrilled to announce the alpha release of pg_cirrus. While this is just the first step on our journey, we’re excited to share this early version with our community and gather feedback to help us make it even better. So stay tuned and get ready for more updates as we move forward on this exciting journey together.

For any questions or comments, please write to us at [email protected]

Leave A Comment