PG12.9-Repmgr5.1.0-vip-3节点部署

Centos 7.6
PG 12.9
repmgr 5.1.0

1.系统环境准备

   主机名  	  ip地址    	 角色 	端口 
  enmo-01	172.20.10.1	 主库 	6000
  enmo-02	172.20.10.2	 备库 	6000
  enmo-03	172.20.10.3	 备库 	6000

准备将172.20.10.6设置为vip

修改网卡(刷新一下 mac 地址)

需要所有的机器在同一个网段，防止 vip 无法绑定生效
vi /etc/sysconfig/network-scripts/ifcfg-ens33

ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=static
IPV6INIT=no
IPADDR=172.20.10.1
NETMASK=255.255.255.0
GATEWAY=172.20.10.10
DNS=8.8.8.8

配置 hosts

 vi /etc/ssh/sshd_config
 UseDNS 设置为 no  
 
 修改主机名 
 hostnamectl set-hostname enmo-01 
 hostnamectl set-hostname enmo-02
 hostnamectl set-hostname enmo-03 
 
 修改 hosts 文件 
 echo "172.20.10.1 enmo-01  
 172.20.10.2 enmo-02
 172.20.10.3 enmo-03
 " >>/etc/hosts 

同步时间

 所有服务器的时区及时间需要保持同步 
 timedatectl set-timezone Asia/Shanghai 

关闭防火墙和 selinux

systemctl stop firewalld 
systemctl disable firewalld 

vi /etc/selinux/config 
SELINUX=disabled 

用户配置

创建安装用户
groupadd dba -g 1000 
useradd postgres -g 1000 -u 1000 

添加 sudo 权限
启动服务及管理 VIP 使用，权限可细化，忽略此步骤 vip 漂移会失败
visudo -f /etc/sudoers 
postgres ALL=(ALL) NOPASSWD:ALL

reboot now

重启使sudo生效

2.安装主数据库（源码安装）

此处仅主库装数据库，备库需要有数据库软件。

安装依赖

yum install -y  openssl openssl-devel readline readline-devel zlib-devel gcc flex

切换到postgres用户

su - postgres

获取并解压包

wget https://ftp.postgresql.org/pub/source/v12.9/postgresql-12.9.tar.gz  
tar -zxf postgresql-12.9.tar.gz

–进入目录

cd postgresql-12.9

–编译环境检查

./configure --prefix=/home/postgres/soft --with-openssl --with-pgport=6000

(最后出现makefile)

–编译安装

make world -j24
make install-world -j24

–初始化数据库数据目录到/home/postgres/data并启用checksum检验

/home/postgres/soft/bin/initdb -D /home/postgres/data -k

–启动数据库

/home/postgres/soft/bin/pg_ctl start -D /home/postgres/data -l /tmp/logfile

–配置环境变量.bashrc

vi .bashrc
export PGPORT=6000
export PGUSER=postgres
export PGHOME=/home/postgres/soft
export PGDATA=/home/postgres/data
export PATH=${PGHOME}/bin:${PATH}
LD_LIBRARY_PATH=$PGHOME/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64:$LD_LIBRARY_PATH

–读取环境变量

source .bashrc

–登录到数据库

psql -d postgres

–创建超级用户 postgres

CREATE USER postgres WITH SUPERUSER PASSWORD '1QAZ@WSX';

–修改端口（如果./configure没指定–with-pgport）

alter system set port=6000;

select pg_reload_conf();

3.数据库配置

su - postgres

mkdir -p /home/postgres/archive/wal

cluster_name='enmo_6001'
listen_addresses='0.0.0.0'
port=6000
wal_log_hints=on
logging_collector=on
logging_collector=on
log_filename='pg_log_%u.log'
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
wal_keep_segments=100
archive_mode = on
archive_command = 'cp %p /home/postgres/archive/wal/%f'
wal_level = 'logical'
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

修改参数，其余参数根据环境进行调整。

重启数据库生效

pg_ctl restart -D $PGDATA -l /tmp/logfile

4.节点间配置互信

–配置集群内postgres用户之间互信

–enmo-01

su - postgres
ssh-keygen -t rsa
cd ~/.ssh
ssh-copy-id -i postgres@enmo-02

–enmo-02

su - postgres
ssh-keygen -t rsa
cd ~/.ssh
cat *.pub >>  authorized_keys
scp authorized_keys postgres@enmo-01:/home/postgres/.ssh

上述过程为配置两节点间的postgres的用户的互信。两两节点之间需要做一次。用date 检验，确保集群内节点能通过postgres用户互相访问。

ssh enmo-01 date;
ssh enmo-02 date;
ssh enmo-03 date;

5.安装repmgr（源码安装）并配置白名单

