In our previous blog post, we have introduced steps and techniques how to install PostgreSQL 14 from source code on Google Cloud Engine. By doing this, we have only one standalone instance running which is rare in real-world application use cases. Usually, a complete set of database clusters would be one master and two standby instances which we also used to set up on the same server with three PostgreSQL instances (each instance running in a different port on the same server) in this article. In this experiment, we are going to create new Google GCEs(Google Cloud Engine) and set them up as standby database servers to replicate data from the master by using the repmgr tool.

In this approach, we will create two new Google GCEs for standby servers then set up PostgreSQL 14 in both standby servers (Without initializing data directory), and use the repmgr tool to register master and the two standby to the database cluster which you can fully control and manage through commands provided by repmgr. This article covers ways to manage and maintain clusters in case of any incident happened.

1. Creating Google Cloud Engine (Standby 1 and 2)

In the previous post, we already set up a running PostgreSQL which we will consider as a DB master in this experiment. Therefore, now let's create two new GCEs for standby 1 and standby 2 in Google Cloud Console using OS: Ubuntu 20.40 or you can check out this article as a guideline of how to set it up.

Since in the previous blog we used Ansible to control our single database instance, now let's check if we can SSH to the newly created standby because we are planning to use Ansible as a control to our database cluster.

sethathay@instance-2:~$ ssh standby1
sethathay@instance-2:~$ ssh standby2

//instance-2 is an ansible server to manage the database cluster

2. Installation of PostgreSQL

We will install PostgreSQL 14 in the newly created Google Cloud Engine that will perform as the standby role for our database cluster without initiating the data cluster. We will use repmgr to initiate and clone the data cluster later while registration to repmgr DB cluster. The guideline of how to install PostgreSQL 14 is here. 

3. Installation of repmgr Tool

Repmgr is a product of 2ndQuadrant for managing replication and failover in a cluster of PostgreSQL servers. Repmgr provides built-in hot-standby capabilities with tools to set up standby servers, monitor replication, perform administrative tasks such as failover or manual switchover operations. We will follow this guideline to set up and configure repmgr for our database cluster. Below is the figure of our experiment architecture.

4. Register Node to Cluster

After you have followed the guidelines that we shared in the above steps, now let do the configuration of repmgr of the master, standby1, and standby 2 as following

• MASTER

node_id=1
node_name='master-db'
conninfo='host=10.148.0.2 port=5432 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/data01'
pg_bindir='/app/postgres/product/14/db_1/bin/'
service_start_command = 'sudo systemctl start postgresql'
service_stop_command = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command = 'sudo systemctl reload postgresql'

Next, register to repmgr cluster

/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf primary register --dry-run
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf primary register

//Check status of repmgr cluster
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf cluster show --compact

Result

• STANDBY 1

node_id=2
node_name='standby1-db'
conninfo='host=10.148.0.5 port=5432 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/data01'
pg_bindir='/app/postgres/product/14/db_1/bin/'
service_start_command = 'sudo systemctl start postgresql'
service_stop_command = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command = 'sudo systemctl reload postgresql'

Next, clone the data directory from master and register to repmgr cluster

//Cloning the Standby Nodes
/app/postgres/product/14/db_1/bin/repmgr  -h 10.148.0.2 -p5432 -U repmgr -d repmgr -f /app/repmgr/5.2/repmgr1.conf standby clone --dry-run
/app/postgres/product/14/db_1/bin/repmgr  -h 10.148.0.2 -p5432 -U repmgr -d repmgr -f /app/repmgr/5.2/repmgr1.conf standby clone

//Start postgresql instance of standby 1
sudo systemctl start postgresql

//Register standby1 into repmgr cluster
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf standby register --dry-run
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf standby register

//Check status of repmgr cluster
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf cluster show --compact

Result

• STANDBY 2

node_id=3
node_name='standby2-db'
conninfo='host=10.148.0.6 port=5432 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/data01'
pg_bindir='/app/postgres/product/14/db_1/bin/'
service_start_command = 'sudo systemctl start postgresql'
service_stop_command = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command = 'sudo systemctl reload postgresql'

Next, clone the data directory from master and register to repmgr cluster

//Cloning the Standby Nodes
/app/postgres/product/14/db_1/bin/repmgr  -h 10.148.0.2 -p5432 -U repmgr -d repmgr -f /app/repmgr/5.2/repmgr1.conf standby clone --dry-run
/app/postgres/product/14/db_1/bin/repmgr  -h 10.148.0.2 -p5432 -U repmgr -d repmgr -f /app/repmgr/5.2/repmgr1.conf standby clone

//Start postgresql instance of standby 1
sudo systemctl start postgresql

//Register standby1 into repmgr cluster
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf standby register --dry-run
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf standby register

//Check status of repmgr cluster
/app/postgres/product/14/db_1/bin/repmgr -f /app/repmgr/5.2/repmgr1.conf cluster show --compact

Result

Finally, you may want to find the necessary ways to manage and maintain the repmgr cluster as database administration. We have provided this article with PostgreSQL 13 but it's more likely the same way of using in PostgreSQL 14.

THANK YOU, Find Us @
Facebook
Telegram
Twitter
LinkedIn