承接上篇文章的PostgrSQL + repmgr的搭建部署,本篇主要讲述PostgreSQL + repmgr高可用集群集群是如何实现switchover,以下是具体场景及其操作步骤。
该命令常见的使用场景为,若没有配置自动failover,primary节点宕机后,手动将指定standby节点并提升为primary主服务器节点。
1、查看正常情况下集群状态
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------------+---------+-----------+---------------+------------+----------+----------+----------------------------------------------------------------
1 | 172-16-104-7 | primary | * running | | location1 | 100 | 13 | host=172-16-104-7 user=repmgr dbname=repmgr connect_timeout=2
2 | 172-16-104-55 | standby | running | 172-16-104-7 | location1 | 99 | 13 | host=172-16-104-55 user=repmgr dbname=repmgr connect_timeout=2
3 | 172-16-104-56 | standby | running | 172-16-104-7 | location1 | 100 | 12 | host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2
2、手动关闭primary节点,模拟primary故障宕机
[postgres@172-16-104-7 ~]$ sudo systemctl stop postgres11
3、手动在需要提升为primary节点的standby节点提升为primary节点
1) 查看当前集群信息,可以看到旧primary节点“172-16-104-7 ”已经处于不可达的状态
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------------+---------+---------------+----------------+------------+----------+----------+----------------------------------------------------------------
1 | 172-16-104-7 | primary | ? unreachable | ? | location1 | 100 | | host=172-16-104-7 user=repmgr dbname=repmgr connect_timeout=2
2 | 172-16-104-55 | standby | running | ? 172-16-104-7 | location1 | 99 | 13 | host=172-16-104-55 user=repmgr dbname=repmgr connect_timeout=2
3 | 172-16-104-56 | standby | running | ? 172-16-104-7 | location1 | 100 | 12 | host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2
4 | 172-16-104-57 | witness | * running | 172-16-104-56 | location1 | 0 | n/a | host=172.16.104.57 user=repmgr dbname=repmgr connect_timeout=2
WARNING: following issues were detected
- unable to connect to node "172-16-104-7" (ID: 1)
- node "172-16-104-7" (ID: 1) is registered as an active primary but is unreachable
- unable to connect to node "172-16-104-55" (ID: 2)'s upstream node "172-16-104-7" (ID: 1)
- unable to determine if node "172-16-104-55" (ID: 2) is attached to its upstream node "172-16-104-7" (ID: 1)
- unable to connect to node "172-16-104-56" (ID: 3)'s upstream node "172-16-104-7" (ID: 1)
- unable to determine if node "172-16-104-56" (ID: 3) is attached to its upstream node "172-16-104-7" (ID: 1)
HINT: execute with --verbose option to see connection error messages
2)手动提升“172-16-104-56”为primary节点
[postgres@172-16-104-56 extension]$ repmgr -f /pg_data/pgsql11/repmgr.conf standby promote
WARNING: 1 sibling nodes found, but option "--siblings-follow" not specified
DETAIL: these nodes will remain attached to the current primary:
172-16-104-55 (node ID: 2)
NOTICE: promoting standby to primary
DETAIL: promoting server "172-16-104-56" (ID: 3) using "/usr/local/pgsql11/bin/pg_ctl -w -D '/pg_data/pgsql11/data' promote"
waiting for server to promote.... done
server promoted
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "172-16-104-56" (ID: 3) was successfully promoted to primary
3)查看当前集群状态,可以看到172-16-104-56已经被提升为新的primary
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------------+---------+----------------------+----------------+------------+----------+----------+----------------------------------------------------------------
1 | 172-16-104-7 | primary | ? unreachable | ? | location1 | 100 | | host=172-16-104-7 user=repmgr dbname=repmgr connect_timeout=2
2 | 172-16-104-55 | standby | running | ? 172-16-104-7 | location1 | 99 | 13 | host=172-16-104-55 user=repmgr dbname=repmgr connect_timeout=2
3 | 172-16-104-56 | standby | ! running as primary | | location1 | 100 | 14 | host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2
1、集群中其他standby节点默认还是连接在已经宕机的旧primary节点,需要我们手动将这些standby节点指向新的primary节点
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf standby follow
NOTICE: attempting to find and follow current primary
INFO: local node 2 can attach to follow target node 3
DETAIL: local node's recovery point: 0/35000098; follow target node's fork point: 0/35000098
INFO: creating replication slot as user "repmgr"
NOTICE: setting node 2's upstream to node 3
NOTICE: restarting server using "sudo systemctl restart postgres11"
WARNING: node "172-16-104-55" attached in state "catchup"
NOTICE: STANDBY FOLLOW successful
DETAIL: standby attached to upstream node "172-16-104-56" (ID: 3)
2、查看集群状态信息,可以发现,目前standby节点“172-16-104-55 ”已经正常开始从“172-16-104-57”开始进行流复制同步
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------------+---------+-----------+----------------+-----------+----------+----------+----------------------------------------------------------------
1 | 172-16-104-7 | primary | - failed | ? | location1 | 100 | | host=172-16-104-7 user=repmgr dbname=repmgr connect_timeout=2
2 | 172-16-104-55 | standby | running | 172-16-104-56 | location1 | 99 | 17 | host=172-16-104-55 user=repmgr dbname=repmgr connect_timeout=2
3 | 172-16-104-56 | primary | * running | | location1 | 100 | 18 | host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2
4 | 172-16-104-57 | witness | * running | ? 172-16-104-7 | location1 | 0 | n/a | host=172.16.104.57 user=repmgr dbname=repmgr connect_timeout=2
WARNING: following issues were detected
- unable to connect to node "172-16-104-7" (ID: 1)
- unable to connect to node "172-16-104-57" (ID: 4)'s upstream node "172-16-104-7" (ID: 1)
当primary节点宕机后,后续业务稳定,我们是需要将宕机的primary主服务器节点重新加入集群并作为standby备用服务器节点进行服务
1、清理宕机节点数据目录
[postgres@172-16-104-7 ~]$ rm -rf /pg_data/pgsql11/data/*
2、重新通过repmgr克隆备服务器standby节点
[postgres@172-16-104-7 ~]$ repmgr -h 172-16-104-56 -U repmgr -d repmgr -f /pg_data/pgsql11/repmgr.conf standby clone
NOTICE: destination directory "/pg_data/pgsql11/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=172-16-104-56 user=repmgr dbname=repmgr
DETAIL: current installation size is 87 MB
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: checking and correcting permissions on existing directory "/pg_data/pgsql11/data"
INFO: creating replication slot as user "repmgr"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
/usr/local/pgsql11/bin/pg_basebackup -l "repmgr base backup" -D /pg_data/pgsql11/data -h 172-16-104-56 -p 5432 -U repmgr -X stream -S repmgr_slot_1
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: sudo systemctl start postgres11
HINT: after starting the server, you need to re-register this standby with "repmgr standby register --force" to update the existing node record
3、启动数据库
[postgres@172-16-104-7 ~]$ sudo systemctl start postgres11
4、将宕机恢复后的旧primary节点以standby的身份重新加入集群
[postgres@172-16-104-7 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf standby register --force
INFO: connecting to local node "172-16-104-7" (ID: 1)
INFO: connecting to primary database
INFO: standby registration complete
NOTICE: standby node "172-16-104-7" (ID: 1) successfully registered
1、查看switchover前的集群状态信息
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------------+---------+-----------+---------------+-----------+----------+----------+----------------------------------------------------------------
1 | 172-16-104-7 | standby | running | 172-16-104-56 | location1 | 100 | 14 | host=172-16-104-7 user=repmgr dbname=repmgr connect_timeout=2
2 | 172-16-104-55 | standby | running | 172-16-104-56 | location1 | 99 | 14 | host=172-16-104-55 user=repmgr dbname=repmgr connect_timeout=2
3 | 172-16-104-56 | primary | * running | | location1 | 100 | 14 | host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2
2、手动在需要提升为primary节点服务器上执行switchover命令
[postgres@172-16-104-7 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf standby switchover
NOTICE: executing switchover on node "172-16-104-7" (ID: 1)
WARNING: 2 sibling nodes found, but option "--siblings-follow" not specified
DETAIL: these nodes will remain attached to the current primary:
172-16-104-55 (node ID: 2)
172-16-104-57 (node ID: 4, witness server)
NOTICE: local node "172-16-104-7" (ID: 1) will be promoted to primary; current primary "172-16-104-56" (ID: 3) will be demoted to standby
NOTICE: stopping current primary node "172-16-104-56" (ID: 3)
NOTICE: issuing CHECKPOINT on node "172-16-104-56" (ID: 3)
DETAIL: executing server command "sudo systemctl stop postgres11"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/3A000028
NOTICE: promoting standby to primary
DETAIL: promoting server "172-16-104-7" (ID: 1) using "/usr/local/pgsql11/bin/pg_ctl -w -D '/pg_data/pgsql11/data' promote"
waiting for server to promote.... done
server promoted
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "172-16-104-7" (ID: 1) was successfully promoted to primary
INFO: local node 3 can attach to rejoin target node 1
DETAIL: local node's recovery point: 0/3A000028; rejoin target node's fork point: 0/3A000098
INFO: creating replication slot as user "repmgr"
NOTICE: setting node 3's upstream to node 1
WARNING: unable to ping "host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "sudo systemctl start postgres11"
WARNING: node "172-16-104-56" attached in state "startup"
NOTICE: replication slot "repmgr_slot_1" deleted on node 3
WARNING: 1 inactive replication slots detected
DETAIL: inactive replication slots:
- repmgr_slot_2 (physical)
HINT: these replication slots may need to be removed manually
NOTICE: NODE REJOIN successful
DETAIL: node 3 is now attached to node 1
NOTICE: node "172-16-104-7" (ID: 1) promoted to primary, node "172-16-104-56" (ID: 3) demoted to standby
NOTICE: switchover was successful
DETAIL: node "172-16-104-7" is now primary and node "172-16-104-56" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
3、手动将其他standby节点指向新的primary节点
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf standby follow
NOTICE: attempting to find and follow current primary
INFO: timelines are same, this server is not ahead
DETAIL: local node lsn is 0/3A01E548, follow target lsn is 0/3A01E548
INFO: creating replication slot as user "repmgr"
NOTICE: setting node 2's upstream to node 1
NOTICE: restarting server using "sudo systemctl restart postgres11"
NOTICE: replication slot "repmgr_slot_2" deleted on node 3
NOTICE: STANDBY FOLLOW successful
DETAIL: standby attached to upstream node "172-16-104-7" (ID: 1)
4、查看switchover变更后的集群信息
[postgres@172-16-104-55 ~]$ repmgr -f /pg_data/pgsql11/repmgr.conf cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+---------------+---------+-----------+---------------+-----------+----------+----------+----------------------------------------------------------------
1 | 172-16-104-7 | primary | * running | | location1 | 100 | 15 | host=172-16-104-7 user=repmgr dbname=repmgr connect_timeout=2
2 | 172-16-104-55 | standby | running | 172-16-104-7 | default | 99 | 15 | host=172-16-104-55 user=repmgr dbname=repmgr connect_timeout=2
3 | 172-16-104-56 | standby | running | 172-16-104-7 | default | 100 | 14 | host=172-16-104-56 user=repmgr dbname=repmgr connect_timeout=2