CentOS7双机热备(pgpool+replication)

1、1. 环境信息22. postgresql21. 依赖包22. 调整内核23. 服务配置34. 创建用户35. 创建目录36. 建立ssh信任37. 环境变量48. 编译安装49. 初始化实例510. 运行参数511. 启动实例712. 复制备机713. 注册服务73. pgpool81. 创建用户82. 开放权限83. 环境变量84. 建立ssh信任85. 编译安装96. 加载extension97. 配置pgpool.conf98. 配置pool_hba.conf109. 配置pcp.conf1110. 启动pgpool1111. 状态检查111. 环境信息操作系统：CentOS7数据库：

2、Postgresql9.3.5中间层：Pgpool-II-3.4.0Primary：1 Standby：2Virtual IP：02. postgresql1. 依赖包root主/备通过yum安装，需要联网#yum -y install gcc#yum -y install python-devel#yum -y install ncurses-devel#yum -y install readline-devel#yum -y install zlib-devel#yum -y install libxml2-devel#yum -

3、y install libxslt-devel#yum -y install sendmail2. 调整内核root主/备修改内核#vi /etc/sysctl.confkernel.shmmax = 68719476736kernel.shmall = 4294967296kernel.shmmni = 4096kernel.sem = 50100 64128000 50100 1280fs.file-max = 7672460net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_default = 1048576net.core.rm

4、em_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576生效参数#/sbin/sysctl -p3. 服务配置root主/备关闭防火墙(也可以设置协议规则，不关闭防火墙)#systemctl stop firewalld.service #systemctl disable firewalld.service添加主机名称(方便后续配置和管理)#vi /etc/hosts1 DBServer012 DBServer024. 创建用户root主/备创建用户组#group

5、add postgres创建用户#useradd -d /opt/postgres -s /bin/bash -g postgres -m postgres#passwd postgres5. 创建目录 postgres主/备修改权限(pgpool用户需要读取pg的环境变量)#chmod 750 添加目录(源码上传路径)#mkdir /src添加目录(辅助脚本路径)#mkdir /script6. 建立ssh信任postgres主/备生成ssh key(密码设置为空)#ssh-keygen设置对端postgres信任，hostname根据实际情况修改#ssh-copy-id postgresD

6、BServer027. 环境变量postgres主/备修改profile#vi /.bash_profileexport PGHOME=/opt/postgresexport PATH=$PATH:$PGHOME/binexport LD_LIBRARY_PATH=$PGHOME/libexport LANG=zh_CN.UTF8export PGUSER=postgresexport PGDATA=$PGHOME/dataexport PGLOG=$PGHOME/logexport PGSCRIPT=$PGHOME/script 生效profile#source /.bash_profile

7、8. 编译安装postgres主/备ftp上传postgresql-9.3.5.tar.gz至/opt/postgres/src#cd /src#tar -zxvf postgresql-9.3.5.tar.gz#cd postgresql-9.3.5#./configure -prefix=/opt/postgres -with-python -with-blocksize=16 -enable-debug -with-libxml -with-libxslt#make world#make install-worldftp上传pg_main.sh和pg_main.ini至/opt/post

8、gres/script#chmod 750 /script/*配置pg_main.ini#vi pg_main.ini#脚本的日志路径log=$PGLOG#物理备份路径base=$PGHOME/backup/base#WAL日志归档路径archive=$PGHOME/backup/archive#备份保存天数的阈值keepdays=3#主备机的hostname列表nodes=DBServer01|DBServer029. 初始化实例postgres主初始化实例#initdb -D $PGDATA -encoding=UTF8 -W10. 运行参数postgres主配置postgresql.co

9、nf#vi /data/postgresql.conf#监听所有地址listen_addresses = *#监听端口port = 5432#WAL日志级别，复制流必须打开wal_level = hot_standby#归档模式，复制流必须打开archive_mode = on#归档脚本，可按照实际情况配置archive_command = sh $PGSCRIPT/pg_main.sh archive %p %f#复制流WAL日志发送进程数max_wal_senders = 3#WAL日志段数量wal_keep_segments = 16#热备模式hot_standby = on#系统日志设

10、置为标准输出log_destination = stderr#系统日志转储进程logging_collector = on#系统日志目录log_directory = /opt/postgres/log#系统日志格式log_filename = postgresql-%Y%m%d.log配置pg_hba.conf(红字部分按实际情况配置)#vi /data/pg_hba.confhost all all /0 md5host replication postgres 2/32 trust配置recovery.conf(红字部分按实际情况配置)#cp /shar

