Set Up a Highly Available EDB PostgreSQL12 Cluster on CentOS8,RHEL8

This guide will help you to set up a 3-node highly available cluster with EDB Postgres Advanced Server version 12.10, and EDB Failover Manager version 4.4.

EDB Postgres Advanced Server is built on open-source PostgreSQL Server, which introduces myriad enhancements that enable databases to scale up and scale out in more efficient ways.

EFM is a high-availability module from EnterpriseDB that enables a Postgres Primary node to automatically failover to a standby node in the event of a software or hardware failure on the primary.

 

Prepare Primary Server First


Disable Selinux

If you are not familiar with selinux, you should change SELINUX=permissive to SELINUX=disabled for smooth installation, and configuration.
sudo nano /etc/selinux/config

SELINUX=disabled

Save and close the editor when you are finished.

 

Stop Built-in Firewall

If you are not familiar with OS built-in firewall configuration, you should stop its service before proceeding with the installation, and configuration.
sudo systemctl stop firewalld
sudo systemctl disable firewalld

SET HOSTNAME

Type below command to set the appropriate hostname for your primary server:
sudo hostnamectl set-hostname edb_node1

UPDATE HOSTS FILE 

We are using /etc/hosts file for name resolution:
sudo nano /etc/hosts

192.168.10.1	edb_node1
192.168.10.2	edb_node2
192.168.10.3	edb_node3

Save and close the editor when you are finished.

 

Install EPEL, EDB Repository

You can install EPEL, and EDB repositories like below:
sudo dnf -y install epel-release
sudo dnf -y install https://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm
Edit edb.repo with any of your favorite text editor, and update username, and password value with yours:
sudo nano /etc/yum.repos.d/edb.repo
Navigate to https://www.enterprisedb.com/accounts/profile to get your username and password:
[edb]
name=EnterpriseDB RPMs $releasever - $basearch
baseurl=https://<username>:<password>@yum.enterprisedb.com/edb/redhat/rhel-$releasever-$basearch
enabled=1
repo_gpgcheck=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/ENTERPRISEDB-GPG-KEY

Save and close the editor when you are finished.

 

Install Java

Type below command to install java:

sudo dnf -y update
sudo dnf -y install java
Type below command to disable built-in postgresql module:
sudo dnf -qy module disable postgresql

Install EDB & EFM

We will install Postgres Advanced Server version 12, and Failover Manager version 4.4. If you wish, you can install any other version of your choice:
sudo dnf -y install edb-as12-server edb-efm44
Type below command to add enterprisedb user account to sudo group, and create password for the account as well:
sudo usermod -aG wheel enterprisedb
sudo passwd enterprisedb

Clone VM

If you are working in a virtual environment like me, you can clone your VM for your standby nodes (edb_node2, edb_node3) in our case. 
 
You will, then only need to change IP, and Hostname of your standby nodes. 
 
For instance, if you are working on a bare-metal, and you have three different machines, then you have to perform all of the above steps one by one on each of the node before proceeding with the steps below.
 

SET UP SSH KEY-PAIR 

Log in to your primary server (edb_node1) in our case, with the enterprisedb account, and proceed with the following:
ssh-keygen
ssh-copy-id edb_node2
ssh-copy-id edb_node3

Configure Primary Database

Type below command to initialize database cluster on your primary node:
sudo PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as12/bin/edb-as-12-setup initdb
  Edit pg_hba.conf with any of your favorite text editor:
cd $PGDATA
nano pg_hba.conf
Add your primary, and standby nodes IP entries like below:
# IPv4 local connections:
host    all             all             192.168.10.1/32         trust
host    all             all             192.168.10.2/32         trust
host    all             all             192.168.10.3/32         trust

# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     replication     192.168.10.1/32         md5
host    replication     replication     192.168.10.2/32         md5
host    replication     replication     192.168.10.3/32         md5

Save and close the editor when you are finished.

Next, edit postgresql.conf with any of your favorite text editor:

nano postgresql.conf
Locate these parameters in the file, uncomment, and update their values accordingly:
archive_mode = on
archive_command = 'test ! -f /var/lib/edb/as12/backups/%f && cp %p /var/lib/edb/as12/backups/%f'
max_wal_senders = 10
wal_keep_segments = 50
wal_level = replica

Save and close the editor when you are finished.

 

Start EDB

