Set Up a Highly Available EDB Postgres 12 Cluster on Ubuntu 18.04

This guide will walk you through the steps to set up a highly available EDB PostgreSQL Advanced Server cluster on Ubuntu 18.04. These steps can also be applied if you are running a later release of Ubuntu 20.04 or 22.04.

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.

EDB Failover Manager (EFM) is a high-availability module from EnterpriseDB similar to open-source (patroni) that enables a primary node to automatically failover to a standby node in the event of a software or hardware failure on the primary.

 

Prerequisites

To follow this tutorial along, you will need minimum of three (physical or virtual) machine installed with Ubuntu 18.04.

 

Log in to your Ubuntu and set the appropriate hostname on all three nodes:

sudo hostnamectl set-hostname edb_node1

Make sure you replace the highlighted text with yours.

 

Also, set the correct timezone on each node using the command below:

sudo timedatectl set-timezone Asia/Karachi

 

Add EDB Repository

You must have a registered user account with EDB to access its repository.

sudo su -c 'echo "deb [arch=amd64] https://apt.enterprisedb.com/$(lsb_release -cs)-edb/ $(lsb_release -cs) main" > /etc/apt/sources.list.d/edb-$(lsb_release -cs).list'

You should obtain your encrypted password from https://www.enterprisedb.com/accounts/profile 

Replace USERNAME and PASSWORD below with your username and password for the EDB repositories.

sudo su -c 'echo "machine apt.enterprisedb.com login USERNAME password PASSWORD" > /etc/apt/auth.conf.d/edb.conf'

Add support for secure APT repositories:

sudo apt -y install apt-transport-https

Add the EDB signing key:

sudo wget -q -O - https://apt.enterprisedb.com/edb-deb.gpg.key  | sudo apt-key add -

Update the repository meta data

sudo apt update

Install EDB Postgres Advanced Server version 12, and Failover Manager 4.4:

sudo apt -y install edb-as12-server edb-efm44

