PGSQL主从+keepalived高可用配置

环境说明：

主机与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