PostgreSQL学习随笔19 Keepalived + PostgreSQL 12.10 高可用实验

1.1配置postgresql复制
hostnamectl set-hostname mypg01
hostnamectl set-hostname mypg02

Hosts文件配置
# vim etc/hosts
10.110.9.37 mypg01
10.110.9.51 mypg02
10.110.9.241 vip

以下操作分别在主节点和备节点同时执行:
root配置两台数据库服务器之间的信任关系
su - root
ssh-keygen
ssh-copy-id root@备节点IP           主节点执行
ssh-copy-id root@主节点IP           备节点执行
------------------------------------------------------------------------------------
关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
SELINUX
vi etc/selinux/config
将SELINUX=enforcing 改为SELINUX=disabled
配置流复制主备,主节点配置

编辑vi pg_hba.conf文件,添加如下内容:
host all all 0.0.0.0/0 md5
host replication replica 0.0.0.0/0 md5

编辑postgresql.conf文件,添加如下内容:
listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 32
max_replication_slots = 32
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
wal_sender_timeout = 60s
wal_log_hints = on
unix_socket_directories = '.'
unix_socket_permissions = 0700
bgwriter_delay = '10ms'
datestyle = 'iso, mdy'
full_page_writes = 'True'
huge_pages = 'False'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0
wal_receiver_status_interval = '1s'
wal_writer_delay = '10ms'
autovacuum_max_workers=10
maintenance_work_mem = 256MB
vacuum_cost_limit=150
max_worker_processes = 128
checkpoint_timeout = 10min
log_disconnections = on
wal_writer_delay = 10ms
log_destination = 'csvlog'
logging_collector = on
log_directory = '/opt/pg_root/pg_log'
log_filename = 'postgresql-log.%a'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 1000000
log_min_duration_statement = 500
log_statement = ddl
huge_pages = off

创建 .pgpass文件
su - postgres
vi ~/.pgpass
1主机IP:5432:postgres:postgres:123456
2主机IP:5432:postgres:postgres:123456
1主机IP:5432:replication:replica:REPLICA321
2主机IP:5432:replication:replica:REPLICA321
chmod 0600 .pgpass

流复制配置 在node1节点
#创建replica用户 
create role replica login replication encrypted password 'REPLICA321';

创建判断数据库时延的库、角色、表
create role keepalived nosuperuser nocreatedb login encrypted password 'keepalived';
create database keepalived with owner=keepalived encoding='UTF8';
\c keepalived keepalived
create table sr_delay(id int4,sr_date timestamp(0) without time zone);
insert into sr_delay values(1,now());

