PGSQL主从+keepalived高可用配置

环境说明:

创新互联建站是专业的崇仁网站建设公司,崇仁接单;提供成都网站建设、网站建设,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行崇仁网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!

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


网站名称:PGSQL主从+keepalived高可用配置
转载注明:http://pwwzsj.com/article/pghhji.html