postgresql+keepalived高可用部署(基于postgresql流复制部署)
基于流复制部署,keepalived检测postgresql状态,如果pgsql死了,那么keepalived的VIP漂移到备库,同时提升备库为主库,可以进行读写操作。(默认流复制的主库为可读可写,备库为只读)
主库:192.168.19.73
备库:192.168.19.74
VIP:192.168.19.250
流复制部署完毕后(流复制部署以及测试请看上一章),安装keepalived
1.主备安装keepalived
yum install -y keepalived
2.查看安装文件位置
whereis keepalived
输出信息如下:keepalived: /usr/sbin/keepalived /etc/keepalived /usr/libexec/keepalived /usr/share/man/man8/keepalived.8.gz
3.修改keepalived.conf配置文件
1 ! Configuration File for keepalived
2
3 global_defs {
4 notification_email {
5 *******@163.com
6 }
7 notification_email_from Alexandre.Cassen@localhost
8 smtp_server mail.server.intelli.com
9 smtp_connect_timeout 30
10 router_id pg_73 #注意主备不一样
11 vrrp_skip_check_adv_addr
12 # vrrp_strict #注释掉此处,避免keepalived启动后iptables drop vip
13 vrrp_garp_interval 0
14 vrrp_gna_interval 0
15 }
16 #1-16行为邮箱设置,可以简单设置或者忽略。
17 vrrp_script check_pg_alived {
18 script "/etc/keepalived/scripts/check_pg.sh" #检测脚本位置
19 interval 2
20 weight -5
21 fall 2
22 rise 1
23 }
24
25 vrrp_instance VI_1 {
26 state BACKUP #主备一致
27 interface eth0
28 virtual_router_id 50 #默认51,保持主备一致即可
29 priority 100 #优先级主高于备即可
30 advert_int 1
31 authentication {
32 auth_type PASS
33 auth_pass HT706 #密码主备一致
34 }
35 track_script {
36 check_pg_alived
37 }
38 virtual_ipaddress {
39 192.168.19.250 #VIP
40 }
41 }
42 }
4.主备脚本内容如下
cat /etc/keepalived/scripts/check_pg.sh
#!/bin/bash
#判断pg是否活着
A=`ps -C postgres --no-header | wc -l`
#判断vip浮到哪里
B=`ip a | grep 192.168.19.250 | wc -l`
#判断是否是从库处于等待的状态
C=`ps -ef | grep postgres | grep 'startup process' | wc -l`
#判断从库链接主库是否正常
D=`ps -ef | grep postgres | grep 'receiver' | wc -l`
#判断主库连接从库是否正常
E=`ps -ef | grep postgres | grep 'sender' | wc -l`
#如果pg死了,将消息写入日记并且关闭keepalived
if [ $A -eq 0 ];then
echo "`date "+%Y-%m-%d--%H:%M:%S"` postgresql stop so vip stop " >> /etc/keepalived/log/check_pg.log
systemctl stop keepalived
else
#判断出主ku挂了,vip漂移到了从,提升从的地位让他可读写
if [ $B -eq 1 -a $C -eq 1 -a $D -eq 0 ];then
su - postgres -c "pg_ctl promote -D /var/lib/pgsql/data/" #重新加载pgsql使其可写
echo "`date "+%Y-%m-%d--%H:%M:%S"` standby promote " >> /etc/keepalived/log/check_pg.log
fi
#判断出自己是主并且和从失去联系
if [ $B -eq 1 -a $C -eq 0 -a $D -eq 0 -a $E -eq 0 ];then
echo "`date "+%Y-%m-%d--%H:%M:%S"` can't find standby " >> /etc/keepalived/log/check_pg.log
fi
fi
5.修改keepalived配置
1)修改 /etc/sysconfig/keepaliveddel;把KEEPALIVED_OPTIONS="-D" 修改为KEEPALIVED_OPTIONS="-D -d -S 0"
2)# vi /etc/rsyslog.conf
加入如下配置:
#keepalived -S 0
local0.*/var/log/keepalived.log
6.启动keepalived服务检查VIP情况
systemctl start keepalived && systemctl enable keepalived
ip addr #主库(192.168.19.73)有vip备库(192.168.19.74)无vip
7.关闭SELINUX和防火墙
systemctl stop firewalld && systemctl disable firewalld
setenforce 0 ##永久关闭修改vim /etc/sysconfig/selinux
8.检测
1)查看主库与备库的数据库是否一致,并在主库上写入数据,再到备库上查看是否同步过去了。确定同步没有问题(流复制检查)
2)使用vip连接数据库并写入数据
psql -h 192.168.19.250 -U postgres
create table test(id int); 创建test表
insert into test values (1),(2); 插入数据
3)主备查看数据
select * from test;
4)模拟主库宕机,检测备库情况
systemctl stop postgresql #停掉主库(192.168.19.73)
检查vip在哪一台主机,连接vip再次插入数据,如果可以写入数据则表示备库切换主库成功;
insert into test values (3);
现在只有一台运行(74)
9.将坏掉的主库(192.168.19.73)修改为备库
1)删除数据或者mv备份
rm -rf /var/lib/pgsql/data/*
2)同步主库数据
pg_basebackup -D /var/lib/pgsql/data/ -F p -X stream -v -P -h 192.168.19.74 -U replica #IP为现在运行主库IP地址
3)修改recovery.conf文件指定主库ip
mv recovery.done recovery.conf
vim recovery.conf
standby_mode='on'
primary_conninfo = 'host=192.168.19.73 port=5432 user=replica password=replica' #将该ip指定主库ip地址
restore_command = ''
recovery_target_timeline = 'latest'
4)启动pgsql
chown -R postgres.postgers /var/lib/pgsql #同步过来的属主属组为root修改为poatgres
systemctl restart postgresql
注:部署还不够完美,这里还没有启动keepalived,当启动后vip又漂移会来;需要手动降级
注:本人写了一个将宕机的主库添加为从库脚本(第9步也可以执行脚本即可)脚本内容如下
vim /postgresql/pqsql.sh #只可以执行一次,注意IP地址修改
#!/bin/bash
rm -rf /var/lib/pgsql/data/* && echo "数据删除成功" || echo "数据删除发生错误" #也可以将数据备份
sleep 0.5
#同步主库数据,ip为主库ip
pg_basebackup -D /var/lib/pgsql/data/ -F p -X stream -v -P -h 192.168.19.74 -U replica &> /dev/null && echo “数据同步成功” || echo “数据同步发生错误”
sleep 1
mv /var/lib/pgsql/data/recovery.done /var/lib/pgsql/data/recovery.conf && sed -i "s/192.168.19.73/192.168.19.74/g" /var/lib/pgsql/data/recovery.conf && echo "文件recovery.conf修改成功" || echo "文件recovery.conf修改失败"
chown -R postgres.postgres /var/lib/pgsql
sleep 0.5
systemctl restartt postgresql && echo "pgsql start success" || echo "pgsql start fial"
sleep 1
echo "提示:请去修改keepalived优先级(/etc/keepalived/keepalived.conf)!!!然后开启keepalived!!!"