Configure PostgreSQL replication with repmgr

The guide describes configuring of replication between PostgreSQL Databases using the repmgr library. We assume that SPA environments DB_prod and DB_dr are already installed.

Prerequisites

/opt/workfusion: <INSTALL_DIR> is the default Workfusion installation directory
DB_prod: the PostgreSQL DB server in the PROD environment
DB_dr: the PostgreSQL DB server in the DR environment
repmgr95-4.2-1.el7.x86_64.rpm: the library for replication

Configure replication on DB servers in PROD and DR

To configure replication:

In the <INSTALL_DIR> directory, create the repmgr directory.

$ mkdir -pv /opt/workfusion/repmgr
$ cd /opt/workfusion/repmgr

Copy the repmgr rpm file to <INSTALL_DIR>/repmgr:

$ cp repmgr95-4.2-1.el7.x86_64.rpm /opt/workfusion/repmgr/

Extract rpm:

$ cd /opt/workfusion/repmgr
$ rpm2cpio repmgr95-4.2-1.el7.x86_64.rpm | cpio -idmv

In the postgres home directory, create the repmgr directory:
```
$ mkdir -p /opt/workfusion/postgres/repmgr
```

Move the repmgr libraries and binaries to the corresponding locations:

$ cp /opt/workfusion/repmgr/usr/pgsql-9.5/bin/repmgr* /opt/workfusion/postgres/bin/
$ cp /opt/workfusion/repmgr/usr/pgsql-9.5/lib/repmgr* /opt/workfusion/postgres/lib/
$ cp /opt/workfusion/repmgr/usr/pgsql-9.5/share/extension/repmgr* /opt/workfusion/postgres/share/postgresql/extension/

Back up existing SQL extensions before modifying them:

$ cp -r /opt/workfusion/postgres/share/postgresql/extension /opt/workfusion/postgres/share/postgresql/extension_backup

Modify repmgr-related extensions:

$ cd /opt/workfusion/postgres/share/postgresql/extension/
$ sed -i "s/\$libdir/\/opt\/workfusion\/postgres\/lib/g"  repmgr--unpackaged--4.0.sql
$ sed -i "s/\$libdir/\/opt\/workfusion\/postgres\/lib/g"  repmgr.control

Verify changes:

 $ grep -ir workfusion /opt/workfusion/postgres/share/postgresql/extension/repmgr*

Copy the libpqwalreceiver library to the <INSTALL_DIR>/postgres/lib directory:

$ cp /opt/workfusion/postgres/lib/postgresql/libpqwalreceiver.so /opt/workfusion/postgres/lib/libpqwalreceiver.so

Create a service file for the repmgr service:

$ vi /opt/workfusion/supervisord/apps/repmgr.ini

Add the following content to it:

[program:repmgr]
directory=/opt/workfusion/postgres/repmgr
environment =
    LD_LIBRARY_PATH="/opt/workfusion/postgres/lib:$LD_LIBRARY_PATH"

command = bash -c '/opt/workfusion/postgres/bin/repmgrd -f /opt/workfusion/postgres/repmgr/repmgr.conf -p /opt/workfusion/postgres/repmgr/repmgrd-9.5.pid --verbose --daemonize=false'

stopasgroup=true
autostart = true
killasgroup=true
startinorder = true
priority = 100

stdout_logfile = /opt/workfusion/supervisord/log/%(program_name)s.out.log
stderr_logfile = /opt/workfusion/supervisord/log/%(program_name)s.err.log

Update wfmanager:
```
$ wfmanager update
```

Configure SSH access without passwords between the DB_PROD and DB_DR servers.

# Generate ssh keys on master and slave
$ ssh-keygen
# this command generates ~/.ssh/id_rsa and ~/.ssh/id_rsa.pub
# creates ~/.ssh/authorized_keys and places content of ~/.ssh/id_rsa.pub file there (from Master to Slave and vise a versa)
# executes commands below
$ chmod 0600 ~/.ssh/authorized_keys
$ chmod 0600 ~/.ssh/id_rsa
# copy all three files: authorized_keys, id_rsa, and id_rsa.pub to slave server to the same location. Ensure that chmod is 0600.
# Ensure that in sshd configuration the access without password is enabled

Check that the SSH connection between servers is established.

Configure replication on DB_PROD server

To configure replication:

In the <INSTALL_DIR>/postgres/data/ directory, modify the pg_hba.conf file by adding the following lines to the beginning of the file. Remeber to make a backup of the file before changing it.

local   replication   repmgr                         trust
host    replication   repmgr      127.0.0.1/32       trust
host    replication   repmgr      10.0.0.1/32        trust
host    replication   repmgr      10.0.0.2/32        trust

local   repmgr   repmgr                         trust
host    repmgr   repmgr      127.0.0.1/32       trust
host    repmgr   repmgr      10.0.0.1/32        trust
host    repmgr   repmgr      10.0.0.2/32        trust

where:

10.0.0.1/32: IP-address of the DB_PROD server 10.0.0.2/32: IP-address of DB_DR server

In the <INSTALL_DIR>/postgres/data/ directory, edit the postgresql.conf file. Remember to make a backup of the file before changing it. The values of the target options are as follows:

shared_preload_libraries = 'repmgr'
max_wal_senders          = 10
wal_keep_segments        = 100
max_replication_slots    = 5
dynamic_library_path     = '/opt/workfusion/postgres/lib'
wal_level                = 'hot_standby'
hot_standby              = on
archive_mode             = on
archive_command          = '/bin/true'

In <INSTALL_DIR>/postgres/repmgr/, сreate the repmgr.conf file with the following content:

node_id=1
node_name=10.0.0.1
conninfo='host=10.0.0.1 port=5432 user=repmgr dbname=repmgr'
data_directory='/opt/workfusion/postgres/data'
config_directory='/opt/workfusion/postgres/data'
replication_user='repmgr'
replication_type=physical
location=default
witness_sync_interval=15
log_level=INFO
log_facility=STDERR
log_file='/opt/workfusion/postgres/repmgr/repmgr.log'
log_status_interval=300
repmgr_bindir='/opt/workfusion/postgres/bin/'
ssh_options='-q -o ConnectTimeout=10'
pg_bindir='/opt/workfusion/postgres/bin'
use_replication_slots=1
service_start_command='source /opt/workfusion/environment.sh && wfmanager start postgresql'
service_stop_command='source /opt/workfusion/environment.sh && wfmanager stop postgresql'
service_restart_command='source /opt/workfusion/environment.sh && wfmanager restart postgresql'
service_reload_command='source /opt/workfusion/environment.sh && /opt/workfusion/postgres/bin/pg_ctl reload -D /opt/workfusion/postgres/data'

Create the repmgr user and database:

$ /opt/workfusion/postgres/bin/createuser -h 127.0.0.1 -p 5432 -U postgresadmin --replication --createdb --createrole --superuser repmgr
$ /opt/workfusion/postgres/bin/createdb -h 127.0.0.1 -p 5432 -U postgresadmin repmgr --owner=repmgr

Set search_path for the repmgr user:

source /opt/workfusion/aliases.sh
psql
> ALTER USER repmgr SET search_path TO repmgr, "$user", public;

Restart the postgresql service:
```
$ wfmanager restart postgresql
```

$ /opt/workfusion/postgres/bin/repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf master register -F

Restart the repmgr service:
```
$ wfmanager restart repmgr
```

Check the registered Master node:

$ /opt/workfusion/postgres/bin/repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf cluster show
$ /opt/workfusion/postgres/bin/repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf node status

The output looks like as follows:

bash-4.2$ ../bin/repmgr -f repmgr.conf  cluster show
 ID | Name          | Role    | Status    | Upstream | Location | Connection string
----+---------------+---------+-----------+----------+----------+--------------------------------------------------------
 1  | 10.0.0.1 | primary | * running |          | default  | host=10.0.0.1 port=5432 user=repmgr dbname=repmgr

Configure replication on DB_DR server

To configure replication:

In the <INSTALL_DIR>/postgres/repmgr/ directory, create the repmgr.conf file with the following content:

node_id=2
node_name=10.0.0.2
conninfo='10.0.0.2 port=5432 user=repmgr dbname=repmgr'
data_directory='/opt/workfusion/postgres/data'
config_directory='/opt/workfusion/postgres/data'
replication_user='repmgr'
replication_type=physical
location=default
witness_sync_interval=15
log_level=INFO
log_facility=STDERR
log_file='/opt/workfusion/postgres/repmgr/repmgr.log'
log_status_interval=300
repmgr_bindir='/opt/workfusion/postgres/bin/'
ssh_options='-q -o ConnectTimeout=10'
pg_bindir='/opt/workfusion/postgres/bin'
use_replication_slots=1
service_start_command='source /opt/workfusion/environment.sh && wfmanager start postgresql'
service_stop_command='source /opt/workfusion/environment.sh && wfmanager stop postgresql'
service_restart_command='source /opt/workfusion/environment.sh && wfmanager restart postgresql'
service_reload_command='source /opt/workfusion/environment.sh && /opt/workfusion/postgres/bin/pg_ctl reload -D /opt/workfusion/postgres/data'

Stop the postgresql service:
```
$ wfmanager stop postgresql
```

Remove the data folder:

$ cp -r /opt/workfusion/postgres/data/ /opt/workfusion/postgres/data_backup
$ rm -rf /opt/workfusion/postgres/data

Clone the Master node to the Slave node:

$ /opt/workfusion/postgres/bin/repmgr -h 10.0.0.1 -U repmgr -d repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf standby clone -F

where:

10.0.0.1: IP-address of the DB_PROD server

Start the postgresql service:
```
$ wfmanager start postgresql
```

$ /opt/workfusion/postgres/bin/repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf standby register

Restart the repmgr service:
```
$ wfmanager restart repmgr
```

Check the registered Slave node:

$ /opt/workfusion/postgres/bin/repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf cluster show
$ /opt/workfusion/postgres/bin/repmgr -f /opt/workfusion/postgres/repmgr/repmgr.conf node status

The output looks like as follows:

 ID | Name     | Role    | Status    | Upstream | Location | Connection string
----+----------+---------+-----------+----------+----------+--------------------------------------------------
 1  | 10.0.0.1 | primary | * running |          | default  | host=10.0.0.1 port=5432 user=repmgr dbname=repmgr
 2  | 10.0.0.2 | standby |   running | 10.0.0.1 | default  | host=10.0.0.2 port=5432 user=repmgr dbname=repmgr

WorkFusion Documentation

9.0

Preinstall steps

Installation steps

Post-installation steps

Appendix

General information

APP

DB

OCR

VDS

Single sign-on

WorkFusion Analytics License

Platform Monitor overview

Platform Monitor configuration

Platform Monitor for RPA

Configure PostgreSQL replication with repmgr

Prerequisites

Configure replication on DB servers in PROD and DR

Configure replication on DB_PROD server

Configure replication on DB_DR server