PostgreSQL的streaming replication-阿里云开发者社区

开发者社区> 数据库> 正文

PostgreSQL的streaming replication

简介:

主要参考的是如下url:

http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/

准备两台机器,

master: 10.10.10.2

slave:    10.10.10.1

 

首先在 master机器上,建立一个名为replicator的用户:

psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'thepassword';"

master机器上的 postgresql.conf,配置成这样:

listen_address = # make sure we're listening as appropriate
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8    
wal_keep_segments = 8 

 

然后在master的 pg_hba.conf文件,中,进行如下配置,添加一行,允许replicator用户从远端访问:

host  replication     replicator      10.10.10.1/32              md5

然后,启动master端的postgresql

 

然后,在slave端:

在slave端的postgresql停止的前提下,以postgres用户身份,删除data目录:

 rm -rf /usr/local/pgsql/data

 

然后,在slave端,执行pg_basebackup程序:

pg_basebackup -h 10.10.10.2 -D /usr/local/pgsql/data -U replicator -v -P

 

在执行完毕 pg_basebackup后,会得到一个从 master端拷贝到的/usr/local/pgsql/data目录,

编辑其中的 postgresql.conf,把其standby_mode设置为on。

 

在slave端,编辑一个/usr/local/pgsql/data/recovery.conf文件,

内容如下:

  standby_mode = 'on'
  primary_conninfo = 'host=10.10.10.2 port=5432 user=replicator password=thepassword sslmode=require'
  trigger_file = '/tmp/postgresql.trigger'

 

然后,在slave端,启动postgresql:

复制代码
[postgres@pg200 pgsql]$ ./bin/pg_ctl -D ./data start
pg_ctl: another server might be running; trying to start server anyway
server starting
[postgres@pg200 pgsql]$ LOG:  database system was interrupted while in recovery at log time 2013-09-27 17:28:27 CST
HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/5012F78
LOG:  redo starts at 0/5012EE0
LOG:  record with zero length at 0/5012F78
LOG:  database system is ready to accept read only connections
LOG:  streaming replication successfully connected to primary
复制代码

 

从log中,可以看到,postgresql的 streaming repliation开始工作了。

 

下面进行简单的验证:

master端,新增数据:

复制代码
[postgres@pg200 ~]$ cd /usr/local/pgsql/
[postgres@pg200 pgsql]$ ./bin/psql
psql (9.2.4)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)

postgres=# select * from test;
 id 
----
  1
  2
  3
(3 rows)

postgres=# insert into test values(4);
INSERT 0 1
postgres=# 
复制代码

 

slave端,可以看到数据:

复制代码
[postgres@pg200 ~]$ cd /usr/local/pgsql/bin
[postgres@pg200 bin]$ ./psql
psql (9.2.4)
Type "help" for help.

postgres=# select * from test;
 id 
----
  1
  2
  3
  4
(4 rows)

postgres=# 
复制代码

 

 关于pg_basebackup,其官方文档说明如下:

复制代码
http://www.postgresql.org/docs/current/static/app-pgbasebackup.html

pg_basebackup is used to take base backups of a running PostgreSQL database cluster. These are taken without affecting other clients to the database, and can be used both for point-in-time recovery (see Section 24.3) and as the starting point for a log shipping or streaming replication standby servers (see Section 25.2).

pg_basebackup makes a binary copy of the database cluster files, while making sure the system is automatically put in and out of backup mode automatically. Backups are always taken of the entire database cluster, it is not possible to back up individual databases or database objects. For individual database backups, a tool such as pg_dump must be used.

The backup is made over a regular PostgreSQL connection, and uses the replication protocol. The connection must be made with a superuser or a user having REPLICATION permissions (see Section 20.2), and pg_hba.conf must explicitly permit the replication connection. The server must also be configured with max_wal_senders set high enough to leave at least one session available for the backup.
复制代码

 

但是,实际上有一个问题是需要引起注意的,上述的streaming replication,并没有使用到archive_log模式,这个也不是必须的。

可是如果master很繁忙,比如像这样:

 

create table test01(id integer, val char(1024)); 
insert into test01 values(generate_series(1,1228800),repeat( chr(int4(random()*26)+65),1024));

 

此时,master端的online wal log,不断地快速产生,有的会随着新的wal log的生成而被删除掉。

此时,就会出现如下错误:

复制代码
[postgres@pg200 ~]$ cd /usr/local/pgsql
[postgres@pg200 pgsql]$ ./bin/pg_ctl -D ./data start
server starting
[postgres@pg200 pgsql]$ LOG:  database system was shut down in recovery at 2013-09-30 14:51:27 CST
LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/5013A48
LOG:  redo starts at 0/50139B0
LOG:  record with zero length at 0/5013A48
LOG:  database system is ready to accept read only connections
LOG:  streaming replication successfully connected to primary
FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 000000010000000000000011 has already been removed

LOG:  invalid magic number 0000 in log file 0, segment 17, offset 14467072
LOG:  streaming replication successfully connected to primary
FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 000000010000000000000011 has already been removed
复制代码

从这个意义上说,使用 archive log是必须的。







本文转自健哥的数据花园博客园博客,原文链接:http://www.cnblogs.com/gaojian/p/3347203.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章