本文简单的讲述一下PostgreSQL跨广域网的容灾.
1. 广域网首先要考虑网络稳定情况, 丢包率等.
使用PostgreSQL 流复制不依赖网络稳定情况, 所以比较有弹性.
通过配置archive_command, 或者wal_keep_segments保证有足够的pg_xlog文件, 确保网络极度不稳定或者长时间中断的情况下不需要重做基础备份.
2. 广域网环境
同时还需要考虑数据被截取的可能.
使用ssh建立隧道, 加密传输数据.
3.
广域网环境还需要考虑
带宽的问题.
使用ssh建立隧道, 增加数据压缩选项.
环境 :
IDC1
IDC1_server1
IDC2
ssh-keygen -t rsaIDC2
IDC2_server1
将id_rsa.pub拷贝到~/.ssh/authorized_keys
IDC2_db1 : -- 主库
chmod 700 ~
chmod 700 ~/.ssh
chmod 400 ~/.ssh/authorized_keysIDC2_db1 : -- 主库
IDC1_server1 :
ssh -o TCPKeepAlive=90 -o ServerAliveInterval=10 -o ServerAliveCountMax=10 -o CompressionLevel=9 -p 22 -CqTfnN -L *:17100:IDC2_db1_ip:5432 postgres@IDC2_server1_ip
将在本地启动一个监听17100端口. 访问17100端口相当于通过IDC2_server1访问IDC2_db1_ip:5432端口.
数据加密和压缩发生在IDC1_server1和IDC2_server1之间.
数据库pg_hba.conf应该开放IDC2_server1的访问许可.
[监控]
# cat check_pg_rongzai.sh
配置nagios
#!/bin/bash
export LANG=en_US.utf8
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'
if [ $1 == "digoal" ]; then
# 监控本地ssh代理监听端口
echo -e "192.168.1.2 PostgreSQL rongzai."
CNT=`netstat -anpo|grep 15432|grep -v grep|grep LISTEN|wc -l`
if [ $CNT -eq 0 ]; then
echo -e "15432 LISTEN Failed."
exit 2
fi
# 监控数据库监听端口
CNT=`netstat -anpo|grep 5432|grep -v grep|grep LISTEN|wc -l`
if [ $CNT -eq 0 ]; then
echo -e "5432 LISTEN Failed."
exit 2
fi
# 监控数据库流复制接收进程
CNT=`ps -ewf|grep -v grep|grep postgres|grep "wal receiver"|wc -l`
if [ $CNT -eq 0 ]; then
echo -e "postgres wal receiver Failed."
exit 2
fi
# 监控流复制延迟
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c "select 'standby_good' as stat from (select (pg_stat_file('pg_xlog/'||i)).*,i from pg_ls_dir('pg_xlog') i)t where now()-modification< interval '12 min' limit 1;"|grep standby_good
if [ $? -ne 0 ]; then
echo -e "postgres standby lagged 30 minutes."
exit 2
fi
exit 0
fi
echo -e "参数错误"
exit 1
# Author: digoal.zhou配置nagios
vi /usr/local/nagios/etc/nrpe.cfg
command[check_pg_rongzai1]=/root/script/check_pg_rongzai.sh digoal
service xinetd restart
ssh 广域网加速补丁.
