keepalived + PostgreSQL主从异步流复制的高（01）

举报
snowofsummer 发表于 2021/10/24 13:38:57 2021/10/24
【摘要】 概要说明通过keepalive 来实现  PostgreSQL 数据库的主从自动切换，以达到高可用，当主节点宕机时，从节点可自动切换为主节点，继续对外提供服务，同时为了保证 keepalived 不会出现单点故障，所以keepalived 也搭建主备节点，防止单点故障。环境信息192.168.137.129 PostgreSQL master192.168.137.130 PostgreSQ...

概要说明

通过keepalive 来实现  PostgreSQL 数据库的主从自动切换，以达到高可用，当主节点宕机时，从节点可自动切换为主节点，继续对外提供服务，同时为了保证 keepalived 不会出现单点故障，所以keepalived 也搭建主备节点，防止单点故障。

环境信息

192.168.137.129	PostgreSQL master
192.168.137.130	PostgreSQL slave
192.168.137.100	VIP
192.168.137.129	keepalived master
192.168.137.130	keepalived slave

PG版本: 3.0.0（PostgreSQL 10.3）
Linux 版本: RHEL/CentOS 7.x X64


1.IP

两块网卡均按服务器规划设置对应静态ip，关闭NetworkManager服务并取消自启动

# service NetworkManager stop

# chkconfig NetworkManager off

2.Selinux

# setenforce 0　　

# sed -i "s/^SELINUX\=enforcing/SELINUX\=disabled/g" /etc/selinux/config

 

3.防火墙

推荐关闭防火墙

# service iptables stop

# chkconfig iptables off

# service ip6tables stop

# chkconfig ip6tables off

 

4.主机名配置

mypg01：

# hostname mypg01

# vim /etc/sysconfig/network

NETWORKING=yes

HOSTNAME=mypg01

mypg02：

# hostname mypg02

# vim /etc/sysconfig/network

NETWORKING=yes

HOSTNAME=mypg02

 

5.Hosts文件配置

# vim /etc/hosts

192.168.137.129    mypg01

192.168.137.130    mypg02

192.168.137.100    vip

 

6.节点ssh互信

mypg01:

# ssh-keygen -t rsa

一路回车直到完成

# ssh-copy-id -i ~/.ssh/id_rsa.pub " root@mypg02"

mypg02:

# ssh-keygen -t rsa

一路回车直到完成

# ssh-copy-id -i ~/.ssh/id_rsa.pub " root@mypg01"

7.Yum配置

# mount -o loop /tmp/rhel-6.5.iso /mnt

# vim /etc/yum.repos.d/base.repo

[rhel_6_iso]

name=localiso

baseurl=file:///mnt/

enabled=1

gpgcheck=0

 

[Rhel_6-HA_iso]

Name=localiso

Baseurl=file:///mnt/HighAvailability

enabled=1

Gpgcheck=0

 

8.内核参数

# vim /etc/sysctl.conf

kernel.sem=50100 64128000 50100 1280

fs.file-max=7672460

fs.aio-max-nr=1048576

net.core.rmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_default=262144

net.core.wmem_max=4194304

net.ipv4.ip_local_port_range=9000 65500

net.ipv4.tcp_wmem=8192 65536 16777216

net.ipv4.tcp_rmem=8192 87380 16777216

vm.min_free_kbytes=512000

vm.vfs_cache_pressure=200

vm.swappiness=20

net.ipv4.tcp_max_syn_backlog=10240

net.core.somaxconn=10240

参数生效

# sysctl -p

 

9.资源限制

# vim /etc/security/limits.conf

* soft nofile 655360

* hard nofile 655360

* soft nproc 655360

* hard nproc 655360

* soft core unlimited

* hard core unlimited

* soft memlock 50000000

* hard memlock 50000000

安装配置PostgreSQL

  1. 主库参数配置

1）配置pg_hba.conf 

根据实际网络环境，新增加配置客户端访问权限

$ vim $PGDATA/pg_hba.conf
host     replication     postgres        192.168.137.129/32         trust

host     replication     postgres        192.168.137.130/32         trust

host     all             all            192.168.0.0/16             md5

 

2）配置postgresql.conf

vim $PGDATA/postgresql.conf

***必须配置参数，可以直接追加到配置文件最后
listen_addresses = '*'
port=1921
max_connections = 1000

wal_level = replica
max_wal_senders = 5
hot_standby = on

3）创建recovery.done文件

$ vim  $PGDATA/recovery.done

 

standby_mode = 'on'

primary_conninfo = 'user=postgres host=mypg01 port=1921'

recovery_target_timeline = 'latest'

2.从库设置（仅mypg02）

1）mypg02进行同步操作，生成备库

$ rm -rf $PGDATA/*

$ pg_basebackup -Fp -D $PGDATA -Xs -v -P -h mypg01 -p 1921 -U postgres -R

 

2）删除自动创建的recovery.conf

$ rm -rf $PGDATA/recovery.conf

 

3）修改recovery.done

$ vim $PGDATA/recovery.done
standby_mode = 'on'

primary_conninfo = 'user=postgres host=mypg01 port=1921'

recovery_target_timeline = 'latest

安装配置Keepalived

yum install -y keepalived

配置 keepalived.conf

# vim /etc/keepalived/keepalived.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 ens33
    virtual_router_id 10
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass abcdefgh
    }

    track_script {
        check_pg_alived
    }

    virtual_ipaddress {
        192.168.137.100
    }

   smtp_alert
      notify_master "/etc/keepalived/scripts/failover.sh"
#      notify_fault "/etc/keepalived/scripts/fault.sh"
}

对主从PG 进行状态监控，监控脚本 check_pg.sh,且分配 755 执行权限

#!/bin/bash
export PGDATABASE=postgres
export PGPORT=1921
export PGUSER=postgres
export PGHOME=/usr/local/pg103
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  -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

Keepalived启动及状态查看

MASTER启动keepalived 服务器

# service keepalived start

 

MASTER查看keepalived 状态

# service keepalived status

切换测试

停止主服务器PG服务

pg_ctl stop

waiting for server to shut down..... done

server stopped

查看18 keepalived 状态

/scripts # service keepalived status
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2018-06-13 17:24:12 CST; 24min ago
  Process: 8641 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 8645 (keepalived)
    Tasks: 3 (limit: 512)
   CGroup: /system.slice/keepalived.service
           ├─8645 /usr/sbin/keepalived -D
           ├─8646 /usr/sbin/keepalived -D
           └─8647 /usr/sbin/keepalived -D

Jun 13 17:48:31 [root@mypg01 Keepalived_healthcheckers[8646]: Removing service [192.168.137.129]:1921 from VS [192.168.137.100]:1921
Jun 13 17:48:31 [root@mypg01 Keepalived_healthcheckers[8646]: SMTP connection ERROR to [192.168.137.130]:25.
Jun 13 17:48:39 [root@mypg01 Keepalived_vrrp[8647]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jun 13 17:48:40 [root@mypg01 Keepalived_vrrp[8647]: VRRP_Instance(VI_1) Entering MASTER STATE
Jun 13 17:48:40 [root@mypg01 Keepalived_vrrp[8647]: VRRP_Instance(VI_1) setting protocol VIPs.
Jun 13 17:48:40 [root@mypg01 Keepalived_vrrp[8647]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.137.100
Jun 13 17:48:40 [root@mypg01 Keepalived_vrrp[8647]: Opening script file /etc/keepalived/scripts/failover.sh
Jun 13 17:48:40 [root@mypg01 Keepalived_healthcheckers[8646]: Netlink reflector reports IP 192.168.137.100 added
Jun 13 17:48:40 [root@mypg01 Keepalived_vrrp[8647]: SMTP connection ERROR to [192.168.137.130]:25.
Jun 13 17:48:45 [root@mypg01 Keepalived_vrrp[8647]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.137.100
/scripts #

日志参数介绍
 `VRRP_Instance(VI_1) Entering MASTER STATE` 表示此时130服务器 keepalived 进入master 状态
` VRRP_Instance(VI_1) setting protocol VIPs` 给网卡绑定 `VIP`

查看 IP地址
# ip addr

 发现此时 VIP 绑定在130服务器网卡上
测试结果：当129服务器主库宕机后，通过keepalive ，130服务器备库自动切换为主库，VIP绑定在该服务器，并可继续对外提供服务，从而实现高可用。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
PostgreSQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入