postgresql主從實現之非同步流複製

標籤：postgresql主從實現之非同步流複製

postgresql主從複製實現方式之一：
基於Standby的非同步流複製，這是PostgreSQL9.x版本（2010.9）之後提供的一個很nice的功能，類似的功能在Oracle中是11g之後才提供的active dataguard和SQL Server 2012版本之後才提供的記錄傳送，此處再次為pg鼓掌，確實是一個很棒的開來源資料庫。廢話不多說，本篇blog就詳細記錄一下在pg9.5中實現Hot Standby非同步流複製的完整配置過程和注意事項。

Standby資料庫原理：
首先我們做主從同步的目的就是實現db服務的高可用性，通常是一台主要資料庫提供讀寫，然後把資料同步到另一台從庫，然後從庫不斷apply從主庫接收到的資料，從庫不提供寫服務，只提供讀服務。在postgresql中提供讀寫全功能的伺服器稱為primary database或master database，在接收主庫同步資料的同時又能提供讀服務的從程式庫伺服器稱為hot standby server。

PostgreSQL在資料目錄下的pg_xlog子目錄中維護了一個WAL記錄檔，該檔案用於記錄資料庫檔案的每次改變，這種記錄檔機制提供了一種資料庫熱備份的方案，即：在把資料庫使用檔案系統的方式備份出來的同時也把相應的WAL日誌進行備份，即使備份出來的資料區塊不一致，也可以重放WAL日誌把備份的內容推到一致狀態。這也就是基於時間點的備份（Point-in-Time Recovery），簡稱PITR。而把WAL記錄傳送到另一台伺服器有兩種方式，分別是：

    WAL日誌歸檔（base-file）
    流複製（streaming replication）

第一種是寫完一個WAL日誌後，才把WAL記錄檔拷貝到standby資料庫中，簡言之就是通過cp命令實現遠程備份，這樣通常備庫會落後主庫一個WAL記錄檔。而第二種流複製是postgresql9.x之後才提供的新的傳遞WAL日誌的方法，它的好處是只要master庫一產生日誌，就會馬上傳遞到standby庫，同第一種相比有更低的同步延遲，所以我們肯定也會選擇流複製的方法。
在實際操作之前還有一點需要說明就是standby的搭建中最關鍵的一步——在standby中產生master的基礎備份。postgresql9.1之後提供了一個很方便的工具—— pg_basebackup，關於它的詳細介紹和參數說明可以在官網中查看（pg_basebackup tool）.下面在搭建過程中再做相關具體說明，關於一些基礎概念和原理先介紹到這裡。
pg_basebackup tool官網介紹：
https://www.postgresql.org/docs/current/static/p-pgbasebackup.html


詳細配置環境：
下面開始實戰，首先準備兩台伺服器，我這裡開了2個虛機做測試，分別是：
主庫（master）CentOS release 6.5 (Final) 10.0.0.100 postgresql 9.5.9
從庫（standby）CentOS release 6.7 (Final) 10.0.0.110 postgresql 9.5.9

從主庫配置開始。
首先要提前在master機器10.0.0.100安裝好postgresql,採用的是二進位安裝包，具體參考本博文的postgresql二進位安裝過程。

主庫配置:
注意此處的操作都是在主庫（10.0.0.100）上進行的，首先開啟資料目錄下的postgresql.conf檔案然後做以下修改：

