HA PEM using the S1 architecture with EFM and a Virtual IP v10.2

Suggest edits

This page provides detailed instructions to install and configure a High Availability (HA) PEM deployment according to reference architecture S1.

This example uses EDB Failover Manager (EFM 5.0) for cluster management, EDB Postgres Advanced Server (EPAS 17) as the PEM backend database and Virtual IP (VIP) as the mechanism for routing traffic to the primary on RHEL like systems.

Please see High Availability Patterns for PEM Deployment to understand other options.

Witness nodes vs standby nodes

In this example we configured a primary, two standbys, and a witness node. In reality, you only need a witness node if you have only two data nodes. If you are configuring three or more data nodes (e.g. a primary and two standbys), you may omit the steps pertaining to the witness node. If you are configuring only two data nodes, include the steps pertaining to the witness node.

The examples that follow use these IP addresses:

172.16.161.200 - PEM Backend Primary
172.16.161.201 - PEM Backend Standby 1
172.16.161.202 - PEM Backend Standby 2
172.16.161.203 - EFM Backend Witness Node
172.16.161.211 - PEM Web Application 1
172.16.161.212 - PEM Web Application 2
172.16.161.213 - PEM Web Application 3
172.16.161.245 - PEM VIP (used by agents and users to connect)

Deploying the PEM backend

Initial package installation and Postgres configuration

Perform the following steps on all backend nodes unless stated otherwise.

Install the following packages:
- EDB Postgres Advanced Server (backend database for PEM Server)
- sslutils (see Prerequisites in PEM server installation)
- PEM Server
- EDB Failover Manager
```
dnf -qy module disable postgresql dnf -y install epel-release dnf config-manager --set-enabled crb dnf -y install edb-as17-server edb-pem edb-as17-server-sslutils edb-efm50
```

Initialize a Postgres database and start the service.

PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as17/bin/edb-as-17-setup initdb systemctl start edb-as-17 systemctl enable edb-as-17

Open the following ports on the firewall:
- 5444 for EPAS
- 7800 for EFM
  For example:
```
firewall-cmd --zone=public --add-port=5444/tcp --permanent firewall-cmd --zone=public --add-port=7800/tcp --permanent firewall-cmd --reload 
```
User and access configuration on the primary
Perform the following steps on the primary.

Create a superuser that can login using a password.

su - enterprisedb -c psql edb -c 'create role pemserver login superuser password your-password-here;'

Add the following line to the the pg_hba.conf file to permit the new user to connect from any of the server IPs. You may adjust the size of the subnet as appropriate to you network, but it must include all the PEM backend and web application nodes.
```
hostssl all pemserver 172.16.161.1/24 scram-sha-256
```
Add the following line to the the pg_hba.conf file to permit other PEM users to connect to the PEM backend through the web application. You may adjust the size of the subnet as appropriate to you network, but it must include all the PEM web application nodes.
```
hostssl all +pem_user 172.16.161.1/24 scram-sha-256
```
Restart the Postgres server.
```
systemctl restart edb-as-17
```

Configure PEM on the primary backend node

Configure the PEM database installation on the primary backend server only:

Manually assign the VIP to the primary. For example:
```
/usr/edb/efm-5.0/bin/efm_address add4 eth0 172.16.161.245/32
```
Run the PEM configuration script, specifying the VIP as the host and option 3 (Database):
```
/usr/edb/pem/bin/configure-pem-server.sh -t 3 -ho 172.16.161.245
```
You will be prompted for various additional details. For configuration options see, Configuring the PEM server on Linux.
Optionally, to synchronize PEM web application user preferences between instances, configure central storage of user preferences. At this stage, you can only complete the backend configuration. We will configure the web application later.

Copy the configuration record to the standbys

Copy the file /usr/edb/pem/share/.install-config from the primary to all standbys. This ensures you will be able to upgrade PEM from whichever node is the current primary in future.

Set up the primary node for streaming replication

Create the replication role:

psql -h 172.16.161.200 -p 5444 -U enterprisedb edb -c “CREATE ROLE repl REPLICATION LOGIN PASSWORD 'password'”;

Give the password of your choice.

Configure the following in the postgresql.conf file:
```
wal_level = replica max_wal_senders = 10 wal_keep_size = 500 max_replication_slots = 10
```
For more information on configuring parameters for streaming replication, see the PostgreSQL documentation.

Add the following entry in the host-based authentication (/var/lib/edb/as17/data/pg_hba.conf) file to allow the replication user to connect from all the standbys:
```
hostssl replication repl 172.16.161.201/24 scram-sha-256
```
Restart the EPAS server.
```
systemctl restart edb-as-17.service
```

Set up the standby nodes for streaming replication

