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.