postgresql的hot standby(replication stream)
PG在9.*版本后热备提供了新的一个功能,那就是Stream Replication的读写分离,是PG高可用性的一个典型应用,也就是我们传统意义上说的Hot-Standby,比如Oracle的DG,mssql的mirror以及Mysql的读写分离等,与其他数据库相比较,有相同点,也有不同点,这些后述。下面是PG的流复制的安装步骤以及测试。 环境: [postgres@localhost ~]$ echo $PGHOME /home/postgres [postgres@localhost ~]$ echo $PGDATA 略,slave端可以只装数据库,不初始化数据库 Step2:创建流复制用户 master端执行: CREATE USER repuserreplication LOGIN CONNECTION LIMIT3 ENCRYPTED PASSWORD 'repuser'; Step3:配置Master端的访问文件pg_hba.conf 增加一行:host replication repuser 192.168.2.129/16 md5 Step4:配置MASTER端配置文件 max_wal_senders = 1注:max_wal_senders是Slave库的节点数,有多少个slave库就设多少, wal_level是write ahead log参数值,设置流复制务必将此值更新成hot_standby wal_keep_segments默认值是16,是PG_XLOG下的日志文件数相关参数 archive也可以选择关闭,归档是定时恢复用的,流复制不是必须的 Step5:主库备份(Master端) 5.1:开启文件备份,前提是wal_level参数值必须是archive或者hot_standby,前面已做 select pg_start_backup('Replition work'); 5.2:拷贝$PGDATA文件,并复制到Slave服务器上,排除pg_xlog内容,因为在slave还是要被清掉的,另外注意TAR打包时的权限问题,本次打包时遇到一个问题: tar报错 tar (child): Cannot open: Permission denied
select pg_stop_backup(),current_timestamp;
Step6:修改Slave端配置信息 6.1:postgresql.conf文件 $ cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf
$ vi recovery.conf --新增以下三行
standby_mode = 'on'
trigger_file = '/database/pgdata/postgresql.trigger.1949'
primary_conninfo = 'host=192.168.2.130 port=1949 user=repuser password=repuser keepalives_idle=60'
6.3:配置.pgpass文件(slave端) 6.4:删除slave端(从master端拷过来的)的pid文件和pg_xlog Step7:启动Slave库 [postgres@localhost ~]$ ps -ef|grep postgres 7.2 查看slave进程: [postgres@localhost ~]$ ps -ef|grep postgres 查看日志内容:
[postgres@localhost ~]$ more pgsql.log
LOG: database system was shut down in recovery at 2012-04-23 18:33:25 PDT
LOG: entering standby mode
LOG: streaming replication successfully connected to primary
LOG: redo starts at 0/8000020
LOG: consistent recovery state reached at 0/C000000
LOG: database system is ready to accept read only connections
Step8:测试
On Master:
test=# select * from kenyon;
id | name
----+--------
2 | kenyon
(1 row)
test=# insert into kenyon values (2,'kenyon testing data');
INSERT 0 1
test=#
On Slave:
test=# select * from kenyon;
id | name
----+---------------------
2 | kenyon
2 | kenyon testing data
(2 rows)
test=# delete from kenyon where id = 2;
ERROR: cannot execute DELETE in a read-only transaction
新增的数据已经传输过去,并且slave端的会话是只读的。
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- 可可 – 核心数据vs SQLite 3
- Xcode 4 final – 如何删除文档中的书签?
- ruby-on-rails – SimpleCov:不是每次都运行,只
- c# – servicestack.text将数组反序列化为对象
- ajax UpdatePanel用法
- XML 实现android控件边框设置
- Oracle database 11.2.0.4、12.1.0.2 和 12.2.0.
- postgresql – 随着搜索字符串变长,Trigram搜索速
- Exception in thread "main" net.sf.jso
- ruby-on-rails – Rails:按params排序查询?