Repmgr高可用环境搭建

环境：

centos：7.6
primary：192.168.11.158
standby：192.168.11.159
standby：192.168.11.160
pgsql：11.4
repmgr：4.3

一、repmgr安装

tar -zxvf repmgr-4.3.tar.gz
./configure && make install

二、postgresql安装

主库安装并初始化数据库：

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11
yum install postgresql11-server

postgresql.conf参数：

 max_wal_senders = 10
 max_replication_slots = 10
 wal_level = 'logical'
 hot_standby = on
 archive_mode = on
 archive_command='cp %p /pg11/pg_archive/%f'

pg_hba.conf配置：

local   replication   repmgr                              trust
host    replication   repmgr      127.0.0.1/32            trust
host    replication   repmgr      192.168.11.0/24         trust

local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      192.168.11.0/24         trust

备库安装pgsql软件即可，不需要初始化数据库

三、创建扩展

createuser -s repmgr
createdb repmgr -O repmgr

四、repmgr.conf配置：

node_id=1
node_name=pg1
conninfo='host=pg1 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/pg11/data'
pg_bindir='/usr/pgsql-11/bin'
log_file='/var/log/repmgr/repmgr1.log'

use_replication_slots=true


service_start_command   = 'sudo systemctl start postgresql-11'
service_stop_command    = 'sudo systemctl stop postgresql-11'
service_restart_command = 'sudo systemctl restart postgresql-11'
service_reload_command  = 'sudo systemctl reload postgresql-11'

failover=automatic
promote_command='/usr/pgsql-11/bin/repmgr standby promote -f /pg11/repmgr.conf --log-to-file'
follow_command='/usr/pgsql-11/bin/repmgr standby follow -f /pg11/repmgr.conf --log-to-file --upstream-node-id=%n'
monitoring_history=yes
monitor_interval_secs=2

五、注册主库

注册：

repmgr -f /pg11/repmgr.conf primary register

查看状态：

repmgr -f /pg11/repmgr.conf cluster show
 ID | Name | Role    | Status    | Upstream | Location | Priority | Connection string                                   
----+------+---------+-----------+----------+----------+----------+------------------------------------------------------
 1  | pg1  | primary | * running |          | default  | 100      | host=pg1 user=repmgr dbname=repmgr connect_timeout=2

repmgr=# SELECT * FROM repmgr.nodes;
node_id | upstream_node_id | active | node_name |  type   | location | priority |                       conninfo                   
    | repluser |   slot_name   |    config_file    
---------+------------------+--------+-----------+---------+----------+----------+--------------------------------------------------
----+----------+---------------+-------------------
       2 |                  | t      | pg2       | primary | default  |      100 | host=pg2 user=repmgr dbname=repmgr connect_timeou
t=2 | repmgr   | repmgr_slot_2 | /pg11/repmgr.conf
       3 |                2 | t      | pg3       | standby | default  |      100 | host=pg3 user=repmgr dbname=repmgr connect_timeou
t=2 | repmgr   | repmgr_slot_3 | /pg11/repmgr.conf
       1 |                2 | t      | pg1       | standby | default  |      100 | host=pg1 user=repmgr dbname=repmgr connect_timeou
t=2 | repmgr   | repmgr_slot_1 | /pg11/repmgr.conf

六、clone备库

repmgr.conf配置参数参考节点一

测试clone是否正常：

repmgr -h node1 -U repmgr -d repmgr -f /pg11/repmgr.conf standby clone --dry-run

clone:
repmgr -h node1 -U repmgr -d repmgr -f /pg11/repmgr.conf standby clone

启动备库：

pg_ctl start

注册备库：

repmgr -f /pg11/repmgr.conf standby register

验证复制状态：

 pid  | usesysid | usename | application_name |  client_addr   | client_port |         backend_start         |   state   | sync_stat
e 
------+----------+---------+------------------+----------------+-------------+-------------------------------+-----------+----------
--
 1484 |    16384 | repmgr  | pg3              | 192.168.11.160 |       38410 | 2019-06-26 17:18:17.876922+08 | streaming | async
 4705 |    16384 | repmgr  | pg1              | 192.168.11.158 |       46388 | 2019-06-26 18:26:03.623083+08 | streaming | async

