CentOS7双机热备(pgpool+replication)(2)

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