PG基于repmgr实现自动和手动故障切换

PG基于repmgr实现自动和手动故障切换

1、准备机器（1主2从1withness）

主库

[root@wcbpg ~]# docker rm -f wcbrepmgr64361
[root@wcbpg ~]# docker run -d --name wcbrepmgr64361 -h wcbrepmgr64361 \
  --net=pg-network --ip 172.72.6.61 \
  -p 64361:5432  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0 \
  /usr/sbin/init
[root@wcbpg ~]# docker network connect bridge wcbrepmgr64361

从库1

[root@wcbpg ~]# docker rm -f wcbrepmgr64362
[root@wcbpg ~]# docker run -d --name wcbrepmgr64362 -h wcbrepmgr64362 \
  --net=pg-network --ip 172.72.6.62 \
  -p 64362:5432  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0 \
  /usr/sbin/init
[root@wcbpg ~]# docker network connect bridge wcbrepmgr64362

从库2

[root@wcbpg ~]# docker rm -f wcbrepmgr64363
[root@wcbpg ~]# docker run -d --name wcbrepmgr64363 -h wcbrepmgr64363 \
  --net=pg-network --ip 172.72.6.63 \
  -p 64363:5432  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0 \
  /usr/sbin/init
[root@wcbpg ~]# docker network connect bridge wcbrepmgr64363

withness库

[root@wcbpg ~]#docker rm -f wcbrepmgr64364
[root@wcbpg ~]#docker run -d --name wcbrepmgr64364 -h wcbrepmgr64364 \
  --net=pg-network --ip 172.72.6.64 \
  -p 64364:5432  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0 \
  /usr/sbin/init
[root@wcbpg ~]#docker network connect bridge wcbrepmgr64364

重启这四台容器

[root@wcbpg ~]# docker restart wcbrepmgr64364 wcbrepmgr64363 wcbrepmgr64362 wcbrepmgr64361
[root@wcbpg ~]# docker ps |grep wcbrepmgr
46bd4758ba01        registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0   "/usr/sbin/init"         2 minutes ago       Up 53 seconds       5433-5435/tcp, 0.0.0.0:64364->5432/tcp                                                         wcbrepmgr64364
bbc64f79cfc7        registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0   "/usr/sbin/init"         3 minutes ago       Up 42 seconds       5433-5435/tcp, 0.0.0.0:64363->5432/tcp                                                         wcbrepmgr64363
c7c42f5ca4d2        registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0   "/usr/sbin/init"         4 minutes ago       Up 30 seconds       5433-5435/tcp, 0.0.0.0:64362->5432/tcp                                                         wcbrepmgr64362
577c72695a92        registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:1.0   "/usr/sbin/init"         6 minutes ago       Up 18 seconds       5433-5435/tcp, 0.0.0.0:64361->5432/tcp                                                         wcbrepmgr64361

2、安装repmgr

官网：https://repmgr.org/

官方文档：https://repmgr.org/docs/current/index.html

所有历史版本：https://repmgr.org/downloads.html

GitHub：https://github.com/EnterpriseDB/repmgr

从https://repmgr.org/download/repmgr-5.2.1.tar.gz下载好安装包后上传到Linux。

拷贝文件到容器内

[root@wcbpg /]# docker cp repmgr-5.2.1.tar.gz wcbrepmgr64361:/home/pg13/
[root@wcbpg /]# docker cp repmgr-5.2.1.tar.gz wcbrepmgr64362:/home/pg13/
[root@wcbpg /]# docker cp repmgr-5.2.1.tar.gz wcbrepmgr64363:/home/pg13/
[root@wcbpg /]# docker cp repmgr-5.2.1.tar.gz wcbrepmgr64364:/home/pg13/

依次在所有PG节点环境上安装repmgr