11、e/recovery.conf.sample /data/recovery.done#vi /data/recovery.done#备机恢复timelinerecovery_target_timeline = latest#备机模式standby_mode = on#主机的信息primary_conninfo = host=DBServer02#备机提升为主机的触发文件trigger_file = /opt/postgres/postgres.trigger11. 启动实例postgres主启动实例#pg_ctl start12. 复制备机postgres备从主机复制(红字部分按实际情况配置)

12、#pg_basebackup -D $PGDATA -Fp -Xs -v -P -h DBServer01 -U postgres修改recovery.conf(红字部分按实际情况配置)#mv /data/recovery.done /data/recovery.conf#vi /data/recovery.confprimary_conninfo = host=DBServer01修改pg_hba.conf(红字部分按实际情况配置)#vi /data/pg_hba.confhostreplicationpostgres1/32trust启动实例#pg_ctl start检

13、查进程#ps -ef | grep receiverpostgres 21527 21521 0 20:01 ? 00:00:00 postgres: wal receiver process13. 注册服务root 主/备ftp上传postgres至/etc/init.d(可以从/opt/postgres/src/postgresql-9.3.5/contrib/start-scripts/linux拷贝修改)#chkconfig -add postgres#chkconfig postgres on3. pgpool1. 创建用户root主/备#useradd -d /opt/pgpool

14、 -s /bin/bash -g postgres -m pgpool#passwd pgpool2. 开放权限root 主/备生成虚拟ip用到#chmod u+s /sbin/ifconfig#chmod u+s /sbin/arping3. 环境变量pgpool主/备修改profile#vi /.bash_profile. /opt/postgres/.bash_profileexport PATH=$PATH:$HOME/bin生效profile#source /.bash_profile4. 建立ssh信任生成ssh key(密码设置为空)#ssh-keygen本机postgres信任

15、#ssh-copy-id postgreshostname5. 编译安装pgpool主/备添加目录#mkdir /src解压编译ftp上传pgpool-II-3.4.0.tar.gz至/opt/pgpool/src#cd /src#tar -zxvf pgpool-II-3.4.0.tar.gz#cd /src/pgpool-II-3.4.0#./configure -prefix=/opt/pgpool -with-pgsql=/opt/postgres#make#make install#cd /src/pgpool-II-3.4.0/src/sql#make#make install6.

16、 加载extensionpgpool主备机会自动复制操作，template1是pg创建database的默认模板#psql -h DBServer01 -U postgres -d template1 -c create extension pgpool_regclass;#psql -h DBServer01 -U postgres -d template1 -c create extension pgpool_recovery;7. 配置pgpool.confpgpool主/备以sample-stream为模板，以下列出的是需要更改的配置(红字部分按实际情况配置)#cp /etc/pgpo

17、ol.conf.sample-stream /etc/pgpool.conf#vi /etc/pgpool.conf#监听地址listen_addresses = *#节点信息backend_hostname0 = DBServer01backend_port0 = 5432backend_weight0 = 1backend_data_directory0 = /opt/postgres/databackend_flag0 = ALLOW_TO_FAILOVERbackend_hostname1 = DBServer02backend_port1 = 5432backend_weight1

18、= 1backend_data_directory1 = /opt/postgres/databackend_flag1 = ALLOW_TO_FAILOVER#认证方式enable_pool_hba = on#日志模式log_connections = onlog_hostname = onlog_statement = onlog_per_node_statement = onpid_file_name = /opt/pgpool/pgpool.pid#状态检查sr_check_user = postgressr_check_password = postgreshealth_check_

19、period = 1health_check_user = postgreshealth_check_password = postgres#切换脚本failover_command = ssh postgresDBServer02 $PGSCRIPT/pg_main.sh promote %H #看门狗(避免单点故障)use_watchdog = onwd_hostname = DBServer01delegate_IP = 0 heartbeat_destination0 = DBServer02heartbeat_device0 = eth0other_pgpool_

20、hostname0 = DBServer02other_pgpool_port0 = 9999other_wd_port0 = 90008. 配置pool_hba.confpgpool主/备认证文件#cp /etc/pool_hba.conf.sample /etc/pool_hba.conf#vi /etc/pool_hba.confhost all all /0 md5密码文件(pgsql中所有user需要在此配置，密码需要一致)#pg_md5 -p -u postgres9. 配置pcp.confpgpool主/备执行pcp命令时的用户认证(红字部分密码可以任意修改)#cp /etc/pcp.conf.sample /etc/pcp.conf#echo postgres:pg_md5 postgres /etc/pcp.conf10. 启动pgpoolpgpool主/备先启动的pgpo