CentOS7双机热备(pgpool+replication)

1. 环境信息 ..................................................................................................................................... 2 2. postgresql ................................................................................................................................. 2

1. 依赖包 ................................................................................................................................. 2 2. 调整内核 ............................................................................................................................. 2 3. 服务配置 ............................................................................................................................. 3 4. 创建用户 ............................................................................................................................. 3 5. 创建目录 ............................................................................................................................. 3 6. 建立ssh信任 ..................................................................................................................... 3 7. 环境变量 ............................................................................................................................. 4 8. 编译安装 ............................................................................................................................. 4 9. 初始化实例 ......................................................................................................................... 5 10. 运行参数 ........................................................................................................................... 5 11. 启动实例 ........................................................................................................................... 7 12. 复制备机 ........................................................................................................................... 7 13. 注册服务 ........................................................................................................................... 7 3. pgpool......................................................................................................................................... 8

1. 创建用户 ............................................................................................................................. 8 2. 开放权限 ............................................................................................................................. 8 3. 环境变量 ............................................................................................................................. 8 4. 建立ssh信任 ..................................................................................................................... 8 5. 编译安装 ............................................................................................................................. 9 6. 加载extension .................................................................................................................. 9 7. 配置pgpool.conf .............................................................................................................. 9 8. 配置pool_hba.conf ........................................................................................................ 10 9. 配置pcp.conf .................................................................................................................. 11 10. 启动pgpool .................................................................................................................... 11 11. 状态检查 ......................................................................................................................... 11

1. 环境信息

操作系统：CentOS7 数据库：Postgresql9.3.5 中间层：Pgpool-II-3.4.0

Primary：172.15.0.11 Standby：172.15.0.12 Virtual IP：172.15.0.10

2. postgresql

1. 依赖包

[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 -y install libxslt-devel #yum -y install sendmail

2. 调整内核

[root主/备]

修改内核

#vi /etc/sysctl.conf

kernel.shmmax = 68719476736 kernel.shmall = 4294967296 kernel.shmmni = 4096

kernel.sem = 50100 64128000 50100 1280 fs.file-max = 7672460

net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 1048576 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576

生效参数

#/sbin/sysctl -p

3. 服务配置

[root主/备]

关闭防火墙(也可以设置协议规则，不关闭防火墙) #systemctl stop firewalld.service #systemctl disable firewalld.service

添加主机名称(方便后续配置和管理) #vi /etc/hosts

172.15.0.11 DBServer01 172.15.0.12 DBServer02

4. 创建用户

[root主/备]

创建用户组

#groupadd postgres

创建用户

#useradd -d /opt/postgres -s /bin/bash -g postgres -m postgres #passwd postgres

5. 创建目录

[postgres主/备]

修改权限(pgpool用户需要读取pg的环境变量) #chmod 750 ~

添加目录(源码上传路径) #mkdir ~/src

添加目录(辅助脚本路径) #mkdir ~/script

6. 建立ssh信任

[postgres主/备]

生成ssh key(密码设置为空) #ssh-keygen

设置对端postgres信任，hostname根据实际情况修改 #ssh-copy-id postgres@DBServer02

7. 环境变量

[postgres主/备]

修改profile #vi ~/.bash_profile

export PGHOME=/opt/postgres export PATH=$PATH:$PGHOME/bin export LD_LIBRARY_PATH=$PGHOME/lib export LANG=zh_CN.UTF8 export PGUSER=postgres export PGDATA=$PGHOME/data export PGLOG=$PGHOME/log export PGSCRIPT=$PGHOME/script

生效profile

#source ~/.bash_profile

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-world

ftp上传pg_main.sh和pg_main.ini至/opt/postgres/script

pg_main.shpg_main.ini

#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=\

9. 初始化实例

[postgres主]

初始化实例

#initdb -D $PGDATA --encoding=UTF8 -W

10. 运行参数

[postgres主]

配置postgresql.conf #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

#系统日志设置为标准输出 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.conf

host all all 0.0.0.0/0 md5 host replication postgres 172.15.0.12/32 trust

配置recovery.conf(红字部分按实际情况配置)

#cp ~/share/recovery.conf.sample ~/data/recovery.done #vi ~/data/recovery.done

#备机恢复timeline

recovery_target_timeline = 'latest'

#备机模式

standby_mode = on

#主机的信息

primary_conninfo = 'host=DBServer02'

#备机提升为主机的触发文件

trigger_file = '/opt/postgres/postgres.trigger'

11. 启动实例

[postgres主]

启动实例 #pg_ctl start

12. 复制备机

[postgres备]

从主机复制(红字部分按实际情况配置)

#pg_basebackup -D $PGDATA -Fp -Xs -v -P -h DBServer01 -U postgres

修改recovery.conf(红字部分按实际情况配置) #mv ~/data/recovery.done ~/data/recovery.conf #vi ~/data/recovery.conf

primary_conninfo = 'host=DBServer01'

修改pg_hba.conf(红字部分按实际情况配置) #vi ~/data/pg_hba.conf

host replication postgres 172.15.0.11/32 trust

启动实例 #pg_ctl start

检查进程

#ps -ef | grep receiver

postgres 21527 21521 0 20:01 ? 00:00:00 postgres: wal receiver process

13. 注册服务

[root 主/备]

ftp上传postgres至/etc/init.d

(可以从/opt/postgres/src/postgresql-9.3.5/contrib/start-scripts/linux拷贝修改)

postgres

#chkconfig --add postgres #chkconfig postgres on

3. pgpool

1. 创建用户

[root主/备]

#useradd -d /opt/pgpool -s /bin/bash -g postgres -m pgpool #passwd pgpool

2. 开放权限

[root 主/备]

生成虚拟ip用到

#chmod u+s /sbin/ifconfig #chmod u+s /sbin/arping

3. 环境变量

[pgpool主/备]

修改profile #vi ~/.bash_profile

. /opt/postgres/.bash_profile export PATH=$PATH:$HOME/bin

生效profile

#source ~/.bash_profile

4. 建立ssh信任

生成ssh key(密码设置为空) #ssh-keygen

本机postgres信任

#ssh-copy-id postgres@`hostname`

5. 编译安装

[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 install

6. 加载extension

[pgpool主]

备机会自动复制操作，template1是pg创建database的默认模板

#psql -h DBServer01 -U postgres -d template1 -c \extension pgpool_regclass;\

#psql -h DBServer01 -U postgres -d template1 -c \extension pgpool_recovery;\

7. 配置pgpool.conf

[pgpool主/备]

以sample-stream为模板，以下列出的是需要更改的配置(红字部分按实际情况配置) #cp ~/etc/pgpool.conf.sample-stream ~/etc/pgpool.conf #vi ~/etc/pgpool.conf

#监听地址

listen_addresses = '*'

#节点信息

backend_hostname0 = 'DBServer01' backend_port0 = 5432 backend_weight0 = 1

backend_data_directory0 = '/opt/postgres/data' backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'DBServer02' backend_port1 = 5432 backend_weight1 = 1

backend_data_directory1 = '/opt/postgres/data' backend_flag1 = 'ALLOW_TO_FAILOVER'

#认证方式

enable_pool_hba = on

#日志模式

log_connections = on log_hostname = on log_statement = on

log_per_node_statement = on

pid_file_name = '/opt/pgpool/pgpool.pid'

#状态检查

sr_check_user = 'postgres' sr_check_password = 'postgres' health_check_period = 1 health_check_user = 'postgres' health_check_password = 'postgres'

#切换脚本

failover_command = 'ssh postgres@DBServer02 \promote %H \

#看门狗(避免单点故障) use_watchdog = on

wd_hostname = 'DBServer01' delegate_IP = '172.15.0.10' heartbeat_destination0 = 'DBServer02' heartbeat_device0 = 'eth0'

other_pgpool_hostname0 = 'DBServer02' other_pgpool_port0 = 9999 other_wd_port0 = 9000

8. 配置pool_hba.conf

[pgpool主/备]

认证文件

#cp ~/etc/pool_hba.conf.sample ~/etc/pool_hba.conf #vi ~/etc/pool_hba.conf

host all all 0.0.0.0/0 md5

密码文件(pgsql中所有user需要在此配置，密码需要一致) #pg_md5 -p -u postgres

9. 配置pcp.conf

[pgpool主/备]

执行pcp命令时的用户认证(红字部分密码可以任意修改) #cp ~/etc/pcp.conf.sample ~/etc/pcp.conf

#echo \postgres` >>~/etc/pcp.conf

10. 启动pgpool

[pgpool主/备]

先启动的pgpool节点会为master状态，后启动的为normal状态 #pgpool -n > /opt/pgpool/pgpool.log 2>&1 &

11. 状态检查

[pgpool主]

使用pgpool的pcp命令，检查watchdog的状态

#pcp_watchdog_info 5 DBServer01 9898 postgres postgres

node1 9999 9000 3

#pcp_watchdog_info 5 DBServer02 9898 postgres postgres

Node2 9999 9000 2

watchdog状态说明：

0 End 使用通过vip查看据库状态 #psql -h 172.15.0.10 -p 9999 -U postgres -c \ node_id | hostname | port | status | lb_weight | role ---------+----------+------+--------+-----------+--------- 0 | node1 | 5432 | 2 | 0.500000 | primary 1 Init 2 Normal 3 Master 4 Down 1 | node2 | 5432 | 2 | 0.500000 | standby database状态说明： 0 当主机宕掉，然后重新启动为备机时，需要把节点信息更新一下，pgpool无法自动检测宕掉后重新恢复的节点。 pcp_attach_node [-d] timeout hostname port# username password nodeID 1 2 3 初始化，从不显示 已启动，未连接 已启动，连接缓冲 节点关闭

本文来源：https://www.bwwdw.com/article/4dhp.html