How to configure PostgreSQL DB in RHEL High Availability Cluster?

Resolution

1. Before the postgresql DB setup in pacemaker cluster refer the article the Supported versions of postgresql in RHEL Cluster setup

2. Open the firewall ports for pgsql services on all cluster nodes:

   • Run as root user on all cluster nodes:

   $ firewall-cmd --permanent --add-service=postgresql
   $ firewall-cmd --reload
   

3. For DB setup refer PostgreSQL Configuration. These are the steps that are required:

• Install postgresql-server and ensure resource-agents packages are installed on each node in the cluster.

  • For RHEL 7 for each node in the cluster:

  $ yum install postgresql-server resource-agents
  

  • For RHEL 8 and higher for each node in the cluster:

  $ dnf install postgresql-server resource-agents
  

• On the first node in the cluster only follow the below steps:

  • Login as postgres user and initialize the database:

  # Login as postgres user
  $ su - postgres
  
  # Create directory for use with replication.
  $ mkdir /var/lib/pgsql/pg_archive
  
  # Change to data directory and initialize database.
  $ cd /var/lib/pgsql/data
  $ initdb
  

  • Example output:

  bash-4.2$ initdb
  The files belonging to this database system will be owned by user "postgres".
  This user must also own the server process.
  
  The database cluster will be initialized with locale "en_US.UTF-8".
  The default database encoding has accordingly been set to "UTF8".
  The default text search configuration will be set to "english".
  
  fixing permissions on existing directory /var/lib/pgsql/data ... ok
  creating subdirectories ... ok
  selecting default max_connections ... 100
  selecting default shared_buffers ... 32MB
  creating configuration files ... ok
  creating template1 database in /var/lib/pgsql/data/base/1 ... ok
  initializing pg_authid ... ok
  initializing dependencies ... ok
  creating system views ... ok
  loading system objects' descriptions ... ok
  creating collations ... ok
  creating conversions ... ok
  creating dictionaries ... ok
  setting privileges on built-in objects ... ok
  creating information schema ... ok
  loading PL/pgSQL server-side language ... ok
  vacuuming database template1 ... ok
  copying template1 to template0 ... ok
  copying template1 to postgres ... ok
  
  WARNING: enabling "trust" authentication for local connections
  You can change this by editing pg_hba.conf or using the option -A, or
  --auth-local and --auth-host, the next time you run initdb.
  
  Success. You can now start the database server using:
  
      postgres -D /var/lib/pgsql/data
  or
      pg_ctl -D /var/lib/pgsql/data -l logfile start
  

• On the first node only, as postgres user modify the postgresql.conf file. The steps below are used for creating a replicated database instance for use with promotable pgsql pacemaker resource:

  • Below are the settings which should be included, and these can be appended to the bottom of the configuration file:

  $ vim /var/lib/pgsql/data/postgresql.conf
  ------------------>8-------------------
  #------------------------------------------------------------------------------
  # CUSTOMIZED OPTIONS
  #------------------------------------------------------------------------------
  
  # Add settings for extensions here
  listen_addresses = '*' #<--- Add from here down
  wal_level = hot_standby
  synchronous_commit = on
  archive_mode = on
  archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'
  max_wal_senders=5
  hot_standby = on
  restart_after_crash = off
  wal_receiver_status_interval = 2
  max_standby_streaming_delay = -1
  max_standby_archive_delay = -1
  synchronous_commit = on
  restart_after_crash = off
  hot_standby_feedback = on
  

  • Additional notes and considerations for this postgresql.conf modification:
    • If there is "synchronous_standby_names" parameter, please delete it.
    • Fixed IP cannot be written in listen_address.
    • Replication_timeout is detection time for the replication cuts it, and wal_receiver_status_interval is an interval when HS tries connecting to PRI. To shorten detection, you should set this value to small.
    • When using rep_mode=sync, RA adds "include" into postgresql.conf to switch replication mode. If you want to switch to rep_mode=async from sync, you need to delete it manually.