sudo ln -s /usr/lib/edb-as/12/bin/* /usr/bin/

Make sure you have repeated all of the above steps on each node before proceeding to next step.

 

Prepare Primary Database for Streaming Replication

Log in to your (edb_node1), and edit pg_hba.conf file with any of your favorite text editor

sudo nano /etc/edb-as/12/main/pg_hba.conf

Add your primary, and standby servers IP 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, create replication role in primary database:

sudo -u enterprisedb createuser -U enterprisedb replication -P -c 5 --replication

Set password for enterprisedb user in the database:

sudo -u enterprisedb psql edb -c "ALTER USER enterprisedb PASSWORD 'your_password';"

 

Prepare Standby Database for Streaming Replication

Log in to your standby server (edb_node2) in our case, and perform the following steps: 

sudo ln -s /usr/lib/edb-as/12/bin/* /usr/bin/

Stop EDB postgres:

sudo systemctl stop edb-as@12-main.service

Remove data directory:

sudo -u enterprisedb rm -rf /var/lib/edb-as/12/main

Create an empty data directory, and set appropriate permission:

sudo -u enterprisedb mkdir /var/lib/edb-as/12/main
sudo -u enterprisedb chmod 700 /var/lib/edb-as/12/main

Now that the data directory is empty on your standby server (edb_node2), you can perform a physical backup of the primary’s data files:

sudo -u enterprisedb pg_basebackup -h 192.168.10.1 -p 5444 -U replication -D /var/lib/edb-as/12/main/ -Fp -Xs -R

Edit pg_hba.conf on your standby server (edb_node2) with any of your favorite text editor:

sudo nano /etc/edb-as/12/main/pg_hba.conf

Add your primary, and standby servers IP 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.

 

Start EDB postgres on your standby server (edb_node2):

sudo systemctl start edb-as@12-main.service

Check replication status from your primary server:

sudo -u enterprisedb psql edb -c "SELECT client_addr, state FROM pg_stat_replication;"

You will see in the output, your standby server IP and replication state like below:

 

 

Next, log in to your standby server (edb_node3) in our case, and perform the following steps:

sudo ln -s /usr/lib/edb-as/12/bin/* /usr/bin/

Stop EDB postgres:

sudo systemctl stop edb-as@12-main.service

Remove data directory:

sudo -u enterprisedb rm -rf /var/lib/edb-as/12/main

Create an empty data directory, and set appropriate permission:

sudo -u enterprisedb mkdir /var/lib/edb-as/12/main

sudo -u enterprisedb chmod 700 /var/lib/edb-as/12/main

Now that the data directory is empty on your standby server (edb_node2), you can perform a physical backup of the primary’s data files:

sudo -u enterprisedb pg_basebackup -h 192.168.10.1 -p 5444 -U replication -D /var/lib/edb-as/12/main/ -Fp -Xs -R

Edit pg_hba.conf on your standby server (edb_node2) with any of your favorite text editor:

sudo nano /etc/edb-as/12/main/pg_hba.conf

Add your primary, and standby servers IP 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.

 

Start EDB postgres on your standby server (edb_node3):

sudo systemctl start edb-as@12-main.service

Check replication status from your primary server:

sudo -u enterprisedb psql edb -c "SELECT client_addr, state FROM pg_stat_replication;"

You will see in the output, your both standby servers IP and replication state like below:

 

You can also check the replication status from any of your standby servers like below:

sudo -u enterprisedb psql edb -c "select * from pg_stat_wal_receiver;"

You will see the output similar to like below:

 

 

From your primary server, create a table in edb database:

sudo -u enterprisedb psql edb -c "CREATE TABLE visitor_book (visitor_email text, vistor_id serial, date timestamp, message text);"
sudo -u enterprisedb psql edb -c "INSERT INTO visitor_book (visitor_email, date, message) VALUES ('support@techsupportpk.com', current_date, 'This is a replication test.');"

From any of your standby servers, query for all rows of the visitor_book:

sudo -u enterprisedb psql edb -c "select * from visitor_book;"

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

 

Now that your streaming replication is working, you can proceed to next step. 

Configure Failover Manager (EFM) on Primary Server

Log in to your primary server (edb_node1), and perform the following steps:

sudo ln -s /usr/edb/efm-4.4/bin/* /usr/bin/

You need to generate an encrypted password for enterprisedb account with the following command:

sudo efm encrypt efm

This will ask you to enter password, make sure you enter the same password you have for enterprisedb account in the database.

 

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

 

Type below command to make copy of these two 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, update their values accordingly:

db.user=enterprisedb
db.password.encrypted=931d33cd185ed33290dc2194363ddfc9
db.port=5444
db.database=edb
db.service.owner=enterprisedb
db.service.name=edb-as@12-main.service
db.bin=/usr/lib/edb-as/12/bin
db.data.dir=/var/lib/edb-as/12/main
db.config.dir=/etc/edb-as/12/main
user.email=support@techsupportpk.com
bind.address=192.168.10.1:7800
is.witness=false
ping.server.ip=192.168.10.100
virtual.ip=192.168.10.10
virtual.ip.interface=enp0s8
virtual.ip.prefix=24
virtual.ip.single=true
efm.loglevel=TRACE

The specified virtual IP address is assigned only to the primary node of the cluster. If you specify virtual.ip.single=true, the same VIP address is used on the new primary during a failover. Specify a value of false to provide a unique IP address for each node of the cluster.

 

Make sure you replace highlighted values with yours, save and close the editor when you are finished.

 

Start EFM:

sudo systemctl start edb-efm-4.4.service

Add standby servers in efm cluster:

sudo efm allow-node efm 192.168.10.2
sudo efm allow-node efm 192.168.10.3

At this stage, Failover Manager is ready on our primary server.

 

Configure Failover Manger on Standby Servers

Log in to your standby server (edb_node2), and perform the following:

sudo ln -s /usr/edb/efm-4.4/bin/* /usr/bin/

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:

sudo nano /etc/edb/efm-4.4/efm.properties

You only need to change bind.address value to your standby server's 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.

 

Add primary, and standby server in efm cluster:

sudo efm allow-node efm 192.168.10.1
sudo efm allow-node efm 192.168.10.3

Start EFM on your standby server (edb_node2):

sudo systemctl start edb-efm-4.4.service

Next, log in to your standby server (edb_node3), and perform the following:

sudo ln -s /usr/edb/efm-4.4/bin/* /usr/bin/

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:

sudo nano /etc/edb/efm-4.4/efm.properties

You only need to change bind.address value to your standby server's IP. All other settings will remain same as primary:

bind.address=192.168.10.3:7800

Save and close the editor when you are finished.

 

Add primary, and standby server in efm cluster:

sudo efm allow-node efm 192.168.10.1
sudo efm allow-node efm 192.168.10.2

Start EFM on your standby server (edb_node3):

sudo systemctl start edb-efm-4.4.service

Check EFM cluster status:

efm cluster-status efm

Output will look similar to like below:

 

As you can see, (192.168.10.1) is acting as primary, and VIP also belongs to it. 

 

For instance, if primary server goes down, efm will automatically promote next available standby node to primary.

 

 

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 primary or standby node using the command below:

sudo efm promote efm -switchover

–switchover option, tells EFM to reconfigure the original Primary as a Standby.

 

 

Check EFM cluster status:

efm cluster-status efm

You should see in the output, your primary server is reconfigured to standby, and one of the standby servers has taken over primary role.

 

Wrapping up

I hope this guide was helpful to set up a highly available EDB postgres cluster for your production use.