1.listen_address = ‘*’（預設localhost）
2.port = 10280       （預設是5432）
3.wal_level = hot_standby（預設是minimal）
4.max_wal_senders=2（預設是0）
5.wal_keep_segments=64（預設是0）
下面對上述參數稍作說明
第一個是監聽任何主機，wal_level表示啟動搭建Hot Standby，max_wal_senders則需要設定為一個大於0的數，它表示主庫最多可以有多少個並發的standby資料庫，而最後一個wal_keep_segments也應當設定為一個盡量大的值，以防止主庫產生WAL日誌太快，日誌還沒有來得及傳送到standby就被覆蓋，但是需要考慮磁碟空間允許，一個WAL記錄檔的大小是16M：
[[email protected] data]$ cd /data/pgsql100/data/pg_xlog/
[[email protected] pg_xlog]$ ls
000000010000000000000001  000000010000000000000002  000000010000000000000003  000000010000000000000004  000000010000000000000005  archive_status
[[email protected] pg_xlog]$ du -sh *
16M    000000010000000000000001
16M    000000010000000000000002
16M    000000010000000000000003
16M    000000010000000000000004
16M    000000010000000000000005
4.0K    archive_status
如上，一個WAL記錄檔是16M，如果wal_keep_segments設定為64，也就是說將為standby庫保留64個WAL記錄檔，那麼就會佔用16*64=1GB的磁碟空間，所以需要綜合考慮，在磁碟空間允許的情況下設定大一些，就會減少standby重新搭建的風險。接下來還需要在主庫建立一個超級使用者來專門負責讓standby串連去拖WAL日誌：
CREATE ROLE replica login replication encrypted password ‘replica‘;
接下來開啟資料目錄下的pg_hba.conf檔案然後做以下修改：
[[email protected] pg_xlog]$ tail -2 /data/pgsql100/data/pg_hba.conf
#host    replication     postgres        ::1/128                 trust
host    replication     replica     10.0.0.110/32                md5

如上，這行配置的意思是允許使用者replica從10.0.0.110/32網路上發起到本資料庫的流複製串連，簡言之即允許從程式庫伺服器串連主庫去拖WAL日誌資料。主庫配置很簡單，到此就算結束了，啟動主庫並繼續配置從庫
pg_ctl -D /data/pgsql100/data -l /data/pgsql100/log/postgres.log stop
pg_ctl -D /data/pgsql100/data -l /data/pgsql100/log/postgres.log start

從庫配置:
首先要說明的是從庫上一開始也是需要安裝postgresql資料庫服務的，應為需要pg_basebackup命令工具才能在從庫上產生的master主庫的基礎備份。但是還要強調一點的是：從庫上初始化資料庫時指定的資料目錄/data/psql110/data需要清空，才可以在從庫上使用pg_basebackup命令工具來產生master主庫的基礎備份資料。

從此處開始配置從庫（10.0.0.110），首先要通過pg_basebackup命令列工具在從庫上產生基礎備份：
[[email protected] data]$ pg_basebackup -h 10.0.0.100 -U replica -p 10280 -F p -x -P -R -D /data/psql110/data/ -l replbackup
Password: 密碼（replica）
46256/46256 kB (100%), 1/1 tablespace
[[email protected] data]$
簡單做一下參數說明（可以通過pg_basebackup --help進行查看），
-h指定串連的資料庫的主機名稱或IP地址，這裡就是主庫的ip。
-U指定串連的使用者名稱，此處是我們剛才建立的專門負責流複製的repl使用者。
-F指定了輸出的格式，支援p（原樣輸出）或者t（tar格式輸出）。
-x表示備份開始後，啟動另一個流複製串連從主庫接收WAL日誌。
-P表示允許在備份過程中即時的列印備份的進度。
-R表示會在備份結束後自動產生recovery.conf檔案，這樣也就避免了手動建立。

-D指定把備份寫到哪個目錄，這裡尤其要注意一點就是做基礎備份之前從庫的資料目錄（/data/psql110/data/）目錄需要手動清空。
-l表示指定一個備份的標識。

[[email protected] data]$ cat /data/psql110/data/recovery.conf
standby_mode = ‘on‘
primary_conninfo = ‘user=replica password=replica host=10.0.0.100 port=10280 sslmode=prefer sslcompression=1 krbsrvname=postgres‘

