Postgresql + Keepalived 实现主从高可用
系统环境及架构
| 服务器ip | 系统版本 | Postgres版本 | 部署内容 |
|---|---|---|---|
| 192.168.70.10 | CentOS7 | 9.6 | Postgres主 |
| 192.168.70.20 | CentOS7 | 9.6 | Postgres从1 |
| 192.168.70.30 | CentOS7 | 9.6 | Postgres从2 |
Keepalived VIP：192.168.70.66
Postgres安装
前往官网下载RPM包
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20），Postgres从2（192.168.70.30）]
# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
安装Postgres
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20），Postgres从2（192.168.70.30）]
# rpm -ivh pgdg-centos96-9.6-3.noarch.rpm
# yum install postgresql96-server postgresql96-contrib -y
# firewall-cmd --add-service=postgresql --permanent
# firewall-cmd --reload
数据库初始化
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20），Postgres从2（192.168.70.30）]
# /usr/pgsql-9.6/bin/postgresql96-setup initdb
# systemctl restart postgresql-9.6
# systemctl enable postgresql-9.6
(mv /usr/lib/systemd/system/postgresql-9.6.service /usr/lib/systemd/system/postgresql.service)
修改postgres用户密码
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20），Postgres从2（192.168.70.30）]
# su postgres
bash-4.2$ psql
postgres=# ALTER USER postgres WITH PASSWORD '123456';
postgres=# \q
bash-4.2$ exit
Postgres主从
主库配置
[Postgres主（192.168.70.10）]
创建归档目录
# su postgres
# mkdir -p /var/lib/pgsql/9.6/data/pg_archive
创建replica用户进行主从同步
# su postgres
bash-4.2$ psql
postgres=# create role replica login replication encrypted password '123456';
修改pg_hba.conf，允许replica用户来同步
# vim /var/lib/pgsql/9.6/data/pg_hba.conf
host all all 192.168.70.1/24 trust #允许其他主机连接到本数据库host replication replica 192.168.70.1/24 md5 #允许从服务器使用replica用户来复制
修改postgresql.conf
# vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = '*' #监听所有IP
max_connections = 100 #从库的max_connections必须要大于主库
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = hot_standby #热备模式
archive_mode = on #允许归档
archive_command = 'cp %p /var/lib/pgsql/9.6/data/pg_archive/%f' #用该命令来归档logfile segment
max_wal_senders = 32 #这个设置可以最多有几个流复制连接
wal_keep_segments = 64 #设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
synchronous_standby_names = '*'
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '< %m > '
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
重启postgres
# systemctl restart postgresql-9.6
从库配置
[Postgres从1（192.168.70.20）]
将主库数据拷贝至从库
# systemctl stop postgresql-9.6
# su postgres
bash-4.2$ cd /var/lib/pgsql/9.6
bash-4.2$ rm -rf data
bash-4.2$ /usr/pgsql-9.6/bin/pg_basebackup -h 192.168.70.10 -U replica -D /var/lib/pgsql/9.6/data/ -X stream -P
bash-4.2$ mkdir -p /var/lib/pgsql/9.6/data/pg_archive
bash-4.2$ cd /var/lib/pgsql/9.6/data/ && vim recovery.conf
配置recovery.conf
standby_mode = on #说明该节点是从服务器
primary_conninfo = 'host=192.168.70.10 port=5432 user=replica password=123456' #主节点的信息以及连接的用户
recovery_target_timeline = 'latest'
配置postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
重启postgres
# systemctl restart postgresql-9.6
从库配置（在Keepalived安装配置以后）
[Postgres从2（192.168.70.30）]
将主库数据拷贝至从库
# systemctl stop postgresql-9.6
# su postgres
bash-4.2$ cd /var/lib/pgsql/9.6
bash-4.2$ rm -rf data
bash-4.2$ /usr/pgsql-9.6/bin/pg_basebackup -h 192.168.70.66 -U replica -D /var/lib/pgsql/9.6/data/ -X stream -P
bash-4.2$ mkdir -p /var/lib/pgsql/9.6/data/pg_archive
bash-4.2$ cd /var/lib/pgsql/9.6/data/ && vim recovery.conf
配置recovery.conf
standby_mode = on #说明该节点是从服务器
primary_conninfo = 'host=192.168.70.66 port=5432 user=replica password=123456' #主节点的信息以及连接的用户
recovery_target_timeline = 'latest'
配置postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
重启postgres
# systemctl restart postgresql-9.6
Keepalived
安装Keepalived
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20）]
# yum install keepalived -y
# touch /etc/keepalived/pg_check.sh
# chmod +x /etc/keepalived/pg_check.sh
pg_check.sh脚本内容
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20）]
#!/bin/bash#判断pg是否活着
A=`ps -ef | grep postgres | grep "/usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/" | wc -l`
#判断vip浮到哪里
B=`ip a | grep 192.168.70.66 | 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 ];thenecho "`date "+%Y-%m-%d--%H:%M:%S"` postgresql stop so vip stop " >> /etc/keepalived/log/check_pg.logsystemctl stop keepalivedelse#判断出主挂了，vip浮到了从，提升从的地位让他可读写if [ $B -eq 1 -a $C -eq 1 -a $D -eq 0 ];thensu - postgres -c "/usr/pgsql-9.6/bin/pg_ctl promote -D /var/lib/pgsql/9.6/data/"echo "`date "+%Y-%m-%d--%H:%M:%S"` standby promote " >> /etc/keepalived/log/check_pg.logfi#判断出自己是主并且和从失去联系if [ $B -eq 1 -a $C -eq 0 -a $D -eq 0 -a $E -eq 0 ];thensleep 10echo "`date "+%Y-%m-%d--%H:%M:%S"` can't find standby " >> /etc/keepalived/log/check_pg.logfi
配置Keepalived日志
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20）]
Keepalived日志默认存放在系统日志/var/log/messages下，通过如下命令查看
# tail -f /var/log/messages
现将Keepalived日志单独存放，修改/etc/sysconfig/keepalived，把KEEPALIVED_OPTIONS="-D" 修改为KEEPALIVED_OPTIONS="-D -d -S 0"
# vim /etc/sysconfig/keepalived
# Options for keepalived. See `keepalived --help' output and keepalived(8) and
# keepalived.conf(5) man pages for a list of all options. Here are the most
# common ones :
#
# --vrrp -P Only run with VRRP subsystem.
# --check -C Only run with Health-checker subsystem.
# --dont-release-vrrp -V Dont remove VRRP VIPs & VROUTEs on daemon stop.
# --dont-release-ipvs -I Dont remove IPVS topology on daemon stop.
# --dump-conf -d Dump the configuration data.
# --log-detail -D Detailed log messages.
# --log-facility -S 0-7 Set local syslog facility (default=LOG_DAEMON)
#KEEPALIVED_OPTIONS="-D -d -S 0"
修改/etc/rsyslog.conf文件，在末尾添加如下配置
local0.* /var/log/keepalived.log
重启日志记录服务
# systemctl restart rsyslog
查看Keepalived日志
# tail -f /var/log/keepalived.log
修改Keepalived配置文件
postgres主（192.168.70.10）
# mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {**@qq.com}notification_email_from **@163.comsmtp_server smtp.163.comsmtp_connect_timeout 30router_id postgres_master
}vrrp_script check_pg_alived {script "/etc/keepalived/pg_check.sh"interval 10fall 5
}vrrp_instance VI_1 {state BACKUPnopreemptinterface ens33virtual_router_id 66priority 100advert_int 1authentication {auth_type PASSauth_pass centos}track_script {check_pg_alived}virtual_ipaddress {192.168.70.66}
}
postgres从1（192.168.70.20）
! Configuration File for keepalivedglobal_defs {notification_email {**@qq.com}notification_email_from **@163.comsmtp_server smtp.163.comsmtp_connect_timeout 30router_id postgres_backup1
}vrrp_script check_pg_alived {script "/etc/keepalived/pg_check.sh"interval 10fall 5
}vrrp_instance VI_1 {state BACKUPnopreemptinterface ens33virtual_router_id 66priority 90advert_int 1authentication {auth_type PASSauth_pass centos}track_script {check_pg_alived}virtual_ipaddress {192.168.70.66}
}
重启Keepalived服务
[Postgres主（192.168.70.10），Postgres从1（192.168.70.20）]
# systemctl restart keepalived
# systemctl enable keepalived
# systemctl status keepalived