In this blog we will create the master-slave setup of the postgres using the streaming replication. All below steps are tested with the centos.

HA Postgres Setup:

Type of the replication — Streaming Replication

Step 1: Install the Postgres on both master and slave (Centos 7)

$ rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

$ yum install postgresql10-server postgresql10
Init Postgres:
$ /usr/pgsql-10/bin/postgresql-10-setup initdb
Start and Enable the postgres service:
$ sudo systemctl start postgresql-10.service
$ sudo systemctl enable postgresql-10.service
Make sure the postgres is working:
$ su — postgres -c “psql”

psql (10.0)
Type “help” for help.
postgres=#

Step 2: Configure SSH passwordless connection for postgres user

Create the public and private keys:

## On postgres101.us-west-2.compute.internal — Master
## Create a private key for postgres user
sudo -u postgres ssh-keygen -b 2048 -t rsa -N “” -C “[email protected]”
## On postgres102.us-west-2.compute.internal — Slave
## Create a private key for postgres user
sudo -u postgres ssh-keygen -b 2048 -t rsa -N “” -C “[email protected]”

Copy the public keys:

Slave to Master Password less:

On postgres101.us-west-2.compute.internal — Master Copy the key /var/lib/pgsql/.ssh/id_rsa.pub

On postgres102.us-west-2.compute.internal — Slave Paste the key
/var/lib/pgsql/.ssh/authorized_keys

Master to Slave Password less:

On postgres102.us-west-2.compute.internal — Slave Copy the key /var/lib/pgsql/.ssh/id_rsa.pub

On postgres101.us-west-2.compute.internal — Master Paste the key
/var/lib/pgsql/.ssh/authorized_keys

Test the connection :

On master: SSH into slave without password

On master postgres101.us-west-2.compute.internal:

$ sudo su postgres
bash-4.2$ ssh [email protected]
Last login: Wed Oct 24 05:27:46 2018 from postgres101.us-west-2.compute.internal
-bash-4.2$

On Slave: SSH into the master without password

On slave postgres102.us-west-2.compute.internal:

$ sudo su postgres
bash-4.2$ ssh [email protected]
Last login: Wed Oct 24 05:27:46 2018 from postgres102.us-west-2.compute.internal
-bash-4.2$

If you are able to log in then the SSH passwordless connection is successful.

Step 3: Setup the repmgr for master and slave.

$ curl https://dl.2ndquadrant.com/default/release/get/10/rpm | sudo bash
$ yum install repmgr10

Create the replication repmgr user on master and slave:

$ sudo -i -u postgres
postgres$ createuser — replication — createdb — createrole — superuser repmgr
postgres$ psql -c ‘ALTER USER repmgr SET search_path TO repmgr_test, “$user”, public;’
postgres$ createdb repmgr — owner=repmgr

Change the postgresql.conf on master and slave : Few settings are dependent on the type of server you are using. Check the tool: https://pgtune.leopard.in.ua/#/ for some exact numbers.

For archive make sure you have the directory in our example it’s /mnt/server/archivedir. If not create the directory and allow postgres user to access it on both master and slave.

$ mkdir -p /mnt/server/archivedir
$ sudo chown -R postgres:postgres /mnt/server/archivedir

Add the following lines with appropriate values in: /var/lib/pgsql/10/data/postgres.conf on master and slave. Keep all other parameters as default.

Change the configuration of the pg_hba.conf: /var/lib/pgsql/10/data/pg_hba.conf
Allow the repmgr user trust for the PostgreSQL servers:
Add following lines in /var/lib/pgsql/10/data/pg_hba.conf on master and slave.

#The following setting is for the replication master and server
# Master — postgres101.us-west-2.compute.internal — 192.168.10.5/32
# Slave — postgres102.us-west-2.compute.internal — 192.168.10.6/32

host repmgr repmgr 192.168.10.5/32 trust
host repmgr repmgr 192.168.10.6/32 trust

host replication repmgr 192.168.10.5/32 trust
host replication repmgr 192.168.10.6/32 trust