所有节点都需要安装repmgr工具

–解压压缩包

su - postgres
tar -zxf repmgr-5.1.0.tar.gz
cd repmgr-5.1.0
#环境变量
pg_config验证

–编译安装repmgr

su - postgres
cd /home/postgres/repmgr-5.1.0
./configure
make -j24
make install -j24

–查看版本

/home/postgres/repmgr-5.1.0/repmgr --version 

–安装repmgr扩展

$ psql -d postgres

#主库创建 repmgr 库存储元数据
create user repmgr superuser password '123456';
create database repmgr owner repmgr;

#在postgresql.conf的shared_preload_libraries加上repmgr
vim $PGDATA/postgresql.conf
shared_preload_libraries = 'repmgr'

\c repmgr  
create extension repmgr; 

–pg_hba配置

node11 节点配置 pg_hba.conf 文件
vi $PGDATA/pg_hba.conf

local repmgr repmgr trust
host repmgr repmgr 172.20.10.1/32 trust
host repmgr repmgr 172.20.10.2/32 trust
host repmgr repmgr 172.20.10.3/32 trust
host repmgr repmgr 172.20.10.6/32 trust
host all all 172.20.10.1/24 scram-sha-256

host replication repmgr 172.20.10.1/32 trust
host replication repmgr 172.20.10.2/32 trust
host replication repmgr 172.20.10.3/32 trust
host replication repmgr 172.20.10.6/32 trust

–这边将repmgr之间用trust，其他ip其他用户登录需要用密码。如果想让repmgr在此处也为密码校验，则需要将trust改为md5或者scram-sha-256并在本地创建.pgpass文件,操作如下：

su - postgres
touch ~/.pgpass  
chmod 0600 ~/.pgpass 

echo "172.20.10.1:6000:repmgr:repmgr:123456 
172.20.10.2:6000:repmgr:repmgr:123456
172.20.10.3:6000:repmgr:repmgr:123456 
172.20.10.4:6000:repmgr:repmgr:123456 ">> ~/.pgpass 

使用密码校验需要注意密码的加密格式，留意pg_authid系统表和password_encryption参数。

–实例检查

只保留主节点运行 pg 实例，如果其他节点有实例运行，停止后删除 pgdata 目录