運行命令後看到如下進度提示就說明產生基礎備份成功：
[[email protected] data]$ pg_basebackup -h 10.0.0.100 -U replica -p 10280 -F p -x -P -R -D /data/psql110/data/ -l replbackup
Password: 密碼（replica）
46256/46256 kB (100%), 1/1 tablespace
[[email protected] data]$
如上由於我們在pg_hba.conf中指定的md5認證方式，所以需要輸入密碼。最後還需要修改一下從庫資料目錄下的postgresql.conf檔案，將hot_standby改為啟用狀態，即hot_standby=on。到此為止就算配置結束了，我們現在可以啟動從庫，
[[email protected] data]$ egrep -v ‘^#|^$‘ /data/psql110/data/postgresql.conf|grep "hot_standby"
wal_level = hot_standby            # minimal, archive, hot_standby, or logical
hot_standby = on            # "on" allows queries during recovery

[[email protected] data]$ pg_ctl -D /data/psql110/data -l /data/psql110/log/postgres.log start
server starting

從庫上查看到流複製進程：

[[email protected] data]$ ss -lntup|grep postgres
tcp    LISTEN     0      128                   :::10280                :::*      users:(("postgres",23161,4))
tcp    LISTEN     0      128                    *:10280                 *:*      users:(("postgres",23161,3))
[[email protected] data]$ ps -ef|grep postgres
root       5663   4716  0 18:12 pts/0    00:00:00 su - postgres
postgres   5664   5663  0 18:12 pts/0    00:00:00 -bash
postgres   5855   5664  0 18:13 pts/0    00:00:00 /bin/bash /usr/local/pgsql/bin/psql
postgres   5857   5855  0 18:13 pts/0    00:00:00 /usr/local/pgsql/bin/psql.bin
root      12406   7244  0 18:34 pts/1    00:00:00 su - postgres
postgres  12407  12406  0 18:34 pts/1    00:00:00 -bash
root      13861  13810  0 18:47 pts/3    00:00:00 su - postgres
postgres  13862  13861  0 18:47 pts/3    00:00:00 -bash
root      21768  21736  0 19:54 pts/2    00:00:00 su - postgres
postgres  21769  21768  0 19:54 pts/2    00:00:00 -bash
postgres  23161      1  0 20:05 pts/2    00:00:00 /usr/local/pgsql/bin/postgres -D /data/psql110/data
postgres  23164  23161  0 20:05 ?        00:00:00 postgres: startup process   recovering 000000010000000000000007
postgres  23165  23161  0 20:05 ?        00:00:00 postgres: checkpointer process                     
postgres  23166  23161  0 20:05 ?        00:00:00 postgres: writer process                           
postgres  23167  23161  0 20:05 ?        00:00:00 postgres: stats collector process                  
postgres  23168  23161  0 20:05 ?        00:00:00 postgres: wal receiver process   streaming 0/7000140
postgres  23240  21769  0 20:06 pts/2    00:00:00 ps -ef
postgres  23241  21769  0 20:06 pts/2    00:00:00 grep postgres

主庫上查看到流複製進程：

[[email protected] pg_xlog]$ ps -ef|grep postgres
root       2904   2642  0 00:40 pts/0    00:00:00 su - postgres
postgres   2905   2904  0 00:40 pts/0    00:00:00 -bash
postgres   2939      1  0 00:42 pts/0    00:00:00 /usr/local/pgsql/bin/postgres -D /data/pgsql100/data
postgres   2941   2939  0 00:42 ?        00:00:00 postgres: checkpointer process                      
postgres   2942   2939  0 00:42 ?        00:00:00 postgres: writer process                            
postgres   2943   2939  0 00:42 ?        00:00:00 postgres: wal writer process                        
postgres   2944   2939  0 00:42 ?        00:00:00 postgres: autovacuum launcher process               
postgres   2945   2939  0 00:42 ?        00:00:00 postgres: stats collector process                   
root       3109   3064  0 00:58 pts/2    00:00:00 su - postgres
postgres   3110   3109  0 00:58 pts/2    00:00:00 -bash
postgres   3151   3110  0 00:59 pts/2    00:00:00 /bin/bash /usr/local/pgsql/bin/psql -p10280
postgres   3153   3151  0 00:59 pts/2    00:00:00 /usr/local/pgsql/bin/psql.bin -p10280
root       3189   3087  0 01:07 pts/3    00:00:00 su - postgres
postgres   3190   3189  0 01:07 pts/3    00:00:00 -bash
postgres   3272   2939  0 01:25 ?        00:00:00 postgres: postgres testdb01 [local] idle            
postgres   3415   2939  0 02:16 ?        00:00:00 postgres: wal sender process replica 10.0.0.110(34021) streaming 0/7000140
postgres   3422   3190  0 02:17 pts/3    00:00:00 ps -ef
postgres   3423   3190  0 02:17 pts/3    00:00:00 grep postgres