Save the configuration.

Restart the postgres :

$ sudo systemctl restart postgresql-10.service

Verify the connection:

# On Master — postgres101.us-west-2.compute.internal
sudo su postgres
psql ‘host=postgres102.us-west-2.compute.internal dbname=repmgr user=repmgr’

# On Standby — postgres102.us-west-2.compute.internal
sudo su postgres
psql ‘host=postgres101.us-west-2.compute.internal dbname=repmgr user=repmgr’

Modify the repmgr configuration with respect to master and slave.

On master :

conninfo — It is the URL where repmgr will able to connect local postgres. According to the configuration, it will change. For master it is :postgres101.us-west-2.compute.internal

node_id — It must be unique (integer)
node_name- It must be a unique string (hostname for example)
Please check the comments in the configuration file for the option usage.
File location : /etc/repmgr/10/repmgr.conf

For logging make sure the file is created and have the appropriate permission for the postgres user. In this example,

# On master
$ touch /var/log/repmgr.log
$ sudo chown postgres:postgres /var/log/repmgr.log

The failover for this approach is Manual. We are not considering the automatic failover as of now.

Register the Primary server with repmgr:

On master run the following: ( Make sure the PostgreSQL is running on master )

$ sudo su postgres
bash-4.2$ cd /usr/pgsql-10/bin
bash-4.2$ ./repmgr -f /etc/repmgr/10/repmgr.conf primary register
# Verify the repmgr registration
bash-4.2$./repmgr -f /etc/repmgr/10/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Connection string
— — + — — — — — — — — — — — — — — — — — + — — — — -+ — — — — — -+ — — — — — — — — — — — — — — — — — + — — — — — + — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
1 | postgres101.us-west-2.compute.internal | primary | * running | | default | host=postgres101.us-west-2.compute.internal dbname=repmgr user=repmgr
bash-4.2$

For the first time, it will create the extension and register the master as the primary node.

Start the repmgr service on master :

$ sudo systemctl start repmgr10.service

Register the standby server:

Modify following in the repmgr.conf on standby: Change the settings according to your configuration.

Make sure on slave you have the log file.

#slave
$ touch /var/log/repmgr.log
$ sudo chown postgres:postgres /var/log/repmgr.log

Clone from the master and register standby.

Run following on standby:

$ sudo systemctl stop postgresql-10.service
$ sudo su postgres
bash-4.2$cd /usr/pgsql-10/bin
bash-4.2$./repmgr -f /etc/repmgr/10/repmgr.conf — force -h postgres101.us-west-2.compute.internal -d repmgr -U repmgr — verbose standby clone
bash-4.2$ exit
$ sudo systemctl start postgresql-10.service
$ sudo su postgres
bash-4.2$cd /usr/pgsql-10/bin
bash-4.2$./repmgr -f /etc/repmgr/10/repmgr.conf — force standby register
#Verify connections
bash-4.2$ ./repmgr -f /etc/repmgr/10/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Connection string
— — + — — — — — — — — — — — — — — — — — + — — — — -+ — — — — — -+ — — — — — — — — — — — — — — — — — + — — — — — + — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
1 | postgres101.us-west-2.compute.internal | primary | * running | | default | host=postgres101.us-west-2.compute.internal dbname=repmgr user=repmgr
2 | postgres102.us-west-2.compute.internal | standby | running | postgres101.us-west-2.compute.internal | default | host=postgres102.us-west-2.compute.internal dbname=repmgr user=repmgr

This will set up the replication between the master and the slave. Remember the slave act as the read-only replica of the master. So you must always write into the master. To verify the replication is in place create a database.

# On master
$ sudo su postgres
$ psql
postgres=# CREATE DATABASE y;
CREATE DATABASE
postgres=#
# On slave
$ sudo su postgres
$ psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
— — — — — -+ — — — — — + — — — — — + — — — — — — -+ — — — — — — -+ — — — — — — — — — — — -
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
y | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(2 rows)

This verifies your replication is working. Postgres is deployed with HA status and having streaming replication.