PostgreSql repmgr 高可用部署

1. 服务器配置

1.1 三台服务器配置ip(添加至/etc/hosts文件)

192.168.100.110 master
192.168.100.111 slave1
192.168.100.112 slave2

1.2 三台服务器配置互信

ssh-keygen -t rsa
for i in 192.168.100.110 192.168.100.111 192.168.100.112;do ssh-copy-id -i $i;done

2. 三台服务器分别源码安装pg12.4

https://blog.csdn.net/songyundong1993/article/details/120053276

3. 三台服务器设置环境变量

root 用户下增加环境变量设置

export PGHOME=/usr/local/pgsql/
export PGUSER=postgres
export PGPORT=5432
export PGDATA=/app/pgsql/data
export PGLOG=/app/pgsql/log/postgresql.log
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

4. 三台服务器流复制参数配置

cd /app/pgsql/data
vi postgresql.conf

listen_addresses = '*'
max_wal_senders = 10
max_replication_slots = 10  
wal_level = replica 
hot_standby = on
wal_log_hints = on
full_page_writes=on
shared_preload_libraries = 'repmgr'
wal_keep_segments=100
archive_mode = on 
archive_command = 'test ! -f /app/pgsql/archive/%f && cp %p /app/pgsql/archive/%f'

5. 三台服务器 repmgr 配置

5.1 三个节点均安装 repmgr

--下载并解压
wget -c https://repmgr.org/download/repmgr-5.2.1.tar.gz
tar -zxvf repmgr-5.2.1.tar.gz -C /usr/local/pgsql/contrib
cd /usr/local/pgsql/contrib

--编译安装
mv repmgr-5.2.1 repmgr
cd repmgr
yum install flex
./configure && make install

5.2 参数文件配置

5.2.1 master 节点

vi /etc/repmgr.conf

#repmgr基本配置信息
node_id=1
node_name='master'
conninfo='host=192.168.100.110 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/app/pgsql/data'

#repmgr日志配置
log_level=INFO                          
log_facility=STDERR                     
log_file='/app/pgsql/log/repmgr.log'
log_status_interval=10

#可执行文件配置
pg_bindir='/usr/local/pgsql/bin'

#集群faibver设置
failover='automatic'
promote_command='/usr/local/pgsql/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'
log_file='/app/pgsql/log/repmgr.log'

5.2.2 slave1 节点

vi /etc/repmgr.conf

#repmgr基本配置信息
node_id=2
node_name='slave1'
conninfo='host=192.168.100.111 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/app/pgsql/data'

#repmgr日志配置
log_level=INFO                          
log_facility=STDERR                     
log_file='/app/pgsql/log/repmgr.log'
log_status_interval=10

#可执行文件配置
pg_bindir='/usr/local/pgsql/bin'

#集群faibver设置
failover='automatic'
promote_command='/usr/local/pgsql/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

5.2.3 slave2 节点

#repmgr基本配置信息
node_id=3
node_name='slave2'
conninfo='host=192.168.100.112 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/app/pgsql/data'

#repmgr日志配置
log_level=INFO                          
log_facility=STDERR                     
log_file='/app/pgsql/log/repmgr.log'
log_status_interval=10

#可执行文件配置
pg_bindir='/usr/local/pgsql/bin'

#集群faibver设置
failover='automatic'
promote_command='/usr/local/pgsql/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

6. master 节点配置数据库环境

6.1 创建repmgr数据库及用户

create database repmgr;
create user repmgr with password 'repmgr' superuser login;
alter database repmgr owner to repmgr;

6.2 配置pg_hba.conf

cd /app/pgsql/data
vi pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all           all                                     trust
# IPv4 local connections:
host       all        all             127.0.0.1/32            trust
local   repmgr     repmgr                                     trust
host    repmgr     repmgr             127.0.0.1/32            trust
host    repmgr     repmgr             192.168.100.0/24        trust
host       all        all             0.0.0.0/0               md5
# IPv6 local connections:
host       all        all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

local   replication     repmgr                                     trust
host    replication     repmgr             127.0.0.1/32            trust
host    replication     repmgr             192.168.220.0/24        trust

7. repmgr 集群构建

7.1 master 节点加入集群

--启动master节点数据库
su - postgres
pg_ctl start -l $PGLOG
--master节点，将master数据库注册至集群，并查看状态
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf primary register"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"

7.2 slave1 节点加入集群

--slave1节点，测试连通性并克隆master数据库数据
su - postgres -c "/usr/local/pgsql/bin/repmgr -h 192.168.100.110 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run"
rm -rf /app/pgsql/data/*
su - postgres -c "/usr/local/pgsql/bin/repmgr -h 192.168.100.110 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone"
--启动slave1节点数据库
su - postgres
pg_ctl start -l $PGLOG
--slave1节点，将slave1数据库注册到集群，并查看状态
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf standby register"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"

7.3 slave2 节点加入集群

--slave2节点，测试连通性并克隆master数据库数据
su - postgres -c "/usr/local/pgsql/bin/repmgr -h 192.168.100.110 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run"
rm -rf /app/pgsql/data/*
su - postgres -c "/usr/local/pgsql/bin/repmgr -h 192.168.100.110 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone"
--启动slave2节点数据库
su - postgres
pg_ctl start -l $PGLOG
--slave2节点，将slave2数据库注册到集群，并查看状态
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf standby register"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"

8. 开启守护进程

--开启守护进程（故障自动转移）
su - postgres -c "/usr/local/pgsql/bin/repmgrd -f /etc/repmgr.conf -d  -p /tmp/repmgrd.pid"

--停止守护进程
REPMGRD_PID=`ps -ef | grep repmgrd|grep   -v grep |awk '{print  $2}'`
kill -9 $REPMGRD_PID

9. 其他 repmgr 管理命令

--主备切换并查看
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf standby switchover --siblings-follow -U repmgr  --verbose"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"
--从库重新跟随新主库
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf standby follow"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"

--驱逐备库节点
su - postgres -c "/usr/local/pgsql/bin/repmgr standby unregister -f /etc/repmgr.conf"

--注销不活动的主节点
su - postgres -c "/usr/local/pgsql/bin/repmgr primary unregister -f /etc/repmgr.conf"

--主节点故障时，手动升级备库为主节点
su - postgres -c "/usr/local/pgsql/bin/repmgr standby promote -f /etc/repmgr.conf --siblings-follow"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf standby follow"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"

--故障节点修复后，重新加入集群
su - postgres -c "/usr/local/pgsql/bin/repmgr node rejoin -d 'host=slave2 user=repmgr dbname=repmgr' --force-rewind --verbose -f /etc/repmgr.conf"
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf cluster show"

--强制重新注册
su - postgres -c "/usr/local/pgsql/bin/repmgr -f /etc/repmgr.conf primary register --force"