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

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.2) 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.

Also refer to Deciding on an SSL model to determine how you will provide SSL certificates and keys prior to starting installation.

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 an EFM cluster

Before installing PEM, you should have a working EFM cluster on the backend hosts. This section provides instructions to deploy such a cluster. You do not have to follow these steps if you have your own processes to provision EFM clusters. However, if you plan to use sslutils you should ensure it is installed on your cluster before proceeding.

Initial package installation and Postgres configuration

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

  1. Install the following packages:

    dnf -qy module disable postgresql
dnf -y install epel-release
dnf config-manager --set-enabled crb
dnf -y install edb-as17-server edb-efm52

  2. 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

  3. 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

Optional: install sslutils

If you wish PEM to act as a certificate authority and automatically issue client certificates, you will need to configure sslutils. If you plan to provide your own certificates, then you may skip this step.

  1. Install the sslutils package on all backend hosts.

    dnf install edb-as17-server-sslutils

Set up the primary node for streaming replication

Perform these steps on the primary only.

  1. Create the replication role:

    CREATE ROLE repl REPLICATION LOGIN PASSWORD 'password';

    Give the password of your choice.

  2. 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.

  1. Add the following entries 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:

    host  replication  repl  172.16.161.200/32  scram-sha-256
host  replication  repl  172.16.161.201/32  scram-sha-256
host  replication  repl  172.16.161.202/32  scram-sha-256

  2. Restart the EPAS server.

    systemctl restart edb-as-17.service

Add a .pgpass file

Perform these steps on all non-witness backend nodes.

  1. Create the .pgpass file in the home directory of the enterprisedb user 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>

  2. Set the permissions on the file to restrict access

    chmod 600 ~/.pgpass

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.

  1. Stop the service for EPAS on all the standby nodes:

    systemctl stop edb-as-17.service

  2. Remove the data directory of the database server on all the standby nodes:

    su - enterprisedb
    