验证状态：

repmgr -f /pg11/repmgr.conf cluster show
 ID | Name | Role    | Status    | Upstream | Location | Priority | Connection string                                   
----+------+---------+-----------+----------+----------+----------+------------------------------------------------------
 1  | pg1  | standby |   running | pg2      | default  | 100      | host=pg1 user=repmgr dbname=repmgr connect_timeout=2
 2  | pg2  | primary | * running |          | default  | 100      | host=pg2 user=repmgr dbname=repmgr connect_timeout=2
 3  | pg3  | standby |   running | pg2      | default  | 100      | host=pg3 user=repmgr dbname=repmgr connect_timeout=2

七、配置服务命令

因为默认repmgr使用pg_ctl命令来操作数据库，但是会有一些问题，所以需要配置成systemctl命令来操作数据库

service_start_command   = 'sudo systemctl start postgresql-11'
service_stop_command    = 'sudo systemctl stop postgresql-11'
service_restart_command = 'sudo systemctl restart postgresql-11'
service_reload_command  = 'sudo systemctl reload postgresql-11'

完整配置文件见第四步

八、手动切换

1，停止当前主库
pg_ctl -D /pg11/data -m fast stop
2，提升备库为主库
repmgr -f /pg11/repmgr.conf standby promote
3，备库指向新的主库
repmgr -f /pg11/repmgr.conf repmgr standby follow

可以指定–siblings-follow,–force-rewind选项，自动完成指向新的主库和原主库自动降级成备库：
repmgr standby switchover -f /pg11/repmgr.conf --siblings-follow --force-rewind

九，自动切换

完整配置文件见第四步
如果要实现自动failover，就需要启用repmgrd，启动repmgrd必须设置postgres.conf的shared_preload_libraries=’repmgr’

1，启动repmgrd
repmgrd -f /pg11/repmgr.conf --verbose --monitoring-history > /var/log/repmgr/repmgr.log 2>&1 & 

2，switchover后将原primary重新加入集群并作为standby
repmgr -f /pg11/repmgr.conf node rejoin -d 'host=pg1 dbname=repmgr user=repmgr' --force-rewind --config-files=postgresql.conf,postgresql.auto.conf --verbose --dry-run
repmgr -f /pg11/repmgr.conf node rejoin -d 'host=pg1 dbname=repmgr user=repmgr' --force-rewind --config-files=postgresql.conf,postgresql.auto.conf --verbose


repmgr -f /pg11/repmgr.conf node rejoin -d 'host=pg2 dbname=repmgr user=repmgr' --force-rewind --config-files=postgresql.conf,postgresql.auto.conf --verbose --dry-run


repmgr.conf：
node_id=1
node_name=pg1
conninfo='host=pg1 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/home/postgres/pg114/data'
pg_bindir='/home/postgres/pg114/bin'


service_start_command   = 'sudo service postgresql11-4 start'
service_stop_command    = 'sudo service postgresql11-4 stop'
service_restart_command = 'sudo service postgresql11-4 restart'
service_reload_command  = 'sudo service postgresql11-4 reload'

##########repmgrd config###########
monitoring_history=yes
monitor_interval_secs = 2
connection_check_type = connection    ##ping,connection,query
reconnect_attempts = 5    
reconnect_interval = 5
log_level=INFO
log_file='/home/postgres/pg114/repmgrd.log'
failover=automatic
promote_command='/home/postgres/pg114/bin/repmgr standby promote -f /home/postgres/pg114/repmgr.conf --log-to-file'
follow_command='/home/postgres/pg114/bin/repmgr standby follow -f /home/postgres/pg114/repmgr.conf --log-to-file --upstream-node-id=%n'

repmgrd_service_start_command = 'repmgrd -f /home/postgres/pg114/repmgr.conf --pid-file /tmp/repmgrd.pid'
repmgrd_service_stop_command = 'kill `cat /tmp/repmgrd.pid`'