配置主从复制
备库配置
从此处开始配置备库，首先通过pg_basebackup命令行工具在从库上生成基础备份
命令如下：
rm -rf opt/pg_root/*
pg_basebackup -F p -P -R -D $PGDATA -h 10.110.9.37 -p 5432 -U replica 
REPLICA321
会生成：standby.signal 文件
postgresql.auto.conf 文件会添加连接信息
primary_conninfo = 'user=replica password=REPLICA321 host=10.110.8.97 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any'

安装keeplived
keepalived-2.2.7.tar.gz
tar –zxvf keepalived-2.2.7.tar.gz
cd keepalived-2.2.7/
./configure --prefix=/usr/local/keepalived
make && make install

将自动生成的keeplived文件复制目录usr/sbin
cp /usr/local/keepalived/sbin/keepalived usr/sbin/

将自动生成的keeplived.conf文件复制到目录/etc/sysconfig
cp /usr/local/keepalived/etc/sysconfig/keepalived etc/sysconfig/

选择keeplived 文件上传,cd etc/init.d/
设置文件当前用户可读可写
chmod u+x etc/init.d/keepalived
查看上传后的文件
cat /etc/init.d/keepalived

修改后 建立文件路径
mkdir /etc/keepalived/
mkdir /etc/keepalived/scripts
mkdir /etc/keepalived/log
touch /etc/keepalived/log/failover.log
touch /etc/keepalived/log/pg_status.log
主备节点分别上传
进入此目录下/etc/keepalived/上传修改后的keeplived.conf
chmod 644 keeplived.conf
进入此目录下/etc/keepalived/scripts上传修check_pg.sh、failover.sh
chmod 755 /etc/keepalived/scripts/*
修改keepalived.conf配置文件
网卡名、VIP地址

Keepalived启动及状态查看
MASTER启动keepalived 服务器
# service keepalived start
MASTER查看keepalived 状态  
# service keepalived status

keeplived.conf

! Configuration File for keepalived
global_defs {
router_id mypg02
}

vrrp_script check_pg_alived {
        script "/etc/keepalived/scripts/check_pg.sh"
        interval 5
        fall 3 # require 3 failures for KO
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface ens32
    virtual_router_id 10
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass abcdefgh
    }

    track_script {
        check_pg_alived
    }

    virtual_ipaddress {
        10.110.9.241
    }

   smtp_alert
      notify_master "/etc/keepalived/scripts/failover.sh"
}

------------------------------------------
check_pg.sh

#!/bin/bash
export PGDATABASE=keepalived
export PGPORT=5432
export PGUSER=keepalived
export PGHOME=/opt/pgsql
export PATH=$PGHOME/bin:$PATH:$HOME/bin

PGMIP=127.0.0.1
LOGFILE=/etc/keepalived/log/pg_status.log

#pg_port_status=`lsof -i :$PGPORT | grep LISTEN | wc -l`
#pg_port_status=`ps -ef | grep LISTEN | wc -l`
SQL1='SELECT pg_is_in_recovery from pg_is_in_recovery();'
SQL2='update sr_delay set sr_date = now() where id =1;'
SQL3='SELECT 1;'

db_role=`echo $SQL1  | $PGHOME/bin/psql -h 127.0.0.1 -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w`
#if [ $pg_port_status -lt 1 ];then
#    echo -e `date +"%F %T"` "Error: The postgreSQL is not running,please check the postgreSQL server status!" >> $LOGFILE
#    exit 1
#fi

if [ "$db_role" == 't' ];
then
   echo -e `date +"%F %T"` "Attention1:the current database is standby DB!" >> $LOGFILE
   exit 0
fi

echo $SQL3 | psql -h 127.0.0.1 -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w

if [ $? -eq 0 ] ;
then
   echo $SQL2 | psql -h 127.0.0.1 -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w
   echo -e `date +"%F %T"` "Success: update the master sr_delay successed!" >> $LOGFILE
   exit 0
else
   echo -e `date +"%F %T"` "Error:Is the server is running?" >> $LOGFILE
   exit 1
fi

------------------------------------------
failover.sh

#!/bin/bash
export PGPORT=5432
export PGUSER=keepalived
export PG_OS_USER=postgres
export PGDATA=/opt/pg_root
export PGDBNAME=keepalived
export LANG=zh_CN.UTF-8
export PGPATH=/opt/pgsql/bin
export PATH=$PATH:$PGPATH

PGMIP=127.0.0.1
LOGFILE=/etc/keepalived/log/failover.log

SQL1='select pg_is_in_recovery  from pg_is_in_recovery();'
SQL2="select sr_date as delay_time from sr_delay where now()-sr_date < interval '100';"


db_role=`echo $SQL1 | psql -At -h 127.0.0.1 -p $PGPORT -U $PGUSER -d $PGDBNAME -w`
db_sr_delaytime=`echo $SQL2 | psql -h 127.0.0.1 -p $PGPORT -d $PGDBNAME -U $PGUSER -At -w`


SWITCH_COMMAND='pg_ctl promote -D $PGDATA'


if [ "$db_role" == 'f' ];then
   echo -e `date +"%F %T"` "Attention:The current postgreSQL DB is master database,cannot switched!" >> $LOGFILE
   exit 0
fi


if [ "$db_role" == 't' ];then
   echo -e `date +"%F %T"` "Attention:The current database is statndby,ready to switch master database!" >> $LOGFILE
   su - $PG_OS_USER -c "$SWITCH_COMMAND"
fi


#if [ $db_sr_delaytime -gt 0 ];then
#     echo -e `date +"%F %T"` "Attention:The current master database is health,the standby DB cannot switched!" >> $LOGFILE
#     exit 0
#fi


#if [!"$db_sr_delaytime"];then
#      echo -e `date +"%F %T"` "Attention:The current database is statndby,ready to switch master database!" >> $LOGFILE
#      su - $PG_OS_USER -c "$SWITCH_COMMAND"
#elif [ $? -eq 0 ];then
#          echo -e `date +"%F %T"` "success:The current standby database successed to switched the primary PG database !" >> $LOGFILE
#          exit 0
#else
#          echo -e `date +"%F %T"` "Error: the standby database failed to switch the primary PG database !,pelease checked it!" >> $LOGFILE
#          exit 1
#fi
------------------------------------------