PostgreSql基于Standby的異步流主從復(fù)制

一、概述

PostgreSQl從9.0版本之后推出一個(gè)類似于Oracle的active dataguard和MySql中繼日志一樣的日志傳送。我們借助這個(gè)功能就可實(shí)現(xiàn)PostgreSql的主從復(fù)制。

基本原理就是，通常一臺主數(shù)據(jù)庫提供讀寫，然后把數(shù)據(jù)同步到另一臺從庫。從庫不斷apply從主庫接收到的數(shù)據(jù)，從庫不提供寫服務(wù)，只對外提供讀服務(wù)。在postgresql中提供讀寫全功能的服務(wù)器稱為primary database或master database，在接收主庫同步數(shù)據(jù)的同時(shí)又能提供讀服務(wù)的從庫服務(wù)器稱為hot standby server。

PostgreSQL在數(shù)據(jù)目錄下的pg_xlog子目錄中維護(hù)了一個(gè)WAL日志文件，該文件用于記錄數(shù)據(jù)庫文件的每次改變，這種日志文件機(jī)制提供了一種數(shù)據(jù)庫熱備份的方案，即：在把數(shù)據(jù)庫使用文件系統(tǒng)的方式備份出來的同時(shí)也把相應(yīng)的WAL日志進(jìn)行備份，即使備份出來的數(shù)據(jù)塊不一致，也可以重放WAL日志把備份的內(nèi)容推到一致狀態(tài)。這也就是基于時(shí)間點(diǎn)的備份（Point-in-Time Recovery），簡稱PITR。而把WAL日志傳送到另一臺服務(wù)器有兩種方式，分別是：

1. WAL日志歸檔（base-file）

2. 流復(fù)制（streaming replication）

第一種是寫完一個(gè)WAL日志后，才把WAL日志文件拷貝到standby數(shù)據(jù)庫中，簡言之就是通過cp命令實(shí)現(xiàn)遠(yuǎn)程備份，這樣通常備庫會落后主庫一個(gè)WAL日志文件。而第二種流復(fù)制是postgresql9.x之后才提供的新的傳遞WAL日志的方法，它的好處是只要master庫一產(chǎn)生日志，就會馬上傳遞到standby庫，同第一種相比有更低的同步延遲，所以我們肯定也會選擇流復(fù)制的方式。

在實(shí)際操作之前還有一點(diǎn)需要說明就是standby的搭建中最關(guān)鍵的一步——在standby中生成master的基礎(chǔ)備份。postgresql9.1之后提供了一個(gè)很方便的工具—— pg_basebackup，關(guān)于它的詳細(xì)介紹和參數(shù)說明可以在官網(wǎng)中查看（pg_basebackup tool），下面在搭建過程中再做相關(guān)具體說明。

二、基礎(chǔ)環(huán)境介紹

系統(tǒng)平臺：CentOS release 6.6 (Final)

Postgresql：postgresql-9.6.6

SELINUX=disabled

Iptables關(guān)閉

主庫(master)IP:192.168.221.161

從庫(standby)IP:192.168.221.160

基礎(chǔ)環(huán)境搭建可以參考前一篇文章（Centos6.6下Postgresql9.6.6安裝與配置），也就是PostgreSql的基本安裝與配置。

三、主庫配置

1. 在主庫增加同步的用戶名與密碼

[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，最后一行添加

[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這臺主機(jī)上以md5 加密的形式發(fā)起到本數(shù)據(jù)庫的流復(fù)制連接

3. 在主配置文件下配置下面幾個(gè)參數(shù)

listen_address = ‘*’（默認(rèn)localhost）
wal_level = hot_standby（默認(rèn)是minimal）
max_wal_senders=5（默認(rèn)是0）
wal_keep_segments=64（默認(rèn)是0）
synchronous_standby_names = 'standby01'

第一個(gè)參數(shù)表示監(jiān)聽所有IP；第二個(gè)參數(shù)表示啟動hot standby；第三個(gè)參數(shù)表示主庫可以有多少個(gè)并發(fā)的standby數(shù)據(jù)庫，這里設(shè)置為5；第四個(gè)參數(shù)表示一個(gè)WAL日志文件大小，默認(rèn)為16M

第五個(gè)參數(shù)指定同步復(fù)制的Standby名稱（從庫的recovery.conf中有要定義的地方，不過這一個(gè)參數(shù)可以不設(shè)置）

4. 重啟主庫，讓配置生效。

如果啟動有報(bào)錯(cuò)，可以去日志排查。

四、從庫配置

首先要保證主庫、從庫之間的同步之前的環(huán)境的是一致的，這樣才方便做同步。我因?yàn)橹熬驮趶膸鞕C(jī)器上配置過PG數(shù)據(jù)庫，所以一開始走了不少彎路。最后把PG的家目錄清空，重新再來一次才算成功。

1. 在從庫上通過pg_basebackup命令行工具生成基礎(chǔ)備份，命令如下，看到100%說明備份成功

[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

參數(shù)說明：-h 指定連接的數(shù)據(jù)庫IP；

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

-P 在備份過程中實(shí)時(shí)打印備份進(jìn)度

-D 指定備份的目錄

-U 指定連接的用戶名

-p 指定要連接的端口

--password 指定要連接的用戶密碼

其他參數(shù)介紹：

-R 會在備份后自動生成recovery.conf文件，我也是事后才知道這個(gè)參數(shù)

-l 指定一個(gè)備份的標(biāo)識

具體的參數(shù)介紹可以使用pg_basebackup --help查看，也可以查看官網(wǎng)介紹https://www.postgresql.org/docs/current/static/app-pgbasebackup.html

特別備注：我這里沒有加-R參數(shù)，所以要手動拷貝一下recovery.conf

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

添加以下信息：

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

hot_standby = on

將hot_standby改為啟用狀態(tài)

3. 接下來可以啟動從庫了

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

第一次啟動報(bào)錯(cuò)，這是因?yàn)樯厦嫔蓚浞莸拿钗沂褂?/span>root用戶執(zhí)行的，導(dǎo)致PG家目錄的屬性變成了root，所以要重新設(shè)置權(quán)限

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

再次啟動正常，查看進(jìn)程也OK

[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

五、結(jié)果驗(yàn)證

1. 在主庫通過select usename,application_name,client_addr,state from pg_stat_replication查詢一下：

[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在通過流復(fù)制的方式同步主庫的數(shù)據(jù)

2. 創(chuàng)建表驗(yàn)證一下

主庫上建表，并插入數(shù)據(jù)驗(yàn)證

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)

在從庫上查看：

[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)

嘗試插入數(shù)據(jù)看一下：

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

可以看到，從庫可以查看從主庫同步過來的數(shù)據(jù)，但并不能寫數(shù)據(jù)。

六、總結(jié)

以上是搭建Postgresql主從同步的全過程，一路踩了好多坑，記錄下了，希望能幫助到別人