pg_cirrus – Hassle-free PostgreSQL Cluster Setup

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.