Shulou(Shulou.com)06/01 Report--

环境说明：

主机与IP：

192.168.11.177 主库

192.168.11.180 备库

192.168.11.210 VIP

系统：

centos7.2

PGSQL9.6主从已安装配置完成（参考我之前的博客）

安装配置：

1、安装配置keepalived

主备安装：

# yum install -y keepalived

主配置：

# vi /etc/keepalived/keepalived.conf

bal_defs {

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id pg

}

vrrp_script chk_pgsql {

script "/etc/keepalived/scripts/pgsql_check.sh"

interval 2

weight -5

fall 2

rise 1

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 61

priority 100

nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

chk_pgsql

}

virtual_ipaddress {

192.168.11.210

}

}

备配置：

# vi /etc/keepalived/keepalived.conf

bal_defs {

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id pg

}

vrrp_script chk_pgsql {

script "/etc/keepalived/scripts/pgsql_check.sh"

interval 2

weight -5

fall 2

rise 1

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 61

priority 80

nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

chk_pgsql

}

virtual_ipaddress {

192.168.11.210

}

}

注意：

这里virtual_router_id按照默认的值51会出错bogus VRRP packet received on eth0 !!!，所以改值为61。

主备创建目录和脚本

# mkdir /etc/keepalived/scripts

# vi /etc/keepalived/scripts/pgsql_check.sh

#!/bin/bash

#判断pg是否活着

A=`ps -C postgres --no-header | wc -l`

#判断vip浮到哪里

B=`ip a | grep 192.168.11.210 | 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

#判断出主挂了，vip浮到了从，提升从的地位让他可读写

if [ $B -eq 1 -a $C -eq 1 -a $D -eq 0 ];then

su - postgres -c "pg_ctl promote -D /data/pg_data"

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

sleep 10

echo "`date "+%Y-%m-%d--%H:%M:%S"` can't find standby " >> /etc/keepalived/log/check_pg.log

fi

fi

主备配置日志：

修改 /etc/sysconfig/keepalived

把KEEPALIVED_OPTIONS="-D" 修改为KEEPALIVED_OPTIONS="-D -d -S 0"

# vi /etc/rsyslog.conf

加入如下配置：

#keepalived -S 0

local0.*/var/log/keepalived.log

主备启动服务

# systemctl start keepalived.service

# systemctl enable keepalived.service

2、停止主库服务，并切换主库为备库

停止主库服务，之后发现主库上的VIP消失，备库上的VIP生成，备库变为主库，可以进行建库建表等操作。

原来的主库切换为备库：

$ cd /data/pg_data

$ rm -rf *

$ pg_basebackup -h 192.168.11.180 -U repuser -D /data/pg_data -X stream -P

$ mv recovery.done recovery.conf

$ vi recovery.conf

primary_conninfo = 'host=192.168.11.177 port=5432 user=repuser password=password123! keepalives_idle=60'

>>

primary_conninfo = 'host=192.168.11.180 port=5432 user=repuser password=password123! keepalives_idle=60'

启动主机keepalived

# systemctl start keepalived

3、检查验证

查看原来备库服务和库状态

$ ps -ef | grep postgres

postgres 1081 1 0 Aug07 ? 00:00:06 /usr/local/postgresql/bin/postgres -D /data/pg_data

postgres 1083 1081 0 Aug07 ? 00:00:01 postgres: checkpointer process

postgres 1084 1081 0 Aug07 ? 00:00:02 postgres: writer process

postgres 1085 1081 0 Aug07 ? 00:00:00 postgres: stats collector process

postgres 13961 1081 0 11:09 ? 00:00:00 postgres: wal writer process

postgres 13962 1081 0 11:09 ? 00:00:00 postgres: autovacuum launcher process

postgres 13963 1081 0 11:09 ? 00:00:00 postgres: archiver process last was 000000020000000000000010

postgres 27065 1081 0 11:25 ? 00:00:00 postgres: wal sender process repuser 192.168.11.177(47074) streaming 0/11000060

root 27922 3590 0 11:26 pts/1 00:00:00 grep --color=auto postgres

postgres=# SELECT pg_is_in_recovery from pg_is_in_recovery();

pg_is_in_recovery

-------------------

f

(1 row)

查看原来主库服务和库状态

$ ps -ef | grep postgres

postgres 2602 1 0 11:25 pts/1 00:00:00 /usr/local/postgresql/bin/postgres -D /data/pg_data

postgres 2603 2602 0 11:25 ? 00:00:00 postgres: startup process recovering 000000020000000000000011

postgres 2604 2602 0 11:25 ? 00:00:00 postgres: checkpointer process

postgres 2605 2602 0 11:25 ? 00:00:00 postgres: writer process

postgres 2606 2602 0 11:25 ? 00:00:00 postgres: stats collector process

postgres 2607 2602 0 11:25 ? 00:00:00 postgres: wal receiver process streaming 0/11000060

postgres 2613 2325 0 11:25 pts/1 00:00:00 grep --color=auto postgres

postgres=# SELECT pg_is_in_recovery from pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

(1 row)

4、测试结果

PGSQL高可用测试结果如下：

高可用方案：

PGSQL 主从 + keepalived

资源：

192.168.11.177 主库（称为服务器A）

192.168.11.180 备库 (称为服务器B）

192.168.11.210 VIP

1)、模拟A的PGSQL服务停止

B接管VIP（自动）

B由从库变为主库（自动）

启动A的PGSQL服务，并把A配置为备库（手动）

2)、模拟B的PGSQL服务停止

A接管VIP（自动）

A由从库变为主库（自动）

启动B的PGSQL服务，并把B配置为备库（手动）

参考：

https://blog.csdn.net/vanilla_he/article/details/79001890

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.