Type below command to EDB on your primary server:
sudo systemctl restart edb-as-12.service
At this stage you need to create a replication role for your database:
createuser -U enterprisedb replication -P -c 5 --replication

Enter some strong password when prompt appears for password, and confirm password. 

At this stage, your primary server is ready, you can proceed with the next step to configure streaming replication on your standby servers. 

 

Prepare Standby Servers For Streaming Replication

Log in to your (edb_node2), using enterprisedb account and perform the following:
pg_basebackup -h 192.168.10.1 -D $PGDATA -U replication -v -P
cd $PGDATA
Edit postgresql.conf with any of your favorite text editor:
nano postgresql.conf
Locate the following parameters in the file, uncomment, and update their values accordingly.
primary_conninfo = 'host=192.168.10.1 port=5444 user=replication password=your_password'
promote_trigger_file = '/var/lib/edb/as12/data/edb.trigger'
recovery_target_timeline = 'latest'

Save and close the editor when you are done. 

Create a standby.signal file in order to tell the database that this is a standby server.

touch standby.signal

Start EDB 

Type below command to start EDB on your standby server (edb_node2):
sudo systemctl start edb-as-12.service
If all goes well, replication between the primary and standby server should have begun. You can check replication status from the primary server like below:
psql edb

\x
select * from pg_stat_replication;
Log in to your (edb_node3), using enterprisedb account, and perform the following:
pg_basebackup -h 192.168.10.1 -D $PGDATA -U replication -v -P
cd $PGDATA
Edit postgresql.conf file with any of your favorite text editor:
nano postgresql.conf
Locate the following parameters in the file, uncomment, and update their values accordingly.
primary_conninfo = 'host=192.168.10.1 port=5444 user=replication password=replication'
promote_trigger_file = '/var/lib/edb/as12/data/edb.trigger'
recovery_target_timeline = 'latest'

Save and close the editor when you are finished.

Create a standby.signal file in order to tell the database that this is a standby server.

touch standby.signal

Start EDB

Type below command to start EDB on your standby server (edb_node3):
sudo systemctl start edb-as-12.service

Verify Streaming Replication

From the primary server, connect to database, and check the replication status like below:
psql edb

\x
select * from pg_stat_replication;

In the output, you will see your both standby servers are in sync with your primary server, and replication is taking effect. 

From the standby servers, connect to database, and check the replication status like below:

psql edb

\x

select pg_is_wal_replay_paused();
Note - f means , recovery is running fine. t means it is stopped.
select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

select * from pg_stat_wal_receiver;
From the primary server, connect to database, and create a table:
psql edb

CREATE TABLE visitor_book (visitor_email text, vistor_id serial, date timestamp, message text);

INSERT INTO visitor_book (visitor_email, date, message) VALUES ('support@techsupportpk.com', current_date, 'This is a replication test.');
From the standby servers, connect to database, and query for all rows of the visitor_book:
psql edb

select * from visitor_book;

You should now see that the standby database has received the update from the primary.

At this stage, we have completed the streaming replication between primary and standby servers.

 

Configure Failover Manager on Primary Server