Use the pg_basebackup utility to create replicas of the PEM backend database server on the standby servers.

Stop the service for EPAS on all the standby nodes:
```
systemctl stop edb-as-17.service
```
Remove the data directory of the database server on all the standby nodes:
```
su - enterprisedb rm -rf /var/lib/edb/as17/data/*
```
Create the .pgpass file in the home directory of the enterprisedb user on all the standby nodes and add the following content. Replace <password> with the password of the replication user created previously.
```
172.16.161.200:5444:replication:repl:<password> 172.16.161.201:5444:replication:repl:<password> 172.16.161.202:5444:replication:repl:<password>
```
Set the permissions on the file to restrict access
```
chmod 600 ~/.pgpass
```

Take a backup of the primary node on each of the standby nodes using pg_basebackup:

su - enterprisedb -c "\ /usr/edb/as17/bin/pg_basebackup \ -h 172.31.6.186 \ -D /var/lib/edb/as17/data \ -U repl \ -v -P -Fp -R -p 5444\ "

Start the EPAS database server on each of the standby nodes:
```
systemctl start edb-as-17
```

Register agents and servers on the standbys

On each standby, perform the following steps.

export PEM_SERVER_PASSWORD=password /usr/edb/pem/agent/bin/pemworker --register-agent \ --pem-server 172.17.0.12 \ --pem-user pemserver \ --pem-port 5444 \ -o alert_threads=1 \ --enable-snmp true \ --enable-webhook true \ --enable-smtp true \ --max-webhook-retries 3

Enable and start the PEM agent service:

systemctl enable --now pem-agent

See Registering a PEM Agent for more information.

Register the Postgres instance with PEM. The following command means the PEM web application will use the server's external IP when making a client connection to the database, but the PEM Agent will use the loopback interface to connect locally.
```
export PEM_SERVER_PASSWORD=password /usr/edb/pem/agent/bin/pemworker --register-server \ --pem-user pemserver \ --server-addr 172.16.161.201 \ --server-port 5444 \ --server-database edb \ --server-user pemserver \ --server-service-name edb-as-17 \ --asb-host-name localhost 
```
See Registering a Postgres Server for more information
Execute the following SQL on the pem database as a superuser, providing the correct server and port for each.
```
SELECT pem.register_pem_server(id) FROM pem.server WHERE server='172.16.161.201' and port=5444;
```
Info
In older versions of PEM, the PEM server and its local agent had to have ID 1. This is no longer the case from PEM 10.1. Instead this SQL flags this server and agent as belonging to a PEM deployment, which in turn enables important system jobs such as purging expired data when this server is the primary.

Set up EFM to manage failover

Perform the following steps to set up EFM:

On the primary, create a database user efm to connect to the database servers. Grant execute privileges on the functions related to WAL logs, and monitoring privileges, to the user. As a superuser:

CREATE ROLE efm LOGIN PASSWORD 'password'; -- Give privilege to 'efm' user to connect to a database GRANT CONNECT ON DATABASE edb TO efm; -- Give privilege to 'efm' user to do backup operations GRANT EXECUTE ON FUNCTION pg_current_wal_lsn() TO efm; GRANT EXECUTE ON FUNCTION pg_last_wal_replay_lsn() TO efm; GRANT EXECUTE ON FUNCTION pg_wal_replay_resume() TO efm; GRANT EXECUTE ON FUNCTION pg_wal_replay_pause() TO efm; GRANT EXECUTE ON FUNCTION pg_reload_conf() TO efm; -- Grant monitoring privilege to the 'efm' user GRANT pg_monitor TO efm;

This change will be replicated to the standbys.

On all nodes, add entries in pg_hba.conf to allow the efm database user to connect to the database server from all nodes on all the hosts.

hostssl edb efm 172.16.161.200/32 scram-sha-256 hostssl edb efm 172.16.161.201/32 scram-sha-256 hostssl edb efm 172.16.161.202/32 scram-sha-256 hostssl edb efm 172.16.161.203/32 scram-sha-256

Reload the configurations on all the database servers.
```
SELECT pg_reload_conf();
```
On all nodes, create an efm.nodes file using the sample file (/etc/edb/efm-5.0/efm.nodes.in), and give read-write access to the EFM OS user:
```
cp /etc/edb/efm-5.0/efm.nodes.in /etc/edb/efm-5.0/efm.nodes chown efm:efm /etc/edb/efm-5.0/efm.nodes chmod 600 /etc/edb/efm-5.0/efm.nodes
```
On the standby nodes, add the IP address and EFM port of the primary node in the /etc/edb/efm-5.0/efm.nodes file:

