repmgr之添加witness并switchover

repmgr之添加witness并switchover"/>

repmgr之添加witness并switchover

#查看原集群信息repmgr -f repmgr/repmgr5.2.1.conf cluster showID | Name | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                             
----+------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------1  | db1  | primary | * running |          | default  | 100      | 1        | host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=22  | db2  | standby |   running | db1      | default  | 100      | 1        | host=db2 user=repmgr dbname=repmgr port=1921 connect_timeout=2#解压安装repmgr
tar -zxvf repmgr-5.2.1.tar.gz
cd repmgr-5.2.1
./configure --prefix=/opt/pgsql12/
make  && make install#初始化并启动数据库
initdb -D /opt/pgdata_witness/pg_root/
pg_ctl start -D /opt/pgdata_witness/pg_root/#添加witness
repmgr -f /home/postgres/repmgr/repmgr5.2.1.conf witness register -h 10.4.9.166  -d repmgr -U repmgr
INFO: connecting to witness node "db3" (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 "db3" (ID: 3) successfully registered
postgres@nano-dev-database1-> repmgr -f /home/postgres/repmgr/repmgr5.2.1.conf cluster showID | Name | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                             
----+------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------1  | db1  | primary | * running |          | default  | 100      | 1        | host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=22  | db2  | standby |   running | db1      | default  | 100      | 1        | host=db2 user=repmgr dbname=repmgr port=1921 connect_timeout=23  | db3  | witness | * running | db1      | default  | 0        | n/a      | host=db3 user=repmgr dbname=repmgr port=1922 connect_timeout=2#主备switchover
repmgr standby switchover -f /home/postgres/repmgr/repmgr5.2.1.conf --siblings-follow --dry-run
NOTICE: checking switchover on node "db2" (ID: 2) in --dry-run mode
INFO: SSH connection to host "db1" succeeded
INFO: able to execute "repmgr" on remote host "db1"
INFO: all sibling nodes are reachable via SSH
INFO: 2 walsenders required, 10 available
INFO: demotion candidate is able to make replication connection to promotion candidate
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
INFO: 2 replication slots required, 10 available
INFO: would pause repmgrd on node "db1" (ID 1)
INFO: would pause repmgrd on node "db2" (ID 2)
INFO: would pause repmgrd on node "db3" (ID 3)
NOTICE: local node "db2" (ID: 2) would be promoted to primary; current primary "db1" (ID: 1) would be demoted to standby
INFO: following shutdown command would be run on node "db1":"/opt/pgsql12/bin/pg_ctl  -D '/opt/pgdata/pg_root' -W -m fast stop"
INFO: parameter "shutdown_check_timeout" is set to 60 seconds
INFO: prerequisites for executing STANDBY SWITCHOVER are met#执行切换
repmgr standby switchover -f /home/postgres/repmgr/repmgr5.2.1.conf --siblings-follow
NOTICE: executing switchover on node "db2" (ID: 2)
NOTICE: local node "db2" (ID: 2) will be promoted to primary; current primary "db1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "db1" (ID: 1)
NOTICE: issuing CHECKPOINT on node "db1" (ID: 1) 
DETAIL: executing server command "/opt/pgsql12/bin/pg_ctl  -D '/opt/pgdata/pg_root' -W -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 4/84000028
NOTICE: promoting standby to primary
DETAIL: promoting server "db2" (ID: 2) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "db2" (ID: 2) was successfully promoted to primary
INFO: local node 1 can attach to rejoin target node 2
DETAIL: local node's recovery point: 4/84000028; rejoin target node's fork point: 4/840000A0
INFO: creating replication slot as user "repmgr"
NOTICE: setting node 1's upstream to node 2
WARNING: unable to ping "host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=2"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/opt/pgsql12/bin/pg_ctl  -w -D '/opt/pgdata/pg_root' start"
NOTICE: replication slot "repmgr_slot_2" deleted on node 1
NOTICE: NODE REJOIN successful
DETAIL: node 1 is now attached to node 2
NOTICE: node  "db2" (ID: 2) promoted to primary, node "db1" (ID: 1) demoted to standby
NOTICE: executing STANDBY FOLLOW on 1 of 1 siblings
INFO:  node 3 received notification to follow node 2
INFO: STANDBY FOLLOW successfully executed on all reachable sibling nodes
NOTICE: switchover was successful
DETAIL: node "db2" is now primary and node "db1" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully#查看集群状态信息
repmgr -f repmgr/repmgr5.2.1.conf cluster show 
ID | Name | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                             
----+------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------1  | db1  | standby |   running | db2      | default  | 100      | 1        | host=db1 user=repmgr dbname=repmgr port=1921 connect_timeout=22  | db2  | primary | * running |          | default  | 100      | 2        | host=db2 user=repmgr dbname=repmgr port=1921 connect_timeout=23  | db3  | witness | * running | db2      | default  | 0        | n/a      | host=db3 user=repmgr dbname=repmgr port=1922 connect_timeout=2#切换后，逻辑复制槽不可用，记得要在新的主节点创建复制槽
postgres=# select * from pg_replication_slots ;slot_name              |      plugin      | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn 
| confirmed_flush_lsn 
-------------------------------------+------------------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------
+---------------------pgl_logic_logic_pub_sub_replica01   | pglogical_output | logical   |  16651 | logic    | f         | f      |            |      |        12426 | 4/83A086E0  
| 4/83A086E0sub                                 | pgoutput         | logical   |  16450 | hank     | f         | f      |            |      |        12426 | 4/83A086E0  
| 4/83A086E0pgl_logic_logic_pub_logic_su0d01101 | pglogical_output | logical   |  16651 | logic    | f         | f      |            |      |        12426 | 4/83A086E0  
| 4/83A086E0
(3 rows)