Log in to your primary server (edb_node1), using enterprisedb account and perform the following:
sudo ln -s /usr/edb/efm-4.4/bin/* /usr/bin/
We need to generate an encrypted password for enterprisedb account with the following command:
sudo efm encrypt efm

When it prompts for password, make sure you enter the same password you have for enterprisedb account. 

Copy encrypted value of the password, and save it somewhere for later use.

Type below command to make copy of these two sample configuration files:

sudo cp -p /etc/edb/efm-4.4/efm.properties.in /etc/edb/efm-4.4/efm.properties
sudo cp -p /etc/edb/efm-4.4/efm.nodes.in /etc/edb/efm-4.4/efm.nodes
Apply appropriate permission:
sudo chmod 666 /etc/edb/efm-4.4/efm.properties
sudo chmod 666 /etc/edb/efm-4.4/efm.nodes
Edit efm.properties file with any of your favorite text editor, and make the following changes:
sudo nano /etc/edb/efm-4.4/efm.properties
Locate the following parameters in the file, and update their values accordingly:
db.user=enterprisedb
db.password.encrypted=3b0e0c0a313be90f010059fd8b7fd5ea
db.port=5444
db.database=edb
db.service.owner=enterprisedb
db.service.name=edb-as-12.service
db.bin=/usr/edb/as12/bin
db.data.dir=/var/lib/edb/as12/data
db.config.dir=/var/lib/edb/as12/data
user.email=support@techsupportpk.com
bind.address=192.168.10.1:7800
is.witness=false
ping.server.ip=192.168.10.100
efm.loglevel=TRACE

Save and close the editor when you are finished. 

Edit efm.nodes file with any of your favorite text editor, and make the following changes:

sudo nano /etc/edb/efm-4.4/efm.nodes
Add your standby nodes like below:
192.168.10.2:7800 192.168.10.3:7800

Save and close the editor when you are finished. 

Start Failover Manager using the command below:

sudo systemctl start edb-efm-4.4.service
Add standby servers in allowed node list:
sudo efm allow-node efm 192.168.10.2
sudo efm allow-node efm 192.168.10.3
You can check EFM cluster status with command below:
sudo efm cluster-status efm
From the primary server, copy efm.properties file to your standby servers:
sudo scp -rp /etc/edb/efm-4.4/efm.properties edb_node2:/etc/edb/efm-4.4/
sudo scp -rp /etc/edb/efm-4.4/efm.properties edb_node3:/etc/edb/efm-4.4/

At this stage, Failover Manager configuration completed on primary server.

 

Configure Failover Manger on Standby Servers

Log in to your standby server (edb_node2), using enterprisedb account, and perform the following:
sudo ln -s /usr/edb/efm-4.4/bin/* /usr/bin/
sudo cp -p /etc/edb/efm-4.4/efm.nodes.in /etc/edb/efm-4.4/efm.nodes
Apply appropriate permission:
sudo chmod 666 /etc/edb/efm-4.4/efm.properties
sudo chmod 666 /etc/edb/efm-4.4/efm.nodes
Edit efm.properties file with any of your favorite text editor, and make the following changes:
sudo nano /etc/edb/efm-4.4/efm.properties
You only need to change bind.address value to your standby server IP. All other settings will remain same as primary:
bind.address=192.168.10.2:7800

Save and close the editor when you are finished.

Next, edit efm.nodes file with any of your favorite text editor:

sudo nano /etc/edb/efm-4.4/efm.nodes
Add your primary, and standby server IP:
192.168.10.1:7800 192.168.10.3:7800

Save and close the editor when you are finished. 

Start Failover Manager:

sudo systemctl start edb-efm-4.4.service
Check EFM cluster status:
sudo efm cluster-status efm
Log in to your standby server (edb_node3), using enterprisedb account, and perform the following:
sudo ln -s /usr/edb/efm-4.4/bin/* /usr/bin/
sudo cp -p /etc/edb/efm-4.4/efm.nodes.in /etc/edb/efm-4.4/efm.nodes
Apply appropriate permission:
sudo chmod 666 /etc/edb/efm-4.4/efm.properties
sudo chmod 666 /etc/edb/efm-4.4/efm.nodes
Edit efm.properties file with any of your favorite text editor, and make the following changes:
sudo nano /etc/edb/efm-4.4/efm.properties
You only need to change bind.address value to your standby server IP:
bind.address=192.168.10.2:7800

Save and close the editor when you are finished. 

Next, edit efm.nodes file with any of your favorite text editor:

sudo nano /etc/edb/efm-4.4/efm.nodes
Add your primary and standby server IP:
192.168.10.1:7800 192.168.10.2:7800

Save and close the editor when you are finished.

Start Failover Manager:

sudo systemctl start edb-efm-4.4.service
Check EFM cluster status:
sudo efm cluster-status efm
 

Run EFM Manual Failover

Manual failover should only be performed during a maintenance for your database cluster. If you do not have an up-to-date Standby database available, you will be prompted before continuing.
 
You can initiate manual failover from any node of a Failover Manager cluster to start a manual promotion of a Standby database to Primary database:
sudo efm promote efm -switchover -sourcenode 192.168.10.2
Where:
 
–switchover option, tells EFM to reconfigure the original Primary as a Standby.
–sourcenode option to specify the node from which the recovery settings will be copied to the primary. 
 
Check EFM cluster status:
sudo efm cluster-status efm
 

Conclusion: 

I hope this guide was helpful to set up a highly available EDB Postgres Advanced Server cluster in your environment.

No comments:

ads 728x90 B
Powered by Blogger.