[root@wcbpg ~]# docker exec -it wcbrepmgr64364 bash
[root@wcbrepmgr64364 /]# su - pg13
[pg13@wcbrepmgr64364 ~]$ ls
repmgr-5.2.1.tar.gz
[pg13@wcbrepmgr64364 ~]$ tar -zxvf repmgr-5.2.1.tar.gz
[pg13@wcbrepmgr64364 ~]$ cd repmgr-5.2.1/
[pg13@wcbrepmgr64364 repmgr-5.2.1]$ ./configure 
[pg13@wcbrepmgr64364 repmgr-5.2.1]$ make -j 8 && make install
[pg13@wcbrepmgr64364 repmgr-5.2.1]$ repmgr --help

配置四台机器的互信

下载sshUserSetup.sh文件并上传到Linux

[root@wcbpg ~]# docker cp sshUserSetup.sh  wcbrepmgr64361:/root/

只在主库61节点上，以root用户执行：sshUserSetup.sh依次输入：yes，回车和密码即可。

[root@wcbrepmgr64361 ~]# pwd
/root
[root@wcbrepmgr64361 ~]# ls -lth
total 40K
-rw-r--r-- 1 root root  33K Nov 27 21:59 sshUserSetup.sh
-rw------- 1 root root 3.3K Dec  4  2018 anaconda-ks.cfg
[root@wcbrepmgr64361 ~]#chmod +x sshUserSetup.sh
[root@wcbrepmgr64361 ~]# ./sshUserSetup.sh -user pg13  -hosts "172.72.6.61 172.72.6.62 172.72.6.63 172.72.6.64" -advanced exverify -confirm
The output of this script is also logged into /tmp/sshUserSetup_2022-01-01-11-59-01.log
Hosts are 172.72.6.61 172.72.6.62 172.72.6.63 172.72.6.64
user is pg13
Platform:- Linux 
Checking if the remote hosts are reachable
PING 172.72.6.61 (172.72.6.61) 56(84) bytes of data.
64 bytes from 172.72.6.61: icmp_seq=1 ttl=64 time=0.068 ms
64 bytes from 172.72.6.61: icmp_seq=2 ttl=64 time=0.074 ms
64 bytes from 172.72.6.61: icmp_seq=3 ttl=64 time=0.078 ms
64 bytes from 172.72.6.61: icmp_seq=4 ttl=64 time=0.102 ms
64 bytes from 172.72.6.61: icmp_seq=5 ttl=64 time=0.085 ms

--- 172.72.6.61 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4001ms
rtt min/avg/max/mdev = 0.068/0.081/0.102/0.014 ms
PING 172.72.6.62 (172.72.6.62) 56(84) bytes of data.
64 bytes from 172.72.6.62: icmp_seq=1 ttl=64 time=0.254 ms
64 bytes from 172.72.6.62: icmp_seq=2 ttl=64 time=0.163 ms
64 bytes from 172.72.6.62: icmp_seq=3 ttl=64 time=0.123 ms
64 bytes from 172.72.6.62: icmp_seq=4 ttl=64 time=0.094 ms
64 bytes from 172.72.6.62: icmp_seq=5 ttl=64 time=0.160 ms

--- 172.72.6.62 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4003ms
rtt min/avg/max/mdev = 0.094/0.158/0.254/0.056 ms
PING 172.72.6.63 (172.72.6.63) 56(84) bytes of data.
64 bytes from 172.72.6.63: icmp_seq=1 ttl=64 time=0.447 ms
64 bytes from 172.72.6.63: icmp_seq=2 ttl=64 time=0.154 ms
64 bytes from 172.72.6.63: icmp_seq=3 ttl=64 time=0.134 ms
64 bytes from 172.72.6.63: icmp_seq=4 ttl=64 time=0.147 ms
64 bytes from 172.72.6.63: icmp_seq=5 ttl=64 time=0.170 ms