rm -rf /var/lib/edb/as17/data/*

  3. 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.16.161.200 \
    -D /var/lib/edb/as17/data \
    -U repl \
    -v -P -Fp -R -p 5444\
    "

  4. Start the EPAS database server on each of the standby nodes:

    systemctl start edb-as-17

Set up EFM to manage failover

Perform the following steps to set up EFM:

  1. 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.

  2. On the primary, add entries in pg_hba.conf to allow the efm database user to connect to the database server from all nodes. These entries will be replicated to the standbys.

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

  3. Reload the configurations on all the database servers.

    SELECT pg_reload_conf();

  4. On all nodes, create an efm.nodes file using the sample file (/etc/edb/efm-5.2/efm.nodes.in), and give read-write access to the EFM OS user:

    cp /etc/edb/efm-5.2/efm.nodes.in /etc/edb/efm-5.2/efm.nodes
chown efm:efm /etc/edb/efm-5.2/efm.nodes
chmod 600 /etc/edb/efm-5.2/efm.nodes

  5. On the standby nodes, add the IP address and EFM port of the primary node in the /etc/edb/efm-5.2/efm.nodes file:

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

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

  7. 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.2/bin/efm encrypt efm --from-env

  8. 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

  9. On the witness node, set the value of the is.witness configuration parameter to true:

        is.witness=true

  10. On the primary node, enable and start the EFM service:

    systemctl enable edb-efm-5.2
systemctl start edb-efm-5.2

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

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

  12. Enable and start the EFM service on the standby nodes and the EFM witness node:

    systemctl enable edb-efm-5.2
systemctl start edb-efm-5.2

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

    /usr/edb/efm-5.2/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.

Prepare the EFM cluster for PEM installation

Before installing PEM in HA mode, we must ensure that the cluster meets two requirements:

  • SSL is configured so that the cert auth method can be used for connections from PEM agents to the PEM backend database.
  • HBA rules are in place to allow agents and web application instances to connect to the PEM backend.

Prepare SSL files

Follow the instructions in Generating SSL files for HA PEM backend hosts.

Modify the EFM cluster to add SSL and HBA rules

Perform the following actions on each PEM backend node (excluding the witness).

  1. Modify postgresql.conf to enable SSL and set the paths to the SSL files created above.

    ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

    If you have a CRL, also set:

    ssl_crl_file = 'root.crl'

  2. Modify pg_hba.conf to add the rules required by PEM. Add this block at the top of the file, above any existing content, adjusting the IP addresses to match your cluster.

    # Allow users of the web interface to connect to pem and postgres databases
hostssl pem      +pem_user   127.0.0.1/32           scram-sha-256
hostssl postgres +pem_user   127.0.0.1/32           scram-sha-256
hostssl pem      +pem_user   172.16.161.211/32      scram-sha-256
hostssl postgres +pem_user   172.16.161.211/32      scram-sha-256
hostssl pem      +pem_user   172.16.161.212/32      scram-sha-256
hostssl postgres +pem_user   172.16.161.212/32      scram-sha-256
hostssl pem      +pem_user   172.16.161.213/32      scram-sha-256
hostssl postgres +pem_user   172.16.161.213/32      scram-sha-256

# Allow the PEM backend server agents to register and connect to send data to PEM server
hostssl pem      +pem_agent  127.0.0.1/32           cert
hostssl pem      +pem_admin  172.16.161.200/32      scram-sha-256
hostssl pem      +pem_agent  172.16.161.200/32      cert
hostssl pem      +pem_admin  172.16.161.201/32      scram-sha-256
hostssl pem      +pem_agent  172.16.161.201/32      cert
hostssl pem      +pem_admin  172.16.161.202/32      scram-sha-256
hostssl pem      +pem_agent  172.16.161.202/32      cert

# Allow the PEM web app server agents to register and connect to send data to PEM server
hostssl pem      +pem_admin   172.16.161.211/32    scram-sha-256
hostssl pem      +pem_agent   172.16.161.211/32    cert
hostssl pem      +pem_admin   172.16.161.212/32    scram-sha-256
hostssl pem      +pem_agent   172.16.161.212/32    cert
hostssl pem      +pem_admin   172.16.161.213/32    scram-sha-256
hostssl pem      +pem_agent   172.16.161.213/32    cert

# Allow our superuser to connect to perform cluster configuration
hostssl pem,postgres      pemsuper   172.16.161.200/32    scram-sha-256
hostssl pem,postgres      pemsuper   172.16.161.201/32    scram-sha-256
hostssl pem,postgres      pemsuper   172.16.161.202/32    scram-sha-256
hostssl pem,postgres      pemsuper   172.16.161.211/32    scram-sha-256
hostssl pem,postgres      pemsuper   172.16.161.212/32    scram-sha-256
hostssl pem,postgres      pemsuper   172.16.161.213/32    scram-sha-256

# Allow monitoring agents to connect to PEM
# Specify a subnet that includes all your monitored servers
# Duplicate these lines for multiple subnets
hostssl pem      +pem_user   0.0.0.0/0               scram-sha-256
hostssl pem      +pem_agent  0.0.0.0/0               cert

  3. If using a common agent user, add one additional line to allow the first agent to connect during PEM installation. This line can be removed later if required.

    hostssl pem      common_agent_user  127.0.0.1/32           cert

  4. Modify the HBA rules added in the previous sections (for replication and EFM) to hostssl instead of host to enforce SSL for all connections.

Restart the cluster

Note
By default, EFM will trigger a failover if the database service stops.
During this rolling restart, stop the EFM agent before restarting Postgres to prevent an unintended failover.

Perform a rolling restart of the cluster. On each PEM backend host, starting with the standbys and concluding with the primary, do the following:

  1. Stop the EFM agent service:

    systemctl stop edb-efm-5.2

  2. Restart Postgres:

    systemctl restart edb-as-17

  3. Start the EFM agent service:

    systemctl start edb-efm-5.2

Prepare agent users and certificates

If you are using your own SSL certificates, follow these steps to prepare for PEM deployment. If you are using sslutils, you may skip this section.

Install PEM on the primary

Perform the following steps on the primary host only.

  1. Install the PEM package.

    dnf install edb-pem --allowerasing

  2. Create a superuser that can login using a password. This is the user we will use to install PEM. It will also become the agent-server binding user for the agents running on PEM backend hosts.

    su - enterprisedb -c "psql -c \"create role pemsuper login superuser password 'your-password-here';\""

  3. To configure the PEM server, run the configuration script using the Database option (Option 3) and set the host to your Virtual IP (VIP).

    To ensure the system is correctly set up for High Availability (HA), you must include the following flags:

    • --replication-solution efm & --replication-role primary: These flags force the script into HA mode, ensuring compatibility with your failover manager.

    • --skip-db-certificate: This prevents the script from attempting to automatically configure SSL, as you are managing certificates manually in an HA environment.

    You will be prompted for various additional details. For configuration options see, Configuring the PEM server on Linux.

  4. 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.

Install PEM on the standbys

Perform the following steps on the standby hosts only.

  1. Install the PEM package.

    dnf install edb-pem --allowerasing

  2. To configure the PEM server, run the configuration script using the Database option (Option 3) and set the host to your Virtual IP (VIP).

    To correctly enable High Availability (HA) mode, you must include the following specific flags:

    • --replication-solution efm: Integrates the configuration with Enterprise Failover Manager (EFM).

    • --replication-role standby: Designates this node's initial role within the HA cluster.

    You will be prompted for various additional details. For configuration options see, Configuring the PEM server on Linux.

Deploy the PEM Web Application

Perform the following steps on all web application hosts.

  1. Install the PEM package and the EPAS client binaries:

    dnf install edb-pem edb-as17-server-client

  2. 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

  3. Configure the PEM web application. Specify the VIP as the PEM server address.

    You will be prompted for various additional details. For configuration options see, Configuring the PEM server on Linux.

  4. Run the configure-selinux.sh script to configure the SELinux policy for PEM.

    USE_NGINX=1 /usr/edb/pem/bin/configure-selinux.sh

  5. You can now login to any of the web application instances using the pemsuper superuser we created earlier.

  6. 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

Next →

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