keepalived + haproxy + postgresql构建高可用数据库
1、搭建最低配测试环境(两台虚拟机)
keepalived 的高可用是主备，有一台作为备用
keepalived +
haproxy 搭建的高可用是可以两台都会调度的高可用
2、拓扑图
keepalived：负责抢占虚拟ip，使用vrrp协议
haproxy
：负责做访问调度，减轻单点压力，单独监听一个端口，这里用3306，用于区分postgres的5432端口。
3、资源规划
主机名称IP操作系统
haproxy1192.168.74.126Centos7 64位
haproxy2192.168.74.127Centos7 64位
Master192.168.74.126Centos7 64位
slave192.168.74.127Centos7 64位
VIP192.168.74.150\
应用服务器对只读的应用程序连接虚拟IP地址，连接到haproxy，然后通过haproxy将TCP协议转移到下面的2个postgres主从数据库服务器中。Haproxy在此做4层的TCP交换服务。keepalived为了防止haproxy单点故障。这里可以使用两个VIP（虚拟IP）实现haproxy代理服务器的高可用负载均衡。
4、安装介质
CentOS操作系统版本：CentOS-7-x86_64-DVD-1511.iso
postgres版本：postgresql11-11.2-1PGDG.rhel7.x86_64.rpm
postgresql11-libs-11.2-1PGDG.rhel7.x86_64.rpm
postgresql11-server-11.2-1PGDG.rhel7.x86_64.rpm
HAproxy版本：haproxy-1.8.14.tar.gz
keepalived版本：keepalived-1.2.7.tar.gz
5、准备工作（每台机器）
安装之前，如果之前安装过postgres，那么需要删除相应的各种postgres文件，删除之前请停止postgres服务。并且不要忘记删除postgres.cnf这些配置文件。确保删除干净。不然可能会和后面的安装有冲突。如果是实验，关闭防火墙，实际中，防火墙打开对应端口（注意实际中需要使用的端口不只有5432端口，还有同步需要使用的6677端口和23306端口）。保证服务器之前能互相访问，能ping通。保证固定的ip地址。保证没有别的程序占用需要使用的端口。如3306等。这些都确认完毕后再进行安装。
关闭防火墙
systemctl stop firewalld.service
systemctl disablefirewalld.service
挂载光盘（每台机器）
mount /dev/cdrom /mnt
删除原有的yum的源
cd /etc/yum.repos.d/
rm -rf *
创建自己的源vi my.repo
[centos-yum]
baseurl=file:///mnt
enabled=1
gpgcheck=0
NTP服务需要每台机器进行安装（时间同步）
yum install ntp
systemctl is-enabledntpd
systemctl enable ntpd
systemctl start ntpd
6、安装postgres
以下的所有操作需要在所有的集群节点（126/127）都要进行相同的操作
新建文件夹并赋予权限
mkdir/var/lib/pgsql/pg_archive/
chmod 777 -R/var/lib/pgsql/pg_archive/
将下载后的包上传至服务器/usr/local下
执行命令
cd /usr/local
yum install -ypostgresql11-libs-11.2-1PGDG.rhel7.x86_64.rpm
yum install -ypostgresql11-11.2-1PGDG.rhel7.x86_64.rpm
yum install -ypostgresql11-server-11.2-1PGDG.rhel7.x86_64.rpm
主节点配置：
初始化主节点：
/usr/pgsql-11/bin/postgresql-11-setupinitdb
systemctl enable postgresql-11
systemctl startpostgresql-11
配置pg_hba.conf：
vim/var/lib/pgsql/11/data/pg_hba.conf
#最后添加下面几行
host all all all md5
host all all 192.168.74.126/32 trust
host all all 192.168.74.127/32 trust
host replication replica 192.168.74.127/32 trust
配置postgresql.conf：
vim/var/lib/pgsql/11/data/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
synchronous_commit = on
archive_mode = on
archive_command = 'cp%p /var/lib/pgsql/pg_archive/%f'
max_wal_senders = 16
wal_keep_segments = 256
wal_sender_timeout =60s
hot_standby = on
max_standby_archive_delay= -1
max_standby_streaming_delay= -1
wal_receiver_status_interval= 2s
hot_standby_feedback =on
修改密码：
su postgres
$ psql
\password postgres
postgres
创建同步用户
su postgres
$ psql
CREATE ROLE replicalogin replication encrypted password 'replica';
重启主节点：
systemctl restartpostgresql-11
配置从节点
su postgres
pg_basebackup -h192.168.74.126 -U replica -D /var/lib/pgsql/11/data -X stream -P
配置recovery.conf：
cp/usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
vi/var/lib/pgsql/11/data/recovery.conf
standby_mode = on
primary_conninfo ='host=192.168.74.126 port=5432 user=replica password=replica'
recovery_target_timeline= 'latest'
restore_command = 'cp%p /var/lib/pgsql/pg_archive/%f %p'
配置postgresql.conf：
vim/var/lib/pgsql/11/data/postgresql.conf
max_connections = 200 #其中slave的max_connections要设置比master大
启动从节点：
systemctl startpostgresql-11
在master上登陆psql查看状态:
selectclient_addr,sync_state from pg_stat_replication;
至此，主从就配置完了，可以创建库表、增删记录等方式测试同步情况，这里就不在详述了
7、搭建haproxy
上传haproxy安装包到/usr/local
在74.126和74.127解压缩安装
tar -zxvfhaproxy-1.8.14.tar.tar
cd haproxy-1.8.14
yum install -y gcc
make TARGET=linux310
ARCH=x86_64 # uname -a查看主机信息填写
make installSBINDIR=/usr/sbin/ MANDIR=/usr/share/man/ DOCDIR=/usr/share/doc/
提供启动脚本
vim /etc/init.d/haproxy
#
# haproxy
#
# chkconfig: - 85 15
# description: HAProxy is a free, very fast and reliablesolution \
# offering high availability, loadbalancing, and \
# proxying for TCP and HTTP-based applications
# processname: haproxy
# config: /etc/haproxy/haproxy.cfg
# pidfile: /var/run/haproxy.pid
# Sourcefunctionlibrary.
./etc/rc.d/init.d/functions
# Source networkingconfiguration.
. /etc/sysconfig/network
# Check that networkingis up.
["$NETWORKING" = "no" ] && exit 0
exec="/usr/sbin/haproxy"
prog=$(basename $exec)
[ -e/etc/sysconfig/$prog ] && . /etc/sysconfig/$prog
cfgfile=/etc/haproxy/haproxy.cfg
pidfile=/var/run/haproxy.pid
lockfile=/var/lock/subsys/haproxy
check() {
$exec -c -V -f $cfgfile $OPTIONS
}
start() {
$exec -c -q -f $cfgfile $OPTIONS
if [ $? -ne 0 ]; then
echo "Errors in configurationfile, check with $prog check."
return 1
fi
echo -n $"Starting $prog: "
# start it up here, usually something like"daemon $exec"
daemon $exec -D -f $cfgfile -p $pidfile$OPTIONS
retval=$?
echo
[ $retval -eq 0 ] && touch$lockfile
return $retval
}
stop() {
echo -n $"Stopping $prog: "
# stop it here, often "killproc$prog"
killproc $prog
retval=$?
echo
[ $retval -eq 0 ] && rm -f$lockfile
return $retval
}
restart() {
$exec -c -q -f $cfgfile $OPTIONS
if [ $? -ne 0 ]; then
echo "Errors in configurationfile, check with $prog check."
return 1
fi
stop
start
}
reload() {
$exec -c -q -f $cfgfile $OPTIONS
if [ $? -ne 0 ]; then
echo "Errors in configuration file,check with $prog check."
return 1
fi
echo -n $"Reloading $prog: "
$exec -D -f $cfgfile -p $pidfile $OPTIONS-sf $(cat $pidfile)
retval=$?
echo
return $retval
}
force_reload() {
restart
}
fdr_status() {
status$prog
}
case "$1" in
start|stop|restart|reload)
$1
;;
force-reload)
force_reload
;;
check)
check
;;
status)
fdr_status
;;
condrestart|try-restart)
[ ! -f $lockfile ] || restart
;;
*)
echo $"Usage: $0{start|stop|status|restart|try-restart|reload|force-reload}"
exit2
esac
提供配置文件
mkdir /etc/haproxy
mkdir /var/lib/haproxy
useradd -r haproxy
vim/etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
defaults
mode tcp
log global
option dontlognull
option redispatch
retries 3
timeout http-request 10s
timeoutqueue 1m
timeout connect 10s
timeout client 1m
timeoutserver 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 600
listen stats
mode http
bind :6677
stats enable
stats hide-version
stats uri /haproxyadmin?stats
stats realm Haproxy\ Statistics
stats auth admin:admin
stats admin if TRUE
frontend main
bind *:23306
default_backend mysql
backend mysql
balance leastconn
server m1 192.168.74.126:5432 check port5432 maxconn 300
server m2 192.168.74.127:5432 check port5432 maxconn 300
修改日志系统
###Provides UDP syslog
reception //去掉下面两行注释，开启UDP监听
$ModLoad imudp
$UDPServerRun 514
local2.*
/var/log/haproxy.log //添加此行
service rsyslog restart
启动测试haproxy
service haproxy start
chkconfig --add haproxy
chkconfig haproxy on
netstat -tnlp
psql -h 192.168.74.126
-U postgres # 查看
server_id，判断是否成功
8、搭建keepalived
上传keepalived安装包到/usr/local
在74.126和74.127解压缩安装
tar xfkeepalived-1.2.7.tar.gz
cd keepalived-1.2.7
./configure --prefix=/usr/local/keepalived--sbindir=/usr/sbin/ --sysconfdir=/etc/ --mandir=/usr/local/share/man/
make && makeinstall
chkconfig --addkeepalived
chkconfig keepalived on
可能会报错1
!!! OpenSSL isnot properly installed on your system. !!!
!!! Can not includeOpenSSL headers files.
解决方法：
yum -y installopenssl-devel
可能会报错2
configure: error: Popt
libraries is required
解决方法：
yum install
popt-devel
提供配置文件
vim
/etc/keepalived/keepalived.conf # 两个机器配置文件不同
! Configuration Filefor keepalived
global_defs {
notification_email{ #忽略
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_fromAlexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script chk_haproxy{
script"/etc/keepalived/chk.sh" #检查haproxy的脚本
interval 2 #每两秒检查一次
}
vrrp_instance VI_1 {
stateBACKUP #定义为BACKUP节点
nopreempt #开启不抢占，另一个不写
interface ens33
virtual_router_id 51
priority 100 #开启了不抢占，所以此处优先级必须高于另一台，另一个写99
advert_int 1
authentication {
auth_type PASS
auth_pass abcd
}
virtual_ipaddress {
192.168.74.150 #配置VIP
}
track_script {
chk_haproxy #调用检查脚本
}
notify_backup "/etc/init.d/haproxyrestart"
notify_fault "/etc/init.d/haproxystop"
}
创建check文件
vim/etc/keepalived/chk.sh
#!/bin/bash
if [ $(ps -C haproxy--no-header | wc -l) -eq 0 ]; then
/etc/init.d/keepalived stop
fi
chmod +x/etc/keepalived/chk.sh
service keepalivedstart
9、测试
ip addr # 查看是否绑定了虚ip
tcpdump -nn -i
ens33 vrrp # 抓包查看
http://192.168.74.126:6677/haproxyadmin?stats
# 通过haproxy查看状态
测试无误后，重启postgres、Haproxy、Keepalived，顺序依次为postgres、Haproxy、Keepalived
systemctl start postgresql-11
service haproxy start
service keepalivedstart
测试mysql数据同步
通过对数据库进行增删改查操作，这里就不演示了
测试VIP高可用
systemctl startpostgresql-11 停止服务测试效果，VIP是否还能够连上数据库
psql -h 192.168.74.150-U postgres
测试haproxyl高可用
service haproxy stop 停止服务测试效果
http://192.168.74.126:6677/haproxyadmin?stats是否还能够访问
psql -h 192.168.74.150-U postgres
测试postgres高可用
service keepalived stop 停止服务测试效果
ip addr 查看VIP
psql -h 192.168.74.150
-U postgres 连接数据库
