PGSQL主从+keepalived高可用配置

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/duanbiren123/article/details/81950494

环境说明:

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

展开阅读全文

没有更多推荐了,返回首页