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:

Install the HAProxy load balancer.

note
Install the load balancer only on the first node.
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
```
Install the replication manager.

note
Perform this step on all the nodes of databases.
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
```
note
this command installs postgres 12. Use a repmgr version that supports the postgres version installed.

Configure the HA proxy:

Edit the HAProxy Configuration file.
```
vi /etc/haproxy/haproxy.cfg
```

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

Configure the replication manager:

Postgres service must be up & running.

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'
```
note
In case of synchronous replication, synchronous_standby_names config param needs to be set in postgresql.conf

Create a dedicated user for repmgr.

create user repmgr;
create database repmgr with owner repmgr;

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

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'

-bash-4.2$ /usr/pgsql-12/bin/repmgr -f /var/lib/pgsql/repmgr.conf primary register

Create the repmgr.conf file on standby server with following settings:

note
In the commands for the host IP info, specify the IP of the standby server.

note
Users can also provide a script to automate the updating of HAProxy configurations in case of a failover. Provide this script in the promote_command config.
```
-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'
```

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

-bash-4.2$ /usr/pgsql-12/bin/repmgr -f /var/lib/pgsql/repmgr.conf standby register

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
```