设置主备机免密码传输
主机: 172.17.254.90
cd ~/.ssh
ssh-keygen -t rsa
cat id_rsa.pub >>authorized_keys #本机ssh信任
ssh-copy-id -i root@172.17.254.91 #他机ssh信任
备机: 172.17.254.91
cd ~/.ssh
ssh-keygen -t rsa
cat id_rsa.pub >>authorized_keys #本机ssh信任
ssh-copy-id -i root@172.17.254.90 #他机ssh信任
安装pgpool
yum install http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-6-x86_64/pgpool-II-pg93-3.4.1-3pgdg.rhel6.x86_64.rpm
yum install http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-6-x86_64/pgpool-II-pg93-extensions-3.4.1-3pgdg.rhel6.x86_64.rpm
cp /usr/pgsql-9.3/lib/* /opt/postgres/9.3/lib/postgresql
cp /usr/pgsql-9.3/share/extension/* /opt/postgres/9.3/share/postgresql/extension
/opt/postgres/9.3/bin/psql -U postgres -f /opt/postgres/9.3/share/postgresql/extension/pgpool-regclass.sql template1
/opt/postgres/9.3/bin/psql -U postgres -f /opt/postgres/9.3/share/postgresql/extension/pgpool-recovery.sql template1
/opt/postgres/9.3/bin/psql -U postgres -f /usr/share/pgpool-II/insert_lock.sql template1
#在既存数据库上执行:
CREATE EXTENSION pgpool_regclass;CREATE EXTENSION pgpool_recovery;
#开启pgpool服务
chkconfig pgpool on
设置主机配置文件
vi /opt/postgres/9.3/data/postgres.conf
wal_level = hot_standby #日志模式为hot_standby
max_wal_senders = 5 #wal_senders数目
wal_keep_segments = 256 #主备同期用的wal数目,每个16M(设置的大一点,以防基础备份时间太长,主库数据库变化太大,超过16M*256,则备机恢复失败)
synchronous_standby_names = 'standby1' #设置同步备机名字
synchronous_commit=on #on:同步备库到磁盘
#创建异步的配置文件,备机挂了后启用,否则主机写操作一直等待
cp postgresql.conf postgresql.conf.async
chown postgres:postgres postgresql.conf.async
vi postgresql.conf.async:
synchronous_standby_names = ''
#配置replication用户
vi pg_hba.conf
host replication postgres 0.0.0.0/0 md5
设置备机配置文件
vi postgresql.conf
hot_standby = on
cp /opt/postgres/9.3/share/postgresql/recovery.conf.sample /opt/postgres/9.3/data/recovery.conf
chown postgres:postgres /opt/postgres/9.3/data/recovery.conf
vi recovery.conf
standby_mode = on
primary_conninfo = 'host=172.17.254.90 port=5432 user=postgres password=sa application_name=standby1' #连接主机的字符串
trigger_file = '/tmp/trigger_file0' #备机recover的触发文件
recovery_target_timeline = latest
其他设置
cd /etc/pgpool-II
cp pcp.conf pcp.conf.bk
cp pgpool.conf pgpool.conf.bk
cp pool_hba.conf pool_hba.conf.bk
vi /etc/pgpool-II/pcp.conf:
postgres:c12e01f2a13ff5587e1e9e4aedb8242d #密码: pg_md5 sa
vi pool_hba.conf:
host all all 0.0.0.0/0 md5
vi pool_passwd:
postgres:md5a5e20f3305a136f537aabcbfa853f467 #密码:从以下语句获得
pacer:md5e179eed94de3a74d9da4ffd2a23e74d6 #select rolname,rolpassword from pg_authid
cat pgpool.conf.sample-stream >pgpool.conf #以pgpool.conf.sample-stream为蓝本修改
主机设置
enable_pool_hba = on
listen_addresses = '*'
backend_hostname0 = '172.17.254.90' #pg主机
backend_port0 = 5432
backend_weight0 = 1 #pg主机负载均衡权重
backend_hostname1 = '172.17.254.91' #pg备机
backend_port1 = 5432
backend_weight1 = 999999 #pg备机负载均衡权重
sr_check_user = 'postgres'
sr_check_password = 'sa'
num_init_children = 300 #pgpool子进程数量
log_destination = 'syslog'
health_check_period = 5 #健康检查的间隔
health_check_max_retries = 3 #健康检查重试次数
health_check_user = 'postgres'
health_check_password = 'sa'
failover_command = '/opt/postgres/9.3/bin/failover_stream.sh %d %H /tmp/trigger_file0' #failover脚本
delay_threshold = 10000000 #可接受的延迟数据量,超过此数据量,查询发送到主机
recovery_user = 'postgres'
recovery_password = 'sa'
#watchdog
use_watchdog = on
wd_hostname = '172.17.254.90' #本机ip
delegate_IP = '172.17.254.89' #虚拟ip,主备一致
if_up_cmd = 'ifconfig eth4:0 inet $_IP_$ netmask 255.255.255.0 #eth4为ip对应的网卡
if_down_cmd = 'ifconfig eth4:0 down'
heartbeat_destination0 = '172.17.254.91'#pgpool备机ip
heartbeat_destination_port0 = 9694
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'sa'
other_pgpool_hostname0 = '172.17.254.91'#pgpool备机ip
other_pgpool_port0 = 5432
other_wd_port0 = 9000
heartbeat_device0 = 'eth4' #ip对应的网卡
备机设置
enable_pool_hba = on
listen_addresses = '*'
backend_hostname0 = '172.17.254.90'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = '172.17.254.91'
backend_port1 = 5432
backend_weight1 = 999999
sr_check_user = 'postgres'
sr_check_password = 'sa'
num_init_children = 300
log_destination = 'syslog'
health_check_period = 5
health_check_max_retries = 3
health_check_user = 'postgres'
health_check_password = 'sa'
failover_command = '/opt/postgres/9.3/bin/failover_stream.sh %d %H /tmp/trigger_file0'
delay_threshold = 10000000
recovery_user = 'postgres'
recovery_password = 'sa'
#watchdog
use_watchdog = on
wd_hostname = '172.17.254.91'
delegate_IP = '172.17.254.89'
if_up_cmd = 'ifconfig eth6:0 inet $_IP_$ netmask 255.255.255.0
if_down_cmd = 'ifconfig eth6:0 down'
heartbeat_destination0 = '172.17.254.90'
heartbeat_destination_port0 = 9694
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'sa'
other_pgpool_hostname0 = '172.17.254.90'
other_pgpool_port0 = 5432
other_wd_port0 = 9000
heartbeat_device0 = 'eth6'
备机基础备份
#注意修改主机data目录下所有文件所属为postgres
/opt/postgres/9.3/bin/pg_basebackup -D /opt/postgres/9.3/data -Fp -Xs -v -P -h 172.17.254.90 -p 5432 -U postgres
#修改data目录下所有文件所属为postgres
chown -R postgres:postgres /opt/postgres/9.3/data
重启postgres
#主机:
service postgres-9.3-openscg restart
#备机:
service postgres-9.3-openscg restart
启动pgpool
#主机:
service pgpool start
#备机:
service pgpool start
检证
/opt/postgres/9.3/bin/psql -h 172.17.254.91 -p 5432 -U postgres -c "select * from test;";
/opt/postgres/9.3/bin/psql -h 172.17.254.89 -p 5432 -U postgres -c "select * from test;";
/opt/postgres/9.3/bin/psql -h 172.17.254.91 -p 5432 -U postgres -c "select now()-pg_last_xact_replay_timestamp();"
来源张永光的博客