PostgreSql基于Standby的异步流主从复制

一、概述

PostgreSQl从9.0版本之后推出一个类似于Oracle的active dataguard和MySql中继日志一样的日志传送。我们借助这个功能就可实现PostgreSql的主从复制。

基本原理就是，通常一台主数据库提供读写，然后把数据同步到另一台从库。从库不断apply从主库接收到的数据，从库不提供写服务，只对外提供读服务。在postgresql中提供读写全功能的服务器称为primary database或master database，在接收主库同步数据的同时又能提供读服务的从库服务器称为hot standby server。

PostgreSQL在数据目录下的pg_xlog子目录中维护了一个WAL日志文件，该文件用于记录数据库文件的每次改变，这种日志文件机制提供了一种数据库热备份的方案，即：在把数据库使用文件系统的方式备份出来的同时也把相应的WAL日志进行备份，即使备份出来的数据块不一致，也可以重放WAL日志把备份的内容推到一致状态。这也就是基于时间点的备份（Point-in-Time Recovery），简称PITR。而把WAL日志传送到另一台服务器有两种方式，分别是：

1. WAL日志归档（base-file）

2. 流复制（streaming replication）

第一种是写完一个WAL日志后，才把WAL日志文件拷贝到standby数据库中，简言之就是通过cp命令实现远程备份，这样通常备库会落后主库一个WAL日志文件。而第二种流复制是postgresql9.x之后才提供的新的传递WAL日志的方法，它的好处是只要master库一产生日志，就会马上传递到standby库，同第一种相比有更低的同步延迟，所以我们肯定也会选择流复制的方式。

在实际操作之前还有一点需要说明就是standby的搭建中最关键的一步——在standby中生成master的基础备份。postgresql9.1之后提供了一个很方便的工具—— pg_basebackup，关于它的详细介绍和参数说明可以在官网中查看（pg_basebackup tool），下面在搭建过程中再做相关具体说明。

二、基础环境介绍

系统平台：CentOS release 6.6 (Final)

Postgresql：postgresql-9.6.6

SELINUX=disabled

Iptables关闭

主库(master)IP:192.168.221.161

从库(standby)IP:192.168.221.160

 

基础环境搭建可以参考前一篇文章（Centos6.6下Postgresql9.6.6安装与配置），也就是PostgreSql的基本安装与配置。

三、主库配置

1. 在主库增加同步的用户名与密码

1 2 3 4 5 6 7

[postgres@MidApp ~]$ psql  psql (9.6.6) Type  "help"  for  help.    postgres= # CREATE ROLE repluser REPLICATION LOGIN PASSWORD '123456'; CREATE ROLE postgres= #

2. 修改/home/postgres/pgsql/data/pg_hba.conf，最后一行添加

1 2 3 4 5 6 7

[root@MidApp tmp] # tail -6 /home/postgres/pgsql/data/pg_hba.conf  # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres trust #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust Host replication repluser  192.168.221.160 /32  md5

这行配置意思是允许用户repluser从192.168.221.160这台主机上以md5 加密的形式发起到本数据库的流复制连接

3. 在主配置文件下配置下面几个参数

1 2 3 4 5

listen_address = ‘*’（默认localhost） wal_level = hot_standby（默认是minimal） max_wal_senders=5（默认是0） wal_keep_segments=64（默认是0） synchronous_standby_names =  'standby01'

第一个参数表示监听所有IP；第二个参数表示启动hot standby；第三个参数表示主库可以有多少个并发的standby数据库，这里设置为5；第四个参数表示一个WAL日志文件大小，默认为16M

 

第五个参数指定同步复制的Standby名称（从库的recovery.conf中有要定义的地方，不过这一个参数可以不设置）

4. 重启主库，让配置生效。

如果启动有报错，可以去日志排查。

四、从库配置

首先要保证主库、从库之间的同步之前的环境的是一致的，这样才方便做同步。我因为之前就在从库机器上配置过PG数据库，所以一开始走了不少弯路。最后把PG的家目录清空，重新再来一次才算成功。

1. 在从库上通过pg_basebackup命令行工具生成基础备份，命令如下，看到100%说明备份成功

1 2 3 4

[root@DB tmp] # pg_basebackup -h 192.168.221.161 -F p -P -D /home/postgres/pgsql/data -p5432 -U repluser --password Password:  22802 /22802  kB (100%), 1 /1  tablespace NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to comple

参数说明：-h 指定连接的数据库IP；

