PGSQL主從+keepalived高可用配置

環(huán)境說明：

主機(jī)與IP：

192.168.11.177 主庫

192.168.11.180 備庫 

192.168.11.210  VIP 

系統(tǒng)：

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按照默認(rèn)的值51會(huì)出錯(cuò)bogus VRRP packet received on eth0 !!!，所以改值為61。

主備創(chuàng)建目錄和腳本

# 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` 

#判斷是否是從庫處于等待的狀態(tài)

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死了，將消息寫入日記并且關(guān)閉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

        #判斷出自己是主并且和從失去聯(lián)系

        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

主備啟動(dòng)服務(wù)

# systemctl start keepalived.service

# systemctl enable keepalived.service

2、停止主庫服務(wù)，并切換主庫為備庫

停止主庫服務(wù)，之后發(fā)現(xiàn)主庫上的VIP消失，備庫上的VIP生成，備庫變?yōu)橹鲙?，可以進(jìn)行建庫建表等操作。

原來的主庫切換為備庫：

$ 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'

啟動(dòng)主機(jī)keepalived

# systemctl start keepalived

3、檢查驗(yàn)證

查看原來備庫服務(wù)和庫狀態(tài)

$ 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)

查看原來主庫服務(wù)和庫狀態(tài)

$ 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、測試結(jié)果

PGSQL高可用測試結(jié)果如下：

高可用方案：

PGSQL 主從 + keepalived

資源：

192.168.11.177 主庫（稱為服務(wù)器A）

192.168.11.180 備庫  (稱為服務(wù)器B）

192.168.11.210  VIP 

1)、模擬A的PGSQL服務(wù)停止

B接管VIP（自動(dòng)）

B由從庫變?yōu)橹鲙欤ㄗ詣?dòng)）

啟動(dòng)A的PGSQL服務(wù)，并把A配置為備庫（手動(dòng)）

2)、模擬B的PGSQL服務(wù)停止

A接管VIP（自動(dòng)）

A由從庫變?yōu)橹鲙欤ㄗ詣?dòng)）

啟動(dòng)B的PGSQL服務(wù)，并把B配置為備庫（手動(dòng)）

參考：

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