PostgreSQL是一款優秀的開源關係型數據庫，從1989年至今已有22年歷史，經過多次轉折，如今乃是業界流行度僅次於MySQL的開源數據庫系統。比起MySQL，PostgreSQL有更好的索引支持和更穩固的ACID保證，也提供了諸多非常優秀的複製(Replication)解決方案，同時以更自由的開源許可協議發佈。在PostgreSQL 9.0之前，PostgreSQL就有了許多的第三方的複製方案，如下表：
| 特性 | 共享磁盤容錯 | 文件系統複製 | 基於預寫日誌的可讀複製 | 基於觸發的複製 | 基於語句的複製 | 異步多主節點複製 | 同步多節點主複製 |
|---|---|---|---|---|---|---|---|
| 常見的解決方案 | NAS | DRBD | PITR | Slony | pgpool-II | Bucardo | |
| 通信方法 | shared disk | disk blocks | WAL | table rows | SQL | table rows | table rows and row locks |
| 無需特製硬件 | • | • | • | • | • | • | |
| 允許多主服務器 | • | • | • | ||||
| 不增加服務端負荷 | • | • | • | ||||
| 多服務器無等待 | • | • | • | • | |||
| 主服務器失敗時不丟失數據 | • | • | • | • | |||
| 備用服務器支持讀請求 | Hot only | • | • | • | • | ||
| 表級別粒度 | • | • | • | ||||
| 無衝突解析 | • | • | • | • | • |
從PostgreSQL 9.0開始，基於預寫日誌(Write Ahead Log, WAL)的可讀複製(PITR)更是成爲了官方提供的異步主從複製(Master-Slave Replication)解決方案，該方案擁有如下優點：
- 使用預寫日誌記錄數據庫的改動，不額外增加服務端的其他負荷。
- 當主服務器失敗（如斷電、系統崩潰、災難）時，不會丟失任何數據。
- 支持基於流和基於檔案的兩種日誌傳輸方案。
- 備用服務器可作爲負載均衡節點提供讀請求。
- 支持多個或多級備用服務器。
實現原理
主服務器在接受到每個事務請求時，將數據改動用預寫日誌(WAL)記錄。具體而言，事務採用兩段提交(Two Phase Commit)，即先將改動寫入預寫日誌，然後再實際改動數據庫。這樣可以保證預寫日誌的時間戳永遠不落後於數據庫，即便是正在寫入時服務器突然崩潰，重啟以後也可以依據預寫日誌將數據恢復，因爲預寫日誌保留了比數據庫記錄中更新的版本。PostgreSQL的異步複製解決方案正是利用了預寫日誌，將預寫日誌從主服務器(Master Sever)傳輸到備用服務器(Standby Server)，然後在備用服務器上回放(Replay)出預寫日誌中記錄改動，從而實現主從複製。PostgreSQL使用了兩種方式傳輸預寫日誌：存檔式(archive)和流式(streaming)。
存檔式複製的原理是主服務器將預寫日誌主動拷貝到一個安全的位置（可以直接到備用服務器，也可以是第三臺服務器），同時備用服務器定期掃描這個位置，並將預寫日誌拷貝到備用服務器端然後再回放。這樣即使主服務器崩潰了，備用服務器也可以從這個安全的位置獲取到一份完整的記錄，以確保任何數據不會丟失。而流式複製則簡化了這一個步驟，由主服務器直接通過TCP協議向備用服務器傳輸日誌，避免了兩次複製的開銷，有利於減小備用服務器和主服務器直接的數據延時。但當主服務器崩潰時，未被傳輸到備用服務器的日誌則會丟失，造成數據損失。PostgreSQL支持存檔式和流式兩種模式的混合，當兩種模式都開啟時，備用服務器會定期檢查是否有存檔已經到達指定的位置，並回放日誌。一旦檢測到指定的位置沒有新的日誌，則會切換到流式模式試圖直接從網絡傳輸日誌，接着再檢查存檔，不斷重複這一循環。
基本配置步驟
1、在主服務器和從服務器上分別安裝PostgreSQL 9.1以上版本，初始化數據庫：
initdb -D "/var/postgres/data" # Unix
initdb -D "D:/postgres/data" # Windows
2、修改主服務器數據目錄下的配置文件pg_hba.conf，增加備用服務器訪問的權限，例如：
host replication postgres_repl 59.66.134.0/24 md5
以上表示允許來自59.66.134.0/24的連接，用戶名爲postgres_repl，口令採用MD5驗證。 如果不需要配置流式複製，此項配置可以忽略。
3、修改主服務器數據目錄下的配置文件postgresql.conf： 將wal_level設爲archive或hot_standby，archive爲單純備用服務器所需的日誌格式，hot_standby爲支持讀請求的備用服務器所需格式； 如果要支持流式複製，修改max_wal_senders爲一個大於零的數值，表示流式複製最大的備用服務器連接數目； 如果要支持流式複製，修改wal_sender_delay爲一個適合的時間值，表示發送流式日誌的週期。 如果要支持檔案式複製，設置
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' # Unix
archive_command = 'copy "%p" "X:\\server\\archivedir\\%f"' # Windows
其中cp或copy是shell可執行命令，用於複製日誌，具體應用中根據環境可以是scp，或其他拷貝方式。/mnt/server/archivedir/或X:\server\archivedir\表示存儲日誌的安全的位置，可供備用服務器讀取。
4、修改備份服務器數據目錄下的配置文件postgresql.conf： 如果設置hot_standby爲on，則允許備用服務器支持讀請求，對應主服務器上wal_level要設置爲hot_standby；否則只作爲單純的備份服務器。
5、在備份服務器數據目錄下添加文件recovery.conf： 設置standby_mode爲on； 如果要支持流式複製，設置primary_conninfo，例如：
'host=59.66.134.21 port=5432 user=postgres_repl password=my_pass_word'；
如果要支持檔案式複製，設置
restore_command = 'cp /path/to/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
其中/path/to/archive/爲存儲從主服務器複製的日誌的位置，cp或copy的意義同上，可替換。
6、分別啟動主服務器和備份服務器：
pg_ctl start -D "/var/postgres/data" # Unix
pg_ctl start -D "D:/postgres/data" # Windows
在主服務器上改動數據，測試配置是否成功。
參考資料
http://www.postgresql.org/docs/9.1/interactive/continuous-archiving.html
上次修改時間 2017-03-16