-F 指定输出的格式，支持p(plain原样输出)或者t(tar格式输出)

-P 在备份过程中实时打印备份进度

-D 指定备份的目录

-U 指定连接的用户名

-p 指定要连接的端口

--password 指定要连接的用户密码

其他参数介绍：

-R 会在备份后自动生成recovery.conf文件，我也是事后才知道这个参数

-l 指定一个备份的标识

具体的参数介绍可以使用pg_basebackup --help查看，也可以查看官网介绍https://www.postgresql.org/docs/current/static/app-pgbasebackup.html

特别备注：我这里没有加-R参数，所以要手动拷贝一下recovery.conf

1	cp  /home/postgres/pgsql/share/recovery .conf.sample  /home/postgres/pgsql/data/recovery .conf

添加以下信息：

1 2	standby_mode = on primary_conninfo =  'application_name=standby01 user=repluser password=123456 host=192.168.221.161 port=5432 sslmode=disable sslcompression=1'

2. 修改从库的主配置文件/home/postgres/pgsql/data/postgresql.conf

1	hot_standby = on

将hot_standby改为启用状态

3. 接下来可以启动从库了

1 2 3

[root@DB tmp] # /etc/init.d/postgresql start Starting PostgreSQL: - bash :  /home/postgres/pgsql/data/serverlog : Permission denied ok

第一次启动报错，这是因为上面生成备份的命令我使用root用户执行的，导致PG家目录的属性变成了root，所以要重新设置权限

1	chown  -R postgres:postgres  /home/postgres/pgsql/ *

再次启动正常，查看进程也OK

1 2 3 4 5 6 7 8 9 10 11

[root@DB tmp] # /etc/init.d/postgresql start Starting PostgreSQL: ok [root@DB tmp] # ps -ef | grep postg root      52577  82731  0 15:12 pts /0     00:00:00  su  - postgres postgres  52578  52577  0 15:12 pts /0     00:00:00 - bash postgres  74295      1  0 20:01 ?        00:00:00  /home/postgres/pgsql/bin/postmaster  -D  /home/postgres/pgsql/data postgres  74296  74295  0 20:01 ?        00:00:00 postgres: startup process   recovering 000000010000000000000003  postgres  74297  74295  5 20:01 ?        00:00:00 postgres: wal receiver process   streaming 0 /3000140             postgres  74298  74295  0 20:01 ?        00:00:00 postgres: checkpointer process                                   postgres  74299  74295  0 20:01 ?        00:00:00 postgres: writer process                                         postgres  74300  74295  0 20:01 ?        00:00:00 postgres: stats collector process

                         

五、结果验证

1. 在主库通过select usename,application_name,client_addr,state from pg_stat_replication查询一下：

1 2 3 4 5 6 7 8 9 10 11

[postgres@MidApp ~]$ psql  psql (9.6.6) Type  "help"  for  help.    postgres= # select usename,application_name,client_addr,state from pg_stat_replication;   usename  | application_name |   client_addr   |   state    ----------+------------------+-----------------+-----------   repluser | standby01        | 192.168.221.160 | streaming (1 row)    postgres= #

可以看到192.168.221.160上的repluser在通过流复制的方式同步主库的数据

2. 创建表验证一下

主库上建表，并插入数据验证

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

postgres= # create table test01(id int primary key,note text); CREATE TABLE postgres= # \d           List of relations   Schema |  Name  | Type  |  Owner    --------+--------+-------+----------   public | test01 | table | postgres (1 row)    postgres= # insert into test01 values(1,'1111111'); INSERT 0 1 postgres= # select * from test01;   id  |  note    ----+---------    1 | 1111111 (1 row)

在从库上查看：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[postgres@DB data]$ psql  psql (9.6.6) Type  "help"  for  help.    postgres= # \d           List of relations   Schema |  Name  | Type  |  Owner    --------+--------+-------+----------   public | test01 | table | postgres (1 row)    postgres= # select * from test01;   id  |  note    ----+---------    1 | 1111111 (1 row)

尝试插入数据看一下：

1 2	postgres= # insert into test01 values(2,'2222222'); ERROR:  cannot execute INSERT  in  a  read -only transaction

可以看到，从库可以查看从主库同步过来的数据，但并不能写数据。

六、总结

以上是搭建Postgresql主从同步的全过程，一路踩了好多坑，记录下了，希望能帮助到别人

本文转自 青苗飞扬 51CTO博客，原文链接:http://blog.51cto.com/qingmiao/2047284