Repmgr

== DRAFT ==

Installation

1
2
3
4
5
6
7
8
9
10
# echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# apt-get install wget ca-certificates
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
# apt-get update
# apt-get install postgresql
# echo "export PATH=$PATH:/usr/lib/postgresql/9.5/bin" >> /etc/profile
# apt-get install rsync
# apt-get install repmgr
# su - postgres
# ssh-keygen

Configuration

On echange les clefs des users postgres entre les differents serveurs

  • Set des autorisations postgres
1
2
3
4
# tail -3 /etc/postgresql/9.5/main/pg_hba.conf 
local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      10.0.69.0/24            trust
  • Conf Postgres
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# diff /etc/postgresql/9.5/main/postgresql.conf /tmp/postgresql.conf.ori 
59c59
< listen_addresses = '*'                # what IP address(es) to listen on;
---
> #listen_addresses = 'localhost'               # what IP address(es) to listen on;
143c143
< shared_preload_libraries = 'repmgr_funcs'             # (change requires restart)
---
> #shared_preload_libraries = ''                # (change requires restart)
171c171
< wal_level = hot_standby               # minimal, archive, hot_standby, or logical
---
> #wal_level = minimal                  # minimal, archive, hot_standby, or logical
204c204
< archive_mode = on             # enables archiving; off, on, or always
---
> #archive_mode = off           # enables archiving; off, on, or always
206c206
< archive_command = '/bin/true'         # command to use to archive a logfile segment
---
> #archive_command = ''         # command to use to archive a logfile segment
222c222
< max_wal_senders = 10          # max number of walsender processes
---
> #max_wal_senders = 0          # max number of walsender processes
224c224
< wal_keep_segments = 5000              # in logfile segments, 16MB each; 0 disables
---
> #wal_keep_segments = 0                # in logfile segments, 16MB each; 0 disables
245c245
< hot_standby = on                      # "on" allows queries during recovery
---
> #hot_standby = off                    # "on" allows queries during recovery
  • Conf Repmgr

toute les commande repmgr sont a lancer en postgres

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# cat /etc/repmgr.conf 
cluster=jaytest
node=2
node_name=node2
conninfo='host=10.0.69.110 user=repmgr dbname=repmgr'
pg_basebackup_options='--xlog-method=fetch'
pg_bindir=/usr/lib/postgresql/9.5/bin/
#upstream_node=1
#priority=100

monitor_interval_secs=2
master_response_timeout=60
#reconnect_attempts=6
#reconnect_interval=10

failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr.conf'

logfile='/var/log/postgresql/repmgrd.log'
1
2
$ createuser -s repmgr
$ createdb repmgr -O repmgr

Replication

  • Initialise the master server
1
repmgr -f /etc/repmgr.conf primary register

On check

1
2
=# \c repmgr
=# SELECT * FROM repmgr_jaytest.repl_nodes;
  • Clone the standby server (stop postgresql ; purge /var/lib/postgresql/9.5/main/ first)
1
2
3
4
repmgr -h node1 -U repmgr -d repmgr -D /var/lib/postgresql/9.5/main/ -f /etc/repmgr.conf standby clone

# rsync node1:/etc/postgresql/9.5/main/pg_hba.conf /etc/postgresql/9.5/main/
# rsync node1:/etc/postgresql/9.5/main/postgresql.conf  /etc/postgresql/9.5/main/

On check (depuis le master)

1
=# SELECT * FROM pg_stat_replication;
  • Register the standby
1
repmgr -f /etc/repmgr.conf standby register

On check 

1
2
=# \c repmgr
=# SELECT * FROM repmgr_jaytest.repl_nodes ORDER BY id;

Recovery

node1 est dead

1
2
3
4
5
6
$ repmgr cluster show
Role      | Name  | Upstream | Connection String
----------+-------|----------|-------------------------------------------
  standby | node3 | node2    | host=10.0.69.110 user=repmgr dbname=repmgr
  FAILED  | node1 |          | host=10.0.69.108 user=repmgr dbname=repmgr
* master  | node2 |          | host=10.0.69.109 user=repmgr dbname=repmgr

on stop ses services

1
# /etc/init.d/postgresql stop

on resync & register

1
2
3
# repmgr -h node3 -U repmgr -d repmgr -D /var/lib/postgresql/9.5/main/ -f /etc/repmgr.conf standby clone

# repmgr -f /etc/repmgr.conf standby register -F

node1 is back

1
2
3
4
5
6
$ repmgr cluster show
Role      | Name  | Upstream | Connection String
----------+-------|----------|-------------------------------------------
  standby | node3 | node2    | host=10.0.69.110 user=repmgr dbname=repmgr