• On the first node only, as postgres user modify the pg_hba.conf file, to control who has access to db instance:

  • Important note: This basic configuration documentation does not account for security considerations. For this step you can modify these listings as needed by your security and networking, but access must be passwordless for usage with the cluster.

  • This should be set so that the local node and cluster nodes have "trust" permissions:

  $ vim /var/lib/pgsql/data/pg_hba.conf
  ------------------>8-------------------
  # This first 3 lines should already be present by default.
  # If not seen add these:
  host    all             all     127.0.0.1/32        trust
  local   replication     all                         trust
  local   all             all                         trust
  
  # These will need to be added for your cluster node's ip network. These must
  # must be specified as IP ranges and setting specific IP's will trigger an
  # error:
  host    all             all     192.168.0.0/24      trust
  host    replication     all     192.168.0.0/24      trust
  

  • As postgres user, start PostgreSQL on node 01 only:

  $ pg_ctl -D /var/lib/pgsql/data/ start
  

• On the remaining nodes the cluster only as postgres user, perform the below steps to copy the existing configuration from node 01 to the other nodes in the cluster. These steps should not be performed from node 01 ( primary node used in previous steps ) and should be ran form all other nodes:

  • Login as postgres user and remove any existing ( default ) configs:

  # Login as postgres user
  $ su - postgres
  $ cd /var/lib/pgsql/data
  
  # Remove existing default configurations:
  $ rm -rf /var/lib/pgsql/data/*
  
  # Create directory for use with replication.
  $ mkdir /var/lib/pgsql/pg_archive
  

  • Restore node 01's backup and configuration to remaining nodes. The ip used in example is the node 01's cluster ip, but a separate network can be used if one is configured, and pg_hba.conf has the correct access allowed:

  $ pg_basebackup -h 192.168.0.10 -U postgres -D /var/lib/pgsql/data -X stream -P
  20048/20048 kB (100%), 1/1 tablespace
  

  • Next we need to configure the recovery options that we will use. Please note that these steps will differ slightly depending on what version of Postgres is running:

    • Postgres 11 and lower: Create the /var/lib/pgsql/data/recovery.conf file on each of the remaining nodes. For the primary_conninfo line, the host= option for should be set to node 01's replication IP, and the application_name can be set to anything but should differ for each node:

    $ vim /var/lib/pgsql/data/recovery.conf
    ------------------>8-------------------
    standby_mode = 'on'
    
    primary_conninfo = 'host=192.168.0.10 port=5432 user=postgres application_name=node2'
    
    restore_command = 'cp /var/lib/pgsql/pg_archive/%f %p'
    
    recovery_target_timeline = 'latest'
    

    • Postgres 12 and higher: Usage of the recovery.conf file [is deprecated](PostgreSQL: Documentation: 16: O.1. recovery.conf file merged into postgresql.conf
      https://www.postgresql.org/docs/current/recovery-config.html) and most options need to be added to postgresql.conf instead. For the primary_conninfo line, the host= option for should be set to node 01's replication IP, and the application_name can be set to anything but should differ for each node:

    $ vim /var/lib/pgsql/data/postgresql.conf
    ------------------>8-------------------
    primary_conninfo = 'host=192.168.0.10 port=5432 user=postgres application_name=node2'
    
    restore_command = 'cp /var/lib/pgsql/pg_archive/%f %p'
    
    recovery_target_timeline = 'latest'
    

    • Postgres 12 and higher: Usage of the standby_mode option is also deprecated. Instead of adding this option in the postgresql.conf we should simply create the standby.signal file on each of the non-primary nodes:

    $ touch /var/lib/pgsql/data/standby.signal
    

• Once the above is configured, you can start the database instance on all the remaining cluster nodes ( start DB on non-primary nodes ):

  • As postgres user, start PostgreSQL on remaining non-primary nodes:

  $ pg_ctl -D /var/lib/pgsql/data/ start
  

• As postgres user, from the first node in the cluster confirm that the database is running and replication is up:

  • Login and confirm database and replication status:

  $ su - postgres
  $ psql -c "select client_addr,sync_state from pg_stat_replication;"
  

• If no errors are observed, go ahead and stop the DB on all nodes, ahead of cluster configuration:

  • Run from all nodes as postgres user to stop the database and replication:

  $ su - postgres
  $ pg_ctl -D /var/lib/pgsql/data stop
  $ exit
  

4. The remaining steps assume that a pacemaker cluster has already been installed and setup. If this step has not been completed, please follow the appropriate documentation to create the pacemaker cluster:

   • Chapter 1. Creating a Red Hat High-Availability Cluster with Pacemaker | Red Hat Enterprise Linux 7

   • Chapter 4. Creating a Red Hat High-Availability cluster with Pacemaker | Red Hat Enterprise Linux 8

   • Chapter 2. Getting started with Pacemaker | Red Hat Enterprise Linux 9

5. Add the virtual IP resource used to connect to the primary database:

   • Run from any one node in the cluster to create the IPaddr2 cluster resource:

    # pcs resource create pgvip ocf:heartbeat:IPaddr2 ip=<database IP> nic=<network interface> cidr_netmask=<network prefix>`
   
    # Example:
    $ pcs resource create pgvip IPaddr2 ip=192.168.0.225 nic=enp1s0 cidr_netmask=24
   

6. Create the resources for the cluster to manage the PostgreSQL service:

   • Run on any one node in the cluster to create resource. We will see an initial failure after creation, and until we convert this to a promotable resource:

   # pcs resource create postgresql \
   rep_mode=sync \
   primary_conninfo_opt="keepalives_idle=60 keepalives_interval=5 keepalives_count=5" \
   node_list="<NODE1> <NODE2> ..." \
   restore_command='cp /var/lib/pgsql/pg_archive/%f "%p"' \
   master_ip=<database IP> \
   restart_on_promote=true \
   
   # Example:
   $ pcs resource create postgresql pgsql \
   rep_mode=sync \
   primary_conninfo_opt="keepalives_idle=60 keepalives_interval=5 keepalives_count=5" \
   node_list="rhel8-node1 rhel8-node2" \
   restore_command='cp /var/lib/pgsql/pg_archive/%f "%p"' \
   master_ip=192.168.0.225 \
   restart_on_promote=true
   

7. Convert the postgresql resource into a promotable resource:

   • On RHEL 7 use the "master" command for this:

   # pcs resource master <custom master resource name> <pgsql resource id> master-max=1 master-node-max=1 notify=true
   
   # Example:
   $ pcs resource master master-postgresql postgresql master-max=1 master-node-max=1 notify=true
   

   • On RHEL 8 and higher use the "master" command for this:

   # pcs resource promotable <resource id | group id> master-max=1 master-node-max=1 notify=true
   
   # Example:
   $ pcs resource promotable postgresql master-max=1 master-node-max=1 notify=true
   

8. Create constraints so the virtual IP ( pgvip ) resource only runs with the promoted pgsql instance ( master-postgresql for RHEL 7 or postgresql-clone RHEL 8+ ):

   • Run from any one node in RHEL 7 cluster, to add the constraints from VIP resource to promoted pgsql resource:

   $ pcs constraint colocation add pgvip with Master master-postgresql INFINITY
   $ pcs constraint order promote master-postgresql then start pgvip symmetrical=false score=INFINITY
   $ pcs constraint order demote master-postgresql then stop  pgvip symmetrical=false score=0
   

   • Run from any one node in RHEL 8 cluster, to add the constraints from VIP resource to promoted pgsql resource:

   $ pcs constraint colocation add pgvip with Promoted postgresql-clone INFINITY
   $ pcs constraint order promote postgresql-clone then start pgvip symmetrical=false score=INFINITY
   $ pcs constraint order demote  postgresql-clone then stop  pgvip symmetrical=false score=0
   

9. Verify PostgreSQL is started. This should show that the database is running as master on one node, and as slave on the other node:

   • Expected output should show one promoted node, and the remaining resources as slaves. The virtual IP resource should be running with the promoted node:

   $ pcs status --full
   ------------------>8-------------------
    * pgvip    (ocf::heartbeat:IPaddr2):    Started rhel8-node1
    * Clone Set: postgresql-clone [postgresql] (promotable):
      * Masters: [ rhel8-node1 ]
      * Slaves: [ rhel8-node2 ]