REPLICATION MANAGER

Automatic failover using Replication Manager on PostgreSQL

 

Install repmgr on both the nodes of posgresql

Here Postgresql and repmgr nodes are:

Node1 : 172.16.1.90

Node2 : 172.16.1.91

 

wget http://www.repmgr.org/download/repmgr-3.1.1.tar.gz

tar -zxvf repmgr-3.1.1.tar.gz

cd repmgr/

 

[ Note :Before installatation set the environment variable path as

postgres@owncloud2:~$ cat .bash_profile

export PATH=/home/postgres/pgsql-9.3.6/bin:$PATH ]

make USE_PGXS=1 install

 

Configure  repmgr Conf file

 

At Master: ( 172.16.1.90 )

postgres@owncloud1:~$mkdir -p pgsql-9.3.6/repmgr/

postgres@owncloud1:~/pgsql-9.3.6/repmgr$ vi repmgr.conf

cluster=owncloud  ## We should keep the cluster name same on both the nodes

node=1

node_name=owncloud1.juhu.zedo.com

conninfo=’host=172.16.1.90 port=5432 user=postgres dbname=postgres’

pg_bindir=/home/postgres/pgsql-9.3.6/bin

master_response_timeout=60

reconnect_attempts=6

reconnect_interval=10

failover=automatic

promote_command=’/home/postgres/pgsql-9.3.6/repmgr/auto_failover.sh’

 

———————————————————————————————–

 

Put the auto_failover.sh script with below commands.

 

postgres@owncloud2:~/pgsql-9.3.6/repmgr$ vi auto_failover.sh

#!/bin/bash  

echo “Promoting Standby at `date ‘+%Y-%m-%d %H:%M:%S’`” >>/tmp/repsetup.log  

/home/postgres/pgsql-9.3.6/bin/pg_ctl -D /home/postgres/data-9.3.6 promote >>/tmp/repsetup.log

 

====================================================================

What mainly you have to observe in repmgr.conf is “master_reponse_timeout” which’s total wait duration in seconds before declaring master has disappeared. In that duration 6 reconnect attempts made with 10 seconds of interval. After no response from master in “master_response_timeout” duration automatic failover takes place by promote_command script. The script consist of Standby promotion steps, which I have created one for this setup shared below.

=====================================================================

 

At SLAVE: ( 172.16.1.91)

postgres@owncloud2:~$mkdir -p pgsql-9.3.6/repmgr/

postgres@owncloud2:~/pgsql-9.3.6/repmgr$ vi repmgr.conf

cluster=owncloud

node=2

node_name=owncloud2.juhu.zedo.com

conninfo=’host=172.16.1.91 port=5432  dbname=postgres’

pg_bindir=/home/postgres/pgsql-9.3.6/bin

master_response_timeout=60

reconnect_attempts=6

reconnect_interval=10

failover=automatic

promote_command=’/home/postgres/pgsql-9.3.6/repmgr/auto_failover.sh’

 

Register Master and Standby node with repmgr using “repmgr” utility.

 

ON MASTER SERVER.

postgres@owncloud1:~/pgsql-9.3.6/repmgr$ repmgr -f repmgr.conf –verbose master register

> >~/pgsql-9.3.6/repmgr/repsetup.log

 

Master register logs (~/pgsql-9.3.6/repmgr/repsetup.log)

[INFO] repmgr connecting to master database  

[INFO] repmgr connected to master, checking its state  

[INFO] master register: creating database objects inside the repmgr_test schema  

 

ON SLAVE SERVER.

postgres@owncloud2:~/pgsql-9.3.6/repmgr$repmgr -h 172.16.1.91 -U postgres -d postgres  -D ~/data-9.3.6 -f repmgr.conf standby clone >> ~/pgsql-9.3.6/repmgr/repsetup.log

 

postgres@owncloud2:~/pgsql-9.3.6/repmgr$repmgr -f repmgr.conf standby register  >> ~/pgsql-9.3.6/repmgr/repsetup.log

 

Lets start the repmgr daemon process to watch master

postgres@owncloud2:~/pgsql-9.3.6/repmgr$repmgrd  -f ~/pgsql-9.3.6/repmgr/repmgr.conf –verbose –monitoring-history >> ~/pgsql-9.3.6/repmgr/repsetup.log 2>&1

 

Monitor nodes registered with repmgr and running daemon process.

postgres@owncloud2:~/pgsql-9.3.6/repmgr$  repmgr -f repmgr.conf cluster show

Role      | Name                    | Upstream                | Connection String

———-+————————-|————————-|———————————————————-

 standby | owncloud1.juhu.zedo.com |                         | host=172.16.1.90 port=5432 user=postgres dbname=postgres

* master  | owncloud2.juhu.zedo.com | owncloud1.juhu.zedo.com | host=172.16.1.91 port=5432  dbname=postgres

 

Advertisements