* master  | node2 |          | host=10.0.69.109 user=repmgr dbname=repmgr
  standby | node1 | node2    | host=10.0.69.108 user=repmgr dbname=repmgr

Lancement de repmgrd

  • pour quand le script d’init marchera :

    1
    2
    3
    4
    # sed -i 's/REPMGRD_ENABLED=no/REPMGRD_ENABLED=yes/' /etc/default/repmgrd
    # sed -i 's/\#REPMGRD_CONF\=\"\/path\/to\/repmgr.conf\"/REPMGRD_CONF\=\"\/etc\/repmgr.conf\"/' /etc/default/repmgrd
    # sed -i 's/\#REPMGRD_OPTS=\"\"/REPMGRD_OPTS="-m -d"/' /etc/default/repmgrd
    # sed -i 's/\#REPMGR_PIDFILE\=\/var\/run\/repmgrd.pid/REPMGR_PIDFILE\=\/var\/run\/postgresql\/repmgrd.pid/' /etc/default/repmgrd

  • mon script d’init de remplacement :

    1
    # cat /etc/init.d/repmgrd.init
#!/bin/bash


### BEGIN INIT INFO 
# Provides: repmgrd 
# Required-Start: $remote_fs $all 
# Should-Start: 
# Required-Stop: 
# Default-Start: 2 3 5 
# Default-Stop: 0 6 
# Description: Starts and stops repmgrd. 
### END INIT INFO



REPMGRD=/usr/bin/repmgrd
REPMGR_PIDFILE=/var/run/postgresql/repmgrd.pid
REPMGRD_CONF=/etc/repmgr.conf

case $1 in

# Startup repmgrd servers.
        start)  

                su postgres -c "$REPMGRD -m -d -p $REPMGR_PIDFILE -f $REPMGRD_CONF"
                #renice -5 -p `cat $REPMGR_PIDFILE`
                echo "starting..."
        ;;

# Stop repmgrd servers.
        stop)   
                if [ -f $REPMGR_PIDFILE ]
                        then if  ps auxww | grep `cat $REPMGR_PIDFILE` | grep -v grep
                                then kill `cat $REPMGR_PIDFILE`
                                     echo "stopping..."
                                else echo "Repmgrd is not running, but $REPMGR_PIDFILE is present"
                             fi
                        else echo "Repmgrd is not running"
                fi
        ;;
# status
        status)   
                if ps auxww | grep `cat $REPMGR_PIDFILE` | grep -v grep
                        then echo "Repmgrd is running"
                        else echo "Repmgrd is stopped"
                fi

        ;;

        *)
                echo "Init de Repmgrd: stop / start ou status"
        ;;
esac

Notif

repmgr.conf

%n - node ID
%e - event type
%s - success (1 or 0)
%t - timestamp
%d - details

event_notification_command=’/path/to/some/script %n %e %s “%t” “%d”‘

event_notifications=master_register,standby_register,witness_create
https://github.com/2ndQuadrant/repmgr/blob/master/README.md#generating-event-notifications-with-repmgrrepmgrd

Gestion de la vip avec heartbeat dans le cas d’un master/slave

Install

1
# apt-get install heartbeat

conf de la vip

1
# cat <<EOF >> /etc/network/interfaces
#auto ens160:pg # nonauto
iface ens160:pg inet static
address 10.0.69.0.120
netmask 255.255.255.0
EOF
# echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.conf
# sysctl -p

  • commande heartbeat utiles
    /usr/share/heartbeat/hb_standby
    /usr/share/heartbeat/hb_takeover

  • conf heartbeat

    1
    # cat /etc/ha.d/ha.cf
###############################
# logging
logfile /var/log/ha/ha-log
debugfile /var/log/ha/ha-debug

###############################
# communication
autojoin none
udpport 694
ucast ens160 10.0.69.100
bcast ens160

###############################
# thresholds
warntime 5
deadtime 15
initdead 60
keepalive 2

###############################
# nodes
node bdd1
node bdd2

auto_failback off
1
# cat /etc/ha.d/haresources
# VIP
bdd1 IPaddr::10.0.69.120/24/ens160:pg #MailTo::moi@pouet.fr::HA_Postgresql

  • on adapte la conf /etc/repmgr.conf

    1
    # diff /etc/repmgr.conf_heartbeat /etc/repmgr.conf_ori
- promote_command='repmgr standby promote -f /etc/repmgr.conf'
- follow_command='repmgr standby follow -f /etc/repmgr.conf'
+ promote_command='sudo /usr/share/heartbeat/hb_takeover && repmgr standby promote -f /etc/repmgr.conf'
+ follow_command='sudo /usr/share/heartbeat/hb_standby && repmgr standby follow -f /etc/repmgr.conf'

  • conf sudo
    postgres ALL=(root) NOPASSWD:/usr/share/heartbeat/hb_takeover