--- 172.72.6.63 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4008ms
rtt min/avg/max/mdev = 0.134/0.210/0.447/0.119 ms
PING 172.72.6.64 (172.72.6.64) 56(84) bytes of data.
64 bytes from 172.72.6.64: icmp_seq=1 ttl=64 time=0.446 ms
64 bytes from 172.72.6.64: icmp_seq=2 ttl=64 time=0.138 ms
64 bytes from 172.72.6.64: icmp_seq=3 ttl=64 time=0.175 ms
64 bytes from 172.72.6.64: icmp_seq=4 ttl=64 time=0.163 ms
64 bytes from 172.72.6.64: icmp_seq=5 ttl=64 time=0.097 ms

--- 172.72.6.64 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4013ms
rtt min/avg/max/mdev = 0.097/0.203/0.446/0.125 ms
Remote host reachability check succeeded.
The following hosts are reachable: 172.72.6.61 172.72.6.62 172.72.6.63 172.72.6.64.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
firsthost 172.72.6.61
numhosts 4
The script will setup SSH connectivity from the host wcbrepmgr64361 to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host wcbrepmgr64361
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
Confirmation provided on the command line

The user chose yes
Please specify if you want to specify a passphrase for the private key this script will create for the local host. Passphrase is used to encrypt the private key and makes SSH much more secure. Type 'yes' or 'no' and then press enter. In case you press 'yes', you would need to enter the passphrase whenever the script executes ssh or scp. no 
The estimated number of times the user would be prompted for a passphrase is 8. In addition, if the private-public files are also newly created, the user would have to specify the passphrase on one additional occasion. 
Enter 'yes' or 'no'.
yes

The user chose yes
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /root/.ssh/config, it would be backed up to /root/.ssh/config.backup.
Removing old private/public keys on local host
Running SSH keygen on local host
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:JEwRyjSIrpcJj86qYBxtYPEFcVKipWFbeYGBBme1iBY root@wcbrepmgr64361
The key's randomart image is:
+---[RSA 1024]----+
|oE+@@==o         |
|o*%*=B           |
|+B..= o .        |
|+.o    o         |
|.= =    S        |
|+ B              |
|++               |
|oo               |
|=                |
+----[SHA256]-----+
Creating .ssh directory and setting permissions on remote host 172.72.6.61
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR pg13. THIS IS AN SSH REQUIREMENT.
The script would create ~pg13/.ssh/config file on remote host 172.72.6.61. If a config file exists already at ~pg13/.ssh/config, it would be backed up to ~pg13/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host 172.72.6.61.
Warning: Permanently added '172.72.6.61' (ECDSA) to the list of known hosts.
pg13@172.72.6.61's password: 
Done with creating .ssh directory and setting permissions on remote host 172.72.6.61.
Creating .ssh directory and setting permissions on remote host 172.72.6.62
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR pg13. THIS IS AN SSH REQUIREMENT.
The script would create ~pg13/.ssh/config file on remote host 172.72.6.62. If a config file exists already at ~pg13/.ssh/config, it would be backed up to ~pg13/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host 172.72.6.62.
Warning: Permanently added '172.72.6.62' (ECDSA) to the list of known hosts.
pg13@172.72.6.62's password: 
Done with creating .ssh directory and setting permissions on remote host 172.72.6.62.
Creating .ssh directory and setting permissions on remote host 172.72.6.63
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR pg13. THIS IS AN SSH REQUIREMENT.
The script would create ~pg13/.ssh/config file on remote host 172.72.6.63. If a config file exists already at ~pg13/.ssh/config, it would be backed up to ~pg13/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host 172.72.6.63.
Warning: Permanently added '172.72.6.63' (ECDSA) to the list of known hosts.
pg13@172.72.6.63's password: 
Done with creating .ssh directory and setting permissions on remote host 172.72.6.63.
Creating .ssh directory and setting permissions on remote host 172.72.6.64
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR pg13. THIS IS AN SSH REQUIREMENT.
The script would create ~pg13/.ssh/config file on remote host 172.72.6.64. If a config file exists already at ~pg13/.ssh/config, it would be backed up to ~pg13/.ssh/config.backup.
The user may be prompted for a password here since