其他所有节点执行如下操作
su - postgres 
pg_ctl stop -D $PGDATA
rm -rf $PGDARA/*

6.主库repmgr配置及注册

（1）主节点创建repmgr.conf

touch /home/postgres/repmgr.conf 
vi /home/postgres/repmgr.conf 

node_id=1
node_name='enmo_6001'
conninfo='host=172.20.10.1 port=6000 user=repmgr dbname=repmgr 
connect_timeout=2'
data_directory='/data/pgdata'
replication_user='repmgr'
replication_type='physical'
pg_bindir='/opt/postgresql-12.9/bin'
monitoring_history=yes
monitor_interval_secs=5
log_level='debug'
log_file='/home/postgres/repmgr.log'
failover='automatic'
connection_check_type=ping
reconnect_attempts=3
reconnect_interval=10 
promote_command='/home/postgres/repmgr_promote.sh'
follow_command='/home/postgres/repmgr_follow.sh %n'

上面主要修改的部分为：

node-id  三个节点为1，2，3，用于切换的id
node_name  主要为标识，设置与数据库cluster_name相同
conninfo  设置本地用于连接的repmgr数据库等信息

（2）配置提升为主库需要的 repmgr_promote.sh 脚本

#! /bin/bash

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 2 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.2 "/bin/sudo /usr/sbin/ip addr del 172.10.20.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 2 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 3 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.3 "/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 3 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: promote start" >> /home/postgres/repmgr.log
/home/postgres/repmgr-5.1.0/repmgr standby promote -f /home/postgres/repmgr.conf --log-to-file
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: promote finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: add VIP start" >> /home/postgres/repmgr.log 
/bin/sudo /usr/sbin/ifconfig ens33:0 172.20.10.6 netmask 255.255.255.0

上边几部分分别为

下掉2，3节点的vip
提升本地数据库角色为主库
在本地挂上vip

（3）配置赋予新主跟随的 repmgr_follow.sh 脚本

#! /bin/bash

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 1 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.1 "/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 1 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: follow %n" >> /home/postgres/repmgr.log 
/home/postgres/repmgr-5.1.0/repmgr standby follow -f /home/postgres/repmgr.conf --upstream-node-id=%n --log-to-file

上边两部分为

下掉本地的vip
让本地备机跟随新主

给两个脚本权限

chmod 755 repmgr_promote.sh 
chmod 755 repmgr_follow.sh 

（4）主库注册

/home/postgres/repmgr-5.1.0/repmgr -f /home/postgres/repmgr.conf primary register 

INFO: connecting to primary database…

DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr

host=172.20.10.1 port=6000 fallback_application_name=repmgr"

INFO: “repmgr” extension is already installed

NOTICE: primary node record (ID: 11) registered

查看集群状态，此时集群里只有一个主库

/home/postgres/repmgr-5.1.0/repmgr -f /home/postgres/repmgr.conf cluster show

7.备库repmgr配置及从库注册

上面已经说到所有的节点都需要安装repmgr

如下为2，3节点的配置文件及脚本。

节点2：

（1）主节点创建repmgr.conf

touch /home/postgres/repmgr.conf 
vi /home/postgres/repmgr.conf 

node_id=2
node_name='enmo_6002'
conninfo='host=172.20.10.2 port=6000 user=repmgr dbname=repmgr 
connect_timeout=2'
data_directory='/data/pgdata'
replication_user='repmgr'
replication_type='physical'
repmgr_bindir='/home/postgres/repmgr-5.1.0'
pg_bindir='/opt/postgresql-12.9/bin'
monitoring_history=yes
monitor_interval_secs=5
log_level='debug'
log_file='/home/postgres/repmgr.log'
failover='automatic'
connection_check_type=ping
reconnect_attempts=3
reconnect_interval=10 
promote_command='/home/postgres/repmgr_promote.sh'
follow_command='/home/postgres/repmgr_follow.sh %n'

（2）配置提升为主库需要的 repmgr_promote.sh 脚本

#! /bin/bash

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 1 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.1 "/bin/sudo /usr/sbin/ip addr del 172.10.20.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 1 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 3 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.3 "/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 3 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: promote start" >> /home/postgres/repmgr.log
/home/postgres/repmgr-5.1.0/repmgr standby promote -f /home/postgres/repmgr.conf --log-to-file
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: promote finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: add VIP start" >> /home/postgres/repmgr.log 
/bin/sudo /usr/sbin/ifconfig ens33:0 172.20.10.6 netmask 255.255.255.0

（3）配置赋予新主跟随的 repmgr_follow.sh 脚本

#! /bin/bash

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 2 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.2 "/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 2 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: follow %n" >> /home/postgres/repmgr.log 
/home/postgres/repmgr-5.1.0/repmgr standby follow -f /home/postgres/repmgr.conf --upstream-node-id=%n --log-to-file

节点3：

（1）主节点创建repmgr.conf

touch /home/postgres/repmgr.conf 
vi /home/postgres/repmgr.conf 

node_id=3
node_name='enmo_6003'
conninfo='host=172.20.10.3 port=6000 user=repmgr dbname=repmgr 
connect_timeout=2'
data_directory='/data/pgdata'
replication_user='repmgr'
replication_type='physical'
repmgr_bindir='/home/postgres/repmgr-5.1.0'
pg_bindir='/opt/postgresql-12.9/bin'
monitoring_history=yes
monitor_interval_secs=5
log_level='debug'
log_file='/home/postgres/repmgr.log'
failover='automatic'
connection_check_type=ping
reconnect_attempts=3
reconnect_interval=10 
promote_command='/home/postgres/repmgr_promote.sh'
follow_command='/home/postgres/repmgr_follow.sh %n'

（2）配置提升为主库需要的 repmgr_promote.sh 脚本

#! /bin/bash

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 1 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.1 "/bin/sudo /usr/sbin/ip addr del 172.10.20.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 1 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 2 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.2 "/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 2 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: promote start" >> /home/postgres/repmgr.log
/home/postgres/repmgr-5.1.0/repmgr standby promote -f /home/postgres/repmgr.conf --log-to-file
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: promote finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: add VIP start" >> /home/postgres/repmgr.log 
/bin/sudo /usr/sbin/ifconfig ens33:0 172.20.10.6 netmask 255.255.255.0

（3）配置赋予新主跟随的 repmgr_follow.sh 脚本

#! /bin/bash

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 3 start" >> /home/postgres/repmgr.log
/bin/ssh -t postgres@172.20.10.3 "/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0"
echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: del VIP in 3 finish" >> /home/postgres/repmgr.log

echo "["`date "+%Y-%m-%d %H:%M:%S"`"]: follow %n" >> /home/postgres/repmgr.log 
/home/postgres/repmgr-5.1.0/repmgr standby follow -f /home/postgres/repmgr.conf --upstream-node-id=%n --log-to-file

给两个脚本权限

chmod 755 repmgr_promote.sh 
chmod 755 repmgr_follow.sh 

（4）两个备库分别从主库clone数据，相当于搭建流复制（要加入集群的备机节点执行，确保本地有数据目录，且数据目录下为空）

/home/postgres/repmgr-5.1.0/repmgr -h 172.10.10.1 -p6000 -U repmgr -d repmgr -f /home/postgres/repmgr.conf standby clone 

（5）修改两个备库的 配置参数

修改 postgresql.conf 
vi $PGDATA/postgresql.conf  
cluster_name='enmo_6002' 

vi $PGDATA/postgresql.conf  
cluster_name='enmo_6003' 

（6）启动及注册

两个备库分别拉起

pg_ctl start -D $PGDATA

两个备库分别执行,进行注册

/home/postgres/repmgr-5.1.0/repmgr -f /home/postgres/repmgr.conf standby register 

8.所有节点启动守护进程repmgrd

/home/postgres/repmgr-5.1.0/repmgrd -f /home/postgres/repmgr.conf -d

repmgrd守护进程主要做对集群节点的监控

9.流复制状态和集群状态检查

主备库分别查看 wal 进程 主库有 walsender 备库有 walreceiver

ps -ef | grep wal

查看集群状态,所有节点都可以通过repmgr查看集群状态

/home/postgres/repmgr-5.1.0/repmgr -f /home/postgres/repmgr.conf cluster show

DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.2 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.3 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
 ID | Name        | Role    | Status    | Upstream    | Location | Priority | Timeline | Connection string
----+-------------+---------+-----------+-------------+----------+----------+----------+------------------------------------------------------------------------
 1  | enmo_6001 | primary | * running |             | default  | 100      | 1        | host=172.20.10.1 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 2  | enmo_6002 | standby |   running | enmo_6001 | default  | 100      | 1        | host=172.20.10.2 port=6000 user=repmgr dbname=repmgr connect_timeout=2
 3  | enmo_6003 | standby |   running | enmo_6001 | default  | 100      | 1        | host=172.20.10.3 port=6000 user=repmgr dbname=repmgr connect_timeout=2


/home/postgres/repmgr-5.1.0/repmgr -f /home/postgres/repmgr.conf service status

DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.2 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.3 port=6000 fallback_application_name=repmgr"
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr host=172.20.10.1 port=6000 fallback_application_name=repmgr"
 ID | Name        | Role    | Status    | Upstream    | repmgrd | PID   | Paused? | Upstream last seen
----+-------------+---------+-----------+-------------+---------+-------+---------+--------------------
 1  | enmo_6001 | primary | * running |             | running | 15001 | no      | n/a
 2  | enmo_6002 | standby |   running | enmo_6001 | running | 15311 | no      | 2 second(s) ago
 3  | enmo_6003 | standby |   running | enmo_6001 | running | 16068 | no      | 4 second(s) ago

主库查看流复制状态

postgres=# select * from pg_stat_replication;

  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  |

flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time

-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+-

-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------

 21781 |    16385 | repmgr  | enmo_6002      | 172.20.10.2 |                 |       37700 | 2021-11-30 11:26:55.584587+08 |              | streaming | 0/25003338 | 0/25003338 |

0/25003338 | 0/25003338 |           |           |            |             1 | sync       | 2021-11-30 14:40:23.214424+08

 21880 |    16385 | repmgr  | enmo_6003      | 172.20.10.3 |                 |       63746 | 2021-11-30 11:27:53.53565+08  |              | streaming | 0/25003338 | 0/25003338 |

0/25003338 | 0/25003338 |           |           |            |             0 | async      | 2021-11-30 14:40:20.465075+08

(2 rows)

10.手动给主库挂载vip

/bin/sudo /usr/sbin/ifconfig ens33:0 172.20.10.6 netmask 255.255.255.0

搭建完毕！

附录：相关命令

上vip

/bin/sudo /usr/sbin/ifconfig ens33:0 172.20.10.6 netmask 255.255.255.0

下vip

/bin/sudo /usr/sbin/ip addr del 172.20.10.6/24 dev ens33:0

主库注册

repmgr -f /home/postgres/repmgr.conf primary register

备库注册

repmgr -f /home/postgres/repmgr.conf standby register 

备库从主库clone（相当于全量build）

repmgr -h 172.20.10.1 -p6000 -U repmgr -d repmgr -f /home/postgres/repmgr.conf standby clone 

相当于增量build，如果先加上–dry-run，表示先调试，但不执行

repmgr node rejoin -d 'host=172.20.10.1 user=repmgr dbname=repmgr connect_timeout=2' --force-rewind  --verbose

查看集群状态

repmgr -f /home/postgres/repmgr.conf cluster show
repmgr -f /home/postgres/repmgr.conf service status

启动守护进程

repmgrd -f /home/postgres/repmgr.conf -d

switchover（备库执行）

repmgr -f /home/postgres/repmgr.conf standby switchover -U repmgr  --verbose