免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1426 | 回复: 0

Setting up Streaming Replication in PostgreSQL 9.0 [复制链接]

论坛徽章:
0
发表于 2011-12-22 08:53 |显示全部楼层


* install slave with empty 9.0 postgresql. Some hints are at http://packetcloud.net/2010/12/09/how-to-upgrade-from-postgresql-8-4-5-centos-5-base-to-postgresql-9-pgdg-rpm/ -- just stop at initdb (no need to run pg_upgrade ), erase 8.4 rpms on slave if installed

* stop master pgsql (service postgresql-9.0 stop)

* modify /var/lib/pgsql/9.0/data/pg_hba.conf on master to have (adjust with your IP info)
host replication postgres 10.24.16.0/24 trust

* mkdir /var/lib/pgsql/9.0/data/pg_wal ; chown postgres /var/lib/pgsql/9.0/data/pg_wal

* modify /var/lib/pgsql/9.0/data/postgresql.conf to have
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.0/data/pg_wal/%f'

* service postgresql-9.0 start on master

* Copy master data to standby with a point in time backup:
su - postgres
psql -c "SELECT pg_start_backup('label', true)"
rsync -a -v -e ssh /var/lib/pgsql/9.0/data/ slave:/var/lib/pgsql/9.0/data/ --exclude postmaster.pid
psql -c "SELECT pg_stop_backup()"

* copy the postgresql.conf settings above to slave so it can act as a primary after failover
* enable read only queries on slave in postgresql.conf
hot_standby = on

* create a recovery file in slave with streaming replication in /var/lib/pgsql/9.0/data/recovery.conf, adjust IP as needed
standby_mode = 'on'
primary_conninfo = 'host=10.24.16.11 port=5432 user=postgres'
trigger_file = '/tmp/pgsql.trigger'
restore_command = 'cp /var/lib/pgsql/9.0/data/pg_wal/%f "%p"'

* service postgresql-9.0 start on slave

* calculate replication lag:
SELECT pg_current_xlog_location() --- on master
SELECT pg_last_xlog_receive_location() --- on slave
SELECT pg_last_xlog_replay_location() --- on slave

* check replication using ps command
ps -ef | grep sender (on master) :
postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000

ps -ef | grep receiver ( on slave ):
postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/2000000

How to failover:
* touch /tmp/pgsql.trigger ... start querying to failover server

How to restart replication after failover :
* remake a fresh backup. master doesn't have to be stopped

How to restart replication after standby fails:
* restart postgres in standby after eliminating cause of failure

How to disconnect standby from primary:
* touch /tmp/pgsql.trigger in slave while primary running.

How to re-sync standby after isolation:
* shutdown standby, make a fresh backup as per above

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP