This guide will help you to set up a 3-node highly available EDB cluster on CentOS 7.
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 yum -y install epel-release
sudo yum -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 yum -y update
sudo yum -y install java
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 yum -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 restart 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: