Configure PostgreSQL replication with repmgr

This 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> – a 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:

Export LD_LIBRARY_PATH

$ echo "export LD_LIBRARY_PATH=/opt/workfusion/postgres/lib:\$LD_LIBRARY_PATH" >> /opt/workfusion/environment.sh
$ source /opt/workfusion/environment.sh

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 dir, 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/

Backup 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 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. Remeber to make a backup of the file before changing it. The values of the target options must be 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 the <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 must look, 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='host=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 will look like:

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

General services

APP

DB

OCR

VDS

OOTB configuration

Infrastructure scaling

Run preinstall manually

Using TLS certificates

Elastic Monitoring

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