Enable HA for a Ranger Postgres database

Ranger supports high availability enabled databases when deployed with Postgres.

To support high availability (HA) of a Postgres Ranger database, integrate HAProxy (a third-party load balancer product) into your environment. HAProxy will handle the load balancing. Replication Manager (repmgr) handles failover and recovery of the database.

Ranger jdbc URL must point to a load balancer URL.

Example JDBC URL to connect to load balancer:

SSL:
jdbc:postgresql://<lb_hostname>:<lb_port>/ranger1?sslmode=verify-ca&sslrootcert=/cdep/pgssl/server.crt

Non-SSL:

jdbc:postgresql://<lb_hostname>:<lb_port>/ranger1
where:
Lb_hostname = Load balancer hostname
For example: mvnssl-sync-1.mvnssl-sync.root.hwx.site
Lb_port = Load balancer port
For example: 6432

The following steps describe how to setup HAProxy as a load balancer and repmgr as replication manager for postgres database:

  1. Install the HAProxy load balancer.
    1. On CentOS 7, use yum repository to install HAProxy.
      yum install haproxy

      The latest version of HAProxy available installs in yum repository.

    2. Run the following command to ensure that HAProxy runs every time the server reboots.
      chkconfig haproxy on
    3. Start the service, using the following command.
      systemctl start haproxy && systemctl status haproxy
  2. Install the replication manager.

    You can use repmgr package for performing replication of databases. repmgr is available along with postgres repository so we can directly run the install command to install this package.

    sudo yum install repmgr_12
  3. Configure the HA proxy:
    1. Edit the HAProxy Configuration file.
      vi /etc/haproxy/haproxy.cfg
    2. Add the primary database server hostname and port number.
      Example configuration file for haproxy.cfg:
      frontend                        postgres-front
          mode tcp
          option http-server-close
          timeout client          3h
          timeout http-keep-alive 10s
          bind                        *:6432
          option httpchk
          default_backend             postgres-back
          option tcplog
      
      backend postgres-back
          mode tcp
          option http-server-close
          retries                 5
          timeout connect         3s
          timeout server          3h
          timeout http-keep-alive 10s
          option httpchk
          option tcp-check
          option tcplog
          tcp-check connect
          balance                     source
      server postgresprimary <hostname_of_primary_db>:5432 check
  4. Configure the replication manager:

    Postgres service must be up & running.

    1. Update the following settings in postgresql.conf on the primary server.
      max_wal_senders = 10
      
      max_replication_slots = 10
      
      wal_level = 'hot_standby' or 'replica' or 'logical'
      
      hot_standby = on
      
      archive_mode = on
      
      archive_command = '/bin/true'
      
      shared_preload_libraries = 'repmgr'
    2. Create a dedicated user for repmgr.
      create user repmgr;
      create database repmgr with owner repmgr;
    3. Ensure the repmgr user has appropriate permissions in pg_hba.conf and can connect in replication mode.
         local   replication   repmgr                     trust
          host    replication   repmgr      127.0.0.1/32            trust
          host    replication   repmgr      192.168.1.0/24          trust
      
          local   repmgr        repmgr                              trust
          host    repmgr        repmgr      127.0.0.1/32            trust
          host    repmgr        repmgr      192.168.1.0/24          trust
    4. Create a repmgr.conf on the master server with the following entries:
      cluster='failovertest'
      
      node_id=1
      
      node_name=node1
      
      conninfo='host=node1 user=repmgr dbname=repmgr connect_timeout=2'
      
      data_directory='/var/lib/pgsql/12/data/'
      
      failover=automatic
      
      promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /var/lib/pgsql/repmgr.conf --log-to-file'
      
      follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
    5. Register the primary server with repmgr:
      -bash-4.2$ /usr/pgsql-12/bin/repmgr -f /var/lib/pgsql/repmgr.conf primary register
    6. Create the repmgr.conf file on standby server with following settings:
      -bash-4.2$ cat repmgr.conf
      
      node_id=2
      
      node_name=node2
      
      conninfo='host=172.16.140.137 user=repmgr dbname=repmgr connect_timeout=2'
      
      data_directory='/var/lib/pgsql/12/data'
      
      failover=automatic
      
      promote_command='/usr/pgsql-12/bin/repmgr standby promote -f /var/lib/pgsql/repmgr.conf --log-to-file'
      
      follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
    7. Start cloning the database, using the following command:
      -bash-4.2$ /usr/pgsql-12/bin/repmgr -h 172.16.140.135 -U repmgr -d repmgr -f /var/lib/pgsql/repmgr.conf standby clone
    8. Register the standby server with repmgr :
      -bash-4.2$ /usr/pgsql-12/bin/repmgr -f /var/lib/pgsql/repmgr.conf standby register
    9. Enable the automatic failover, by starting the repmgrd daemon process on master and slave:
      -bash-4.2$ /usr/pgsql-12/bin/repmgrd -f /var/lib/pgsql/repmgr.conf