此時從庫上可以看到流複製的進程，同樣的主庫也能看到該進程。表明主從流複製配置成功。

同步測試示範：
建立庫和建表做測試，在master伺服器（10.0.0.100）中的建立testdb02庫並且建一張表並添加幾條資料：

master上操作：
postgres=# create database testdb02;
CREATE DATABASE
檢查：
[[email protected] pg_xlog]$ psql -p10280 -c ‘\list‘|grep testdb02
 testdb02  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

testdb01=# \c testdb02
You are now connected to database "testdb02" as user "postgres".
testdb02=# \d
No relations found.
建立表：
CREATE TABLE weather ( city varchar(80), temp_lo int, temp_hi int, prcp real,date date);
testdb02=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | weather | table | postgres
(1 row)

testdb02=# \d weather
           Table "public.weather"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 city    | character varying(80) |
 temp_lo | integer               |
 temp_hi | integer               |
 prcp    | real                  |
 date    | date                  |

testdb02=#
testdb02=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES (‘China05‘, ‘47‘, ‘59‘, ‘1.0‘, ‘1994-12-15‘);
INSERT 0 1
testdb02=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES (‘China04‘, ‘46‘, ‘58‘, ‘2.0‘, ‘1994-12-14‘);\
INSERT 0 1
testdb02=# select * from weather;
  city   | temp_lo | temp_hi | prcp |    date    
---------+---------+---------+------+------------
 China05 |      47 |      59 |    1 | 1994-12-15
 China04 |      46 |      58 |    2 | 1994-12-14
(2 rows)
testdb02=#

從庫上檢查：
[[email protected] data]$  psql -p10280 -c ‘\list‘|grep testdb02
 testdb02  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres=# \c testdb02;
You are now connected to database "testdb02" as user "postgres".
testdb02=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | weather | table | postgres
(1 row)

testdb02=# \d weather;
           Table "public.weather"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 city    | character varying(80) |
 temp_lo | integer               |
 temp_hi | integer               |
 prcp    | real                  |
 date    | date                  |

testdb02=# select * from weather;
  city   | temp_lo | temp_hi | prcp |    date    
---------+---------+---------+------+------------
 China05 |      47 |      59 |    1 | 1994-12-15
 China04 |      46 |      58 |    2 | 1994-12-14
(2 rows)

testdb02=#
可以看到完美同步，那麼從庫是否能刪除呢？測試一下：
從庫上測試刪除資料庫testdb02;
postgres=# drop database testdb02;
ERROR:  cannot execute DROP DATABASE in a read-only transaction
postgres=# drop database testdb01;
ERROR:  cannot execute DROP DATABASE in a read-only transaction

standby的資料無法刪除，正如我們之前說的，standby只提供唯讀服務，而只有master才能進行讀寫操作，所以master才有許可權刪除資料。master刪除的同時standby中的資料也將同步刪除，

查看複製狀態

主庫中執行

650) this.width=650;" src="https://s4.51cto.com/oss/201710/29/7cdecd8be51e6a025b4afd68ea3b68ed.png-wh_500x0-wm_3-wmp_4-s_675295564.png" title="2222.png" alt="7cdecd8be51e6a025b4afd68ea3b68ed.png-wh_" />

關於非同步流複製的內容到這裡.

參考博文：

http://blog.csdn.net/wzyzzu/article/details/53331206

本文出自 “10931853” 部落格，請務必保留此出處http://wujianwei.blog.51cto.com/10931853/1977210

postgresql主從實現之非同步流複製