HA PEM using the S1 architecture with EFM and a Virtual IP v10.2
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:
5444for EPAS7800for EFMFor 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.conffile 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.conffile 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/32Run 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.conffile: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-256Restart 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
.pgpassfile 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.
Register the PEM agent. Specify the VIP as the PEM server host and enable alert and notification handling. For example:
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
pemdatabase as a superuser, providing the correct server and port for each.SELECT pem.register_pem_server(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
efmto 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.confto allow theefmdatabase 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.nodesfile 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.nodesfile:On all nodes, create the
efm.propertiesfile 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
efmdatabase user's password (as defined in Step 1 above) using theefmutility, 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.propertiesfile. 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.serverwith 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.witnessconfiguration parameter totrue:is.witness=true
On the primary node, enable and start the EFM service:
systemctl enable edb-efm-5.0 systemctl start edb-efm-5.0On 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.0Check 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-pemOpen the following ports on the firewall of all servers:
8443for 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.shscript 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.