pg_cirrus – Hassle-free PostgreSQL Cluster Setup
pg_cirrus is a simple and automated solution to deploy highly available 3-node PostgreSQL cluster with load balancing and auto failover. It is built using Ansible and Python. pg_cirrus uses pgpool to perform auto failover and load balancing.
You can read more about pg_cirrus here: https://stormatics.tech/pg_cirrus-hassle-free-postgresql-cluster-setup
Github repository for pg_cirrus can be found here: https://github.com/stormatics/pg_cirrus
Here are the detailed steps to configure 3-node PostgreSQL cluster using pg_cirrus.
Prerequisites
Configure Primary, Standby1 and Standby2 Nodes
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 passwordless sudo privileges
sudo adduser postgres
sudo visudo
Add the following string at the end of visudo file
postgres ALL=(ALL) NOPASSWD:ALL
Configure 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
Install python3
sudo apt-get install python3 -y
Create postgres user and give it passwordless sudo privileges
sudo adduser postgres
sudo visudo
Add the following string at the end of visudo file
postgres ALL=(ALL) NOPASSWD:ALL
Generate ssh key-pair for postgres user if key-pair is not already generated
NOTE: If you have already generated public key of postgres user you may skip this step
ssh-keygen
Copy public key of postgres user from pgpool node to standby1, standby2 and primary nodes for ssh passwordless access
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/3-node-cluster/
Create vault.yml file using Ansible vault
ansible-vault create vault.yml
Write following lines inside vault.yml
PSQL_SERVER_PASSWORD: <your_postgres_database_password>
REPUSER_PASSWORD: <your_replication_user_password>
Create file to store vault password
touch /home/postgres/pg_cirrus/3-node-cluster/vault-pass-file
chmod 0600 /home/postgres/pg_cirrus/3-node-cluster/vault-pass-file
Please insert your vault password in plain text inside vault-pass-file.
Execute pg_cirrus
Run Python script “main.py”
python3 deploy.py
Vault password – Specify absolute path for vault password file
Ansible vault password file: /home/postgres/pass
All checks for VAULT_PASSWORD_FILE were passed
PostgreSQL version – pg_cirrus will fetch the latest PostgreSQL version. You can enter a version or continue with the latest one
Getting latest PostgreSQL stable version ...
Enter PostgreSQL version (Latest: 15):
PostgreSQL port – If custom port number is entered that will be used else default port 5432 will be used
Enter the PostgreSQL port number: (Default: 5432):
Data Directory Path – If custom path is entered that will be used else default path “/home/postgres/stormatics/pg_cirrus/data” will be selected
Enter the Data Directory Path: (Default: /home/postgres/stormatics/pg_cirrus/data):
Subnet – pg_cirrus assumes that all the nodes will be inside the same subnet, please specify IP address of your subnet
Subnet address for the cluster: 172.16.14.0/24
Please specify IP address for Primary node
Primary PostgreSQL Server IP address: 172.16.14.165
Please specify IP addresses for Standby nodes
Standby 1 IP address: 172.16.14.162
Standby 2 IP address: 172.16.14.163
Please specify the IP address of your current machine to setup pgpool
IP address of this node to setup pgpool: 172.16.14.154
Inventory and conf.yml files will be generated, playbooks will start executing
Generating conf.yml ...
Generating inventory file ...
After playbooks have been successfully executed the 3-node HA cluster will be smoothly running.
Connect to pgpool node from client machine using psql
We are now ready to connect with newly deployed cluster
psql -h <pgpool_node_ip> -p 9999 -d postgres -U postgres
You will be prompted for password please enter your PSQL_SERVER_PASSWORD to connect.