On all nodes, create the efm.properties file using the sample file (/etc/edb/efm-5.0/efm.properties.in). Grant read access to all users:

cp /etc/edb/efm-5.0/efm.properties.in /etc/edb/efm-5.0/efm.properties chown efm:efm /etc/edb/efm-5.0/efm.properties chmod a+r /etc/edb/efm-5.0/efm.properties

On any node, encrypt the efm database user's password (as defined in Step 1 above) using the efm utility, make a note of the output for the next step.
```
export EFMPASS=password /usr/edb/efm-5.0/bin/efm encrypt efm --from-env
```

On all nodes, edit the following parameters in the efm.properties file. Replace <encrypted-password> with the output of the previous step. Replace <ip-addr> with the IP address of each node.

Note

If your hosts are not connected to the internet, replace the value of ping.server with the address of a reliable server accessible on your network that will respond to pings.

For more detail on EFM configuration please refer to the documentation.

db.user=efm db.password.encrypted=<encrypted-password> db.port=5444 db.database=edb db.service.owner=enterprisedb db.service.name=edb-as-17 db.bin=/usr/edb/as17/bin db.data.dir=/var/lib/edb/as17/data jdbc.sslmode=require user.email=username@example.com from.email=node1@efm-pem notification.text.prefix=[PEM/EFM] bind.address=<ip-addr>:7800 is.witness=false encrypt.agent.messages=true stop.isolated.primary=true stop.failed.primary=true primary.shutdown.as.failure=false ping.server.ip=8.8.8.8 # VIP configuration virtual.ip=172.16.161.245 virtual.ip.interface=ens33 virtual.ip.prefix=24 virtual.ip.single=true check.vip.before.promotion=true

On the witness node, set the value of the is.witness configuration parameter to true:
```
 is.witness=true
```
On the primary node, enable and start the EFM service:
```
systemctl enable edb-efm-5.0 systemctl start edb-efm-5.0
```

On the primary node, allow the standbys to join the cluster:

/usr/edb/efm-5.0/bin/efm allow-node efm 172.16.161.201 /usr/edb/efm-5.0/bin/efm allow-node efm 172.16.161.202 /usr/edb/efm-5.0/bin/efm allow-node efm 172.16.161.203

Enable and start the EFM service on the standby nodes and the EFM witness node:
```
systemctl enable edb-efm-5.0 systemctl start edb-efm-5.0
```

Check the EFM cluster status by running the following command on any node.

/usr/edb/efm-5.0/bin/efm cluster-status efm

The output should look like this:

Cluster Status: efm Agent Type Address DB VIP ---------------------------------------------------------------- Primary 172.16.161.200 UP 172.16.161.245* Standby 172.16.161.201 UP 172.16.161.245 Standby 172.16.161.202 UP 172.16.161.245 Witness 172.16.161.203 N/A 172.16.161.245 Allowed node host list: 172.16.161.200 172.16.161.201 172.16.161.202 172.16.161.203 Membership coordinator: 172.16.161.200 Standby priority host list: 172.16.161.201 172.16.161.202 Promote Status: DB Type Address WAL Received LSN WAL Replayed LSN Info --------------------------------------------------------------------------- Primary 172.16.161.200 0/F7A3808 Standby 172.16.161.201 0/F7A3808 0/F7A3808 Standby 172.16.161.202 0/F7A3808 0/F7A3808 Standby database(s) in sync with primary. It is safe to promote.

This status confirms that EFM is set up successfully and managing the failover for the PEM server.

In case of failover, any of the standbys are promoted as the primary node, and PEM agents connect to the new primary node. You can replace the failed primary node with a new standby using the procedure above.

Deploy the PEM Web Application

Perform the following steps on all web application hosts.

Install the PEM package:
```
dnf install edb-pem
```
Open the following ports on the firewall of all servers:
- 8443 for PEM Server (HTTPS)
For example:
```
firewall-cmd --zone=public --add-port=8443/tcp --permanent firewall-cmd --reload 
```
Configure the PEM web application. Select the VIP as the PEM server address.
```
/usr/edb/pem/bin/configure-pem-server.sh -t 2 -ho 172.16.161.245
```
You will be prompted for various additional details. For configuration options see, Configuring the PEM server on Linux.
Run the configure-selinux.sh script to configure the SELinux policy for PEM.
```
USE_NGINX=1 /usr/edb/pem/bin/configure-selinux.sh
```
If you chose to synchronize PEM web application user preferences between instances, complete the setup now by configuring each web application instance to use the backend for user settings.

← Prev

HA PEM using the C1 architecture with EFM and a Virtual IP

↑ Up

Installing Postgres Enterprise Manager in HA Patterns

Storing user settings in the backend, accessible to all web application instances