PostgreSQL高可用之Repmgr见证服务器搭建

原创多米爸比 2020-02-05

3009

环境
HA1:192.168.99.200
HA2:192.168.99.202
HA3:192.168.99.222
PostgreSQL 12.1
repmgr 5.0.0

1.见证服务器HA3搭建

--clone from ha2
# vi /etc/sysconfig/network-scripts/ifcfg-enp0s3
IPADDR=192.168.99.222

# vi /etc/hosts
127.0.0.1   ha3 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         ha3 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.99.202 ha2
192.168.99.200 ha1
192.168.99.222 ha3

# hostnamectl set-hostname ha3
# reboot

--ha1和ha2确保集群状态正常，且repmgrd进程正常运行
[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                        
----+-------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------
 1  | node1 | standby |   running | node2    | default  | 100      | 16       | host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 16       | host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2

# service postgres-12 stop

[postgres@ha3 ~]$ cd /opt
[postgres@ha3 opt]$ rm -rf archive6000/*
[postgres@ha3 opt]$ rm -rf data6000/*

--初始化数据目录
[postgres@ha3 opt]$ /opt/pgsql/bin/initdb \
-D/opt/data6000 \
-EUTF8 \
-Upostgres \
-W

[postgres@ha3 opt]$ vi /opt/data6000/postgresql.conf
shared_preload_libraries = 'repmgr'
listen_addresses = '*'
port=6000
logging_collector=on
log_destination=csvlog
log_directory='/opt/data6000/pg_log'
log_filename='pg_log_%Y-%m-%d_%H%M%S.log'
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
log_rotation_size=10MB
log_min_messages=warning
log_min_duration_statement=30s
log_checkpoints=on
log_connections=on
log_disconnections=on
log_duration=on
log_lock_waits=on
log_statement=DDL

[postgres@ha3 opt]$ vi /opt/data6000/pg_hba.conf
host    repmgr        repmgr      192.168.99.0/24          trust

[postgres@ha3 opt]$ /opt/pgsql/bin/pg_ctl -D /opt/data6000/ start &

[postgres@ha3 opt]$ /opt/pgsql/bin/psql -p6000 -h127.0.0.1 -Upostgres
create user repmgr superuser connection limit 10 password '123456' ;
create database repmgr owner repmgr;

[postgres@ha3 opt]$ vi /opt/repmgr.conf 
node_id=3
node_name='node3'
conninfo='host=192.168.99.222 port=6000 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/opt/data6000'
replication_user='repmgr'
replication_type='physical'
repmgr_bindir='/opt/pgsql/bin'
pg_bindir='/opt/pgsql/bin'
monitoring_history=yes
monitor_interval_secs=5
log_level='info'
log_file='/opt/data6000/repmgr.log'

--192.168.99.202为主库HA2
[postgres@ha3 opt]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf witness register -h 192.168.99.202 -U repmgr -d repmgr --verbose
NOTICE: using provided configuration file "/opt/repmgr.conf"
INFO: connecting to witness node "node3" (ID: 3)
INFO: connecting to primary node
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
INFO: witness registration complete
NOTICE: witness node "node3" (ID: 3) successfully registered

[postgres@ha3 opt]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                        
----+-------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------
 1  | node1 | standby |   running | node2    | default  | 100      | 16       | host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 16       | host=192.168.99.202 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 3  | node3 | witness | * running | node2    | default  | 0        | 1        | host=192.168.99.222 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 
 --启动repmgrd
[postgres@ha3 opt]$ /opt/pgsql/bin/repmgrd -f /opt/repmgr.conf

2.测试关闭主节点HA2

[postgres@ha2 ~]$ /opt/pgsql/bin/pg_ctl -D /opt/data6000/ stop &

3.查看见证服务器日志

[postgres@ha3 opt]$ tailf /opt/data6000/repmgr.log 
[2020-02-03 19:45:11] [DETAIL] PQping() returned "PQPING_NO_RESPONSE"
[2020-02-03 19:45:11] [INFO] sleeping 10 seconds until next reconnection attempt
[2020-02-03 19:45:21] [INFO] checking state of node 2, 3 of 6 attempts
[2020-02-03 19:45:21] [WARNING] unable to ping "user=repmgr connect_timeout=2 dbname=repmgr host=192.168.99.202 port=6000 fallback_application_name=repmgr"
[2020-02-03 19:45:21] [DETAIL] PQping() returned "PQPING_NO_RESPONSE"
[2020-02-03 19:45:21] [INFO] sleeping 10 seconds until next reconnection attempt
[2020-02-03 19:45:31] [INFO] checking state of node 2, 4 of 6 attempts
[2020-02-03 19:45:31] [WARNING] unable to ping "user=repmgr connect_timeout=2 dbname=repmgr host=192.168.99.202 port=6000 fallback_application_name=repmgr"
[2020-02-03 19:45:31] [DETAIL] PQping() returned "PQPING_NO_RESPONSE"
[2020-02-03 19:45:31] [INFO] sleeping 10 seconds until next reconnection attempt
[2020-02-03 19:45:41] [INFO] checking state of node 2, 5 of 6 attempts
[2020-02-03 19:45:41] [WARNING] unable to ping "user=repmgr connect_timeout=2 dbname=repmgr host=192.168.99.202 port=6000 fallback_application_name=repmgr"
[2020-02-03 19:45:41] [DETAIL] PQping() returned "PQPING_NO_RESPONSE"
[2020-02-03 19:45:41] [INFO] sleeping 10 seconds until next reconnection attempt
[2020-02-03 19:45:51] [INFO] checking state of node 2, 6 of 6 attempts
[2020-02-03 19:45:51] [WARNING] unable to ping "user=repmgr connect_timeout=2 dbname=repmgr host=192.168.99.202 port=6000 fallback_application_name=repmgr"
[2020-02-03 19:45:51] [DETAIL] PQping() returned "PQPING_NO_RESPONSE"
[2020-02-03 19:45:51] [WARNING] unable to reconnect to node 2 after 6 attempts
[2020-02-03 19:45:54] [NOTICE] witness node 3 now following new primary node 1
[2020-02-03 19:45:54] [INFO] resuming witness monitoring mode
[2020-02-03 19:45:54] [DETAIL] following new primary "node1" (ID: 1)
[2020-02-03 19:45:54] [INFO] witness monitoring connection to primary node "node1" (ID: 1)

4.节点HA2重新rejoin

[postgres@ha2 ~]$ /opt/pgsql/bin/repmgr -f /opt/repmgr.conf node rejoin -d'host=192.168.99.200 port=6000 user=repmgr dbname=repmgr connect_timeout=2' --force-rewind