Postgresql基于流复制 (streaming replication)的hot-standby
Primary:
l 归档设置:
Wal_level=hot_standby
Hot_standby=on
Archive_mode=on
archive_command = 'cp -i %p /data/pgsql/archived_wal/%f'
l 流复制相关设置:
max_wal_senders = '10' #启动复制进程数量限制,必须大于0
max_replication_slots = '10' #为使用replication slot,必须大于0;replication slot作用是保证wal没有同步到standby之前不能从pg_xlog移走;
wal_keep_segments = '50' #指定pg_xlog中最少保留的wal数量
select pg_create_physical_replication_slot(‘gp1_a_slot’); #创建replication slot
select * from pg_replication_slots; #查询创建的replication slot
l 编辑pg_hba.conf
# Allow replication connections from localhost, by a user with the replication privilege.
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust
local replication postgres trust
host replication postgres 192.168.12.0/24 trust
l 联机备份过程(基础备份)
#touch /var/lib/pgsql/backup_in_progress
$psql –c "select pg_start_backup('hot_backup');"
$tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
$psql -c "select pg_stop_backup();"
#rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/ #打包归档
Standby:
l 编辑recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.12.38 port=5666 user=postgres'
primary_slot_name='gp1_a_slot'
#restore_command = 'cp /data/pgsql/archived_wal/%f %p'
#archive_cleanup_command = 'pg_archivecleanup /data/pgsql/archived_wal %r'
l 将primary上的基础备份传输到standby上
$scp primary: /var/lib/pgsql/backup.tar .
解压备份到standby上的$PGDATA
l 启动standby
$pg_ctl start –D $PGDATA
启动standby后,postgres开始从primary上接收wal日志进行恢复,并且数据库保持:read only connect状态,psql能登录进行查询操作;
Hot-Standby,只读状态下的可以执行的操作:
1. 查询,所有select
2. Checkpoint,可以执行不报错,但是不产生log
3. Select pg_create_physical_replication_slot(‘slot_name’); 等操作,如drop replication slot;
Switchover:
Hot-standby进行主备切换时注意事项:
1. 必须先停止primary,然后再在standby上pg_ctl promote;
2. 为方便快速切换,主备配置尽量相同,并提前准备妥当,例如 .pgpass,pg_hba.conf ,recovery.conf等;
3. 使用replication slot时,主备分别指定不同primary_slot_name
主备切换步骤:
A. 主备分别准备recovery.conf,注意修改primary_conninfo和primary_slot_name参数设置
B. 停止主数据库
C. Standby上,pg_ctl promote,或者创建触发文件(切换成主库)
D. 启动原主库,切换为备份库
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16976507/viewspace-2133409/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16976507/viewspace-2133409/