PostgreSQL的streaming replication

磨砺技术珠矶，践行数据之道，追求卓越价值
回到上一级页面： PostgreSQL集群方案相关索引页回到顶级页面：PostgreSQL索引页[作者高健@博客园 luckyjackgao@gmail.com]

主要参考的是如下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 -- :: 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 /5012F78

LOG:  redo starts at /5012EE0

LOG:  record with zero length at /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是必须的。

[作者高健@博客园 luckyjackgao@gmail.com]
回到上一级页面： PostgreSQL集群方案相关索引页回到顶级页面：PostgreSQL索引页磨砺技术珠矶，践行数据之道，追求卓越价值

巴特西

PostgreSQL的streaming replication

最新文章

热门文章