首页 > 代码库 > postgresql异步流复制搭建
postgresql异步流复制搭建
节点 | IP | 角色 |
citus-master | 10.10.100.1 | master |
citus-standby | 10.10.100.2 | standby |
master上创建流复制所需要的用户。
CREATE ROLE replication WITH REPLICATION PASSWORD ‘replication‘ LOGIN;
修改master的pg_hba.conf文件,设置replication用户远程访问权限
## vim /data/pgsql/data/pg_hba.conf,追加下面一行 host replication replication 10.10.0.0/16 md5
master上设置与复制相关的参数
wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 32
master上重启pgsql
/etc/init.d/postgresql-9.5 restart
standby上使用pg_basebackup对master做一次全备并将备份拉到standby上来
pg_basebackup -h 10.10.100.1 -D /var/lib/pgsql/9.5/data/ -P -U replication -R --xlog-method=stream
修改standby节点上的postgresql.conf文件,设置备库为standby的状态
## vim /data/pgsql/data/postgresql.conf hot_standby = on
修改standby的recovery.done文件,设置master节点的同步信息及trigger文件
standby_mode = ‘on‘ primary_conninfo = ‘user=replication password=replication host=10.10.100.1 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres‘ trigger_file = ‘/data/pgsql/data/postgresql.trigger.1973‘
standby上重启pgsql
/etc/init.d/postgresql-9.5 restart
可以在master上创建一张测试表并插入数据,来验证standby上是否同步过来数据。当master挂掉之后,在standby上创建trigger_file参数声明的文件,就会触发standby的激活,会自动把standby提升为master。
当master挂掉之后,standby上创建trigger file之后,recovery.conf将会重命名为recovery.done。这时standby将会接管master进入可读写状态。紧接着可以将原来的master设置为现在的standby,过程如下
## 创建recovery.conf,写入下面内容 recovery_target_timeline = ‘latest‘ standby_mode = ‘on‘ primary_conninfo = ‘user=replication password=replication host=10.10.100.2 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres‘
## vim postgresql.conf文件,打开hot_standby hot_standby = on
最后重启pgsql,并查看日志
/etc/init.d/postgresql-9.5 restart tail -9f pg_log/postgresql-Tue.log < 2016-11-01 16:51:53.590 CST >LOG: shutting down < 2016-11-01 16:51:53.594 CST >LOG: database system is shut down < 2016-11-01 16:51:54.679 CST >LOG: database system was shut down in recovery at 2016-11-01 16:51:53 CST < 2016-11-01 16:51:54.679 CST >LOG: entering standby mode < 2016-11-01 16:51:54.692 CST >LOG: consistent recovery state reached at 0/F001430 < 2016-11-01 16:51:54.692 CST >LOG: redo starts at 0/F001430 < 2016-11-01 16:51:54.692 CST >LOG: database system is ready to accept read only connections < 2016-11-01 16:51:54.692 CST >LOG: invalid record length at 0/F001510 < 2016-11-01 16:51:54.697 CST >LOG: started streaming WAL from primary at 0/F000000 on timeline 3
本文出自 “勇敢向前,坚决向左” 博客,谢绝转载!
postgresql异步流复制搭建
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。