Postgresql主從複製–物理複製

• 2019 年 10 月 8 日
• 筆記

1 複製類型

PostgreSQL支援物理複製（流複製）及邏輯複製2種。通過流複製技術，可以從實例級複製出一個與主庫一模一樣的實例級的從庫。流複製同步方式有同步、非同步兩種。

另一種複製方式為邏輯複製，區別於物理複製的是物理複製是基於實例級的複製，只能複製整個PostgreSQL實例，而不能基於部分庫及表。從PostgreSQL10開始，出現了基於表級別的複製，即邏輯複製。

2  流複製

主庫安裝及從庫編譯此處就省略了，直接進入主從複製的安裝環節。

2.1 修改主庫配置文件postgresql.conf

/*  除了基礎參數,搭建備庫至少需要配置如下參數 */  listen_address = '*'  wal_level = replica  archive_mode = on  archive_command = 'cp %p /data/postgresql/archive/%f '  max_wal_senders= 10  wal_keep_segments=1024  hot_standby = on

參數簡要說明：

listen_address：  按需設置,本次測試配置為所有主機均可以訪問,生產環境可以按需配置網段或IP等  wal_level：  設置流複製模式至少設置為replica  archive_mode:  本次啟用歸檔  archive_command：WAL日誌歸檔命令,生產環境可以將歸檔拷貝到對應目錄或其他機器上,本次測試配置為歸檔到本機的另一個目錄下  max_wal_senders： 最大WAL發送進程數，此數量需大於等於從庫個數且比max_connections小。  wal_keep_segments： pg_wal目錄下保留WAL日誌的個數,每個WAL文件默認16M,為保障從庫能在應用歸檔落後時依舊能追上主庫，此值建議設置較大一點。  hot_standby： 此參數控制在恢復歸檔期間是否支援只讀操作，設置為ON後從庫為只讀模式。

注意： 上述參數中有涉及歸檔日誌的路徑，需手動創建

mkdir -p  /data/postgresql/archive/

2.2  創建複製帳號

為了數據安全及便於許可權控制，創建一個複製專用的資料庫帳號

postgres=# create user repl  REPLICATION  LOGIN ENCRYPTED  PASSWORD 'repl123';  CREATE ROLE

2.3  修改配置文件pg_hba.conf

添加複製帳號的許可權，因可能會主從切換，因此 主從機器的IP均添加。也可以設置網段，以便於後期添加從庫。

# replication privilege.  local   replication     all                                     trust  host    replication     all             127.0.0.1/32            trust  host    replication     all             ::1/128                 trust  host    replication     repl             192.168.56.33/24            md5  host    replication     repl             192.168.56.32/24            md5

2.4  備份數據

從機上在線備份主庫數據，並將數據放在指定路徑，此路徑建議與主庫路徑一致。可以使用pg_basebackup

命令在線熱備份，具體命令如下：

pg_basebackup -h 192.168.56.32 -U repl -p 5432 -F p   -X s  -v -P -R -D /data/postgresql/data/ -l postgres32

pg_basebackup命令中的參數說明：

-h 指定連接的資料庫的主機名或IP地址，這裡就是主庫的ip

-U 指定連接的用戶名，此處是我們剛才創建的專門負責流複製的repl用戶

-F 指定生成備份的數據格式，支援p（plain原樣輸出）或者t（tar格式輸出）

-X 表示備份開始後，啟動另一個流複製連接從主庫接收WAL日誌，有 f(fetch)和s (stream）兩種方式，建議使用s方式

-P 表示顯示數據文件、表空間傳輸的近似百分比 允許在備份過程中實時的列印備份的進度

-v 表示啟用verbose模式，命令執行過程中會列印各階段日誌，建議啟用

-R 表示會在備份結束後自動生成recovery.conf文件，這樣也就避免了手動創建

-D 指定把備份寫到哪個目錄，這裡尤其要注意一點就是做基礎備份之前從庫的數據目錄（/data/postgresql/data）目錄需要手動清空

-l 表示指定個備份的標識，運行命令後可以看到進度提示

以上備份命令輸出過程如下

[postgres@PG33 data]$ pg_basebackup -h 192.168.56.32 -U repl -p 5432 -F p   -X s  -v -P -R -D /data/postgresql/data/ -l postgres32  Password:  pg_basebackup: initiating base backup, waiting for checkpoint to complete  pg_basebackup: checkpoint completed  pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1  pg_basebackup: starting background WAL receiver  pg_basebackup: created temporary replication slot "pg_basebackup_17737"  56041/56041 kB (100%), 1/1 tablespace  pg_basebackup: write-ahead log end point: 0/20000F8  pg_basebackup: waiting for background process to finish streaming ...  pg_basebackup: base backup completed

從以上日誌資訊看出pg_basebackup命令首先對資料庫做一次checkpoint，之後基於時間點做一個全庫基準備份，全備過程中會拷貝$PGDATA數據文件和表空間文件到備庫節點對應目錄。

2.5 修改recovery.conf

以上備份命令中生成了recovery.conf 文件,因此簡單修改即可。

standby_mode = 'on'  primary_conninfo = 'user=repl password=repl123 host=192.168.56.32 port=5432 sslmode=disable sslcompression=0 target_session_attrs=any'  ## 添加如下資訊  recovery_target_timeline = 'latest'  

參數說明：

standby_mode： 設置是否啟用資料庫為備庫，如果設置成on，備庫會不停地從主庫上獲取WAL日誌流，直到獲取主庫上最新的WAL日誌流  primary_conninfo：設置主庫的連接資訊，這裡設置了主庫IP、埠、用戶名資訊等，此處是明文密碼，生產環境建議配置非明文密碼，而是將密碼配置在另一個隱藏文件中  covery_target_timeline： 設置恢復的時間線（timeline），默認情況下是恢復到基準備份生成時的時間線，設置成latest表示從備份中恢復到最近的時間線，通常流複製環境設置此參數為latest，複雜的恢復場景可將此參數設置成其他值

2.6 啟動從庫

直接使用pg_ctl或配置服務啟動從庫即可。

pg_ctl  -D /data/postgresql/data/  -l pg33.log  start

如果啟動過程中出現如下錯誤

waiting for server to start….2019-09-26 10:40:54.327 CST [10267] FATAL: data directory "/data/postgresql/data" has invalid permissions

2019-09-26 10:40:54.327 CST [10267] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

stopped waiting

pg_ctl: could not start serve

Examine the log output.

則需要先修改許可權，再啟動即可

[postgres@PG33 data]$ chmod 0750 /data/postgresql/data/  [postgres@PG33 data]$ pg_ctl  -D /data/postgresql/data/  -l pg33.log  start  waiting for server to start.... done  server started

2.7 測試主從同步

在主庫創建表並新增數據

[postgres@PG32 ~]$ psql  psql (11.4)  Type "help" for help.  postgres=# create table test2(id int primary key, name varchar(20));  CREATE TABLE  postgres=# insert into test2 values(1,'aaa'),(2,'abc');  INSERT 0 2

在從庫查看

[postgres@PG33 data]$ psql  psql (11.4)  Type "help" for help.    postgres=# select * from test2;   id | name  ----+------    1 | aaa    2 | abc  

數據已正常同步

2.8 查看複製狀態

通過pg_stat_replication視圖可以查看複製狀態

postgres=# select  pid ,usesysid,usename,client_addr,state,sync_state  from  pg_stat_replication;    pid  | usesysid | usename |  client_addr   |   state   | sync_state  -------+----------+---------+----------------+-----------+------------   25123 |    16797 | repl    | 192.168.56.33 | streaming | async  (1 row)

以上查詢結果sync_state欄位值為async，表示主備數據複製使用非同步方式；state值為streaming,表示流複製方式。

2.9 調整為同步複製

前面的步驟部署的為非同步複製,如想配置為同步複製，則調整recovery.conf配置文件里的 synchronous_commit及synchronous_standby_names 後重啟或reload即可。

synchronous_commit = remote_write  synchronous_standby_names = '*'

之後再查看結果如下：

postgres=# select  pid ,usesysid,usename,client_addr,state,sync_state  from  pg_stat_replication;    pid  | usesysid | usename |  client_addr   |   state   | sync_state  -------+----------+---------+----------------+-----------+------------   16265 |    16797 | repl    | 192.168.56.33 | streaming | sync  (1 row)  

此時狀態已變為同步複製了。

註： synchronous_commit 有多種方式，在流複製模式下,主要設置情況如下：

remote_write： 當流複製主庫提交事務時，需等待備庫接收主庫發送的WAL日誌流並寫入備節點作業系統快取中，之後向客戶端返回成功，這種情況下備庫實例出現異常關閉時不會有已傳送的WAL日誌丟失風險，但備庫作業系統異常宕機就有已傳送的WAL丟失風險了，此時WAL可能還沒完全寫入備節點WAL文件中，簡單地說remote_write表示本地WAL已落盤，備庫的WAL還在備庫作業系統快取中，也就是說只有一份持久化的WAL，這個選項帶來的事務響應時間較低

on： 設置成on表示流複製主庫提交事務時，需等待備庫接收主庫發送的WAL日誌流並寫入WAL文件，之後才向客戶端返回成功，簡單地說on表示本地WAL已落盤，備庫的WAL也已落盤，也就是說有兩份持久化的WAL，但備庫此時還沒有完成重做，這個選項帶來的事務響應時間較高

remote_apply： 表示表示流複製主庫提交事務時，需等待備庫接收主庫發送的WAL並寫入WAL文件，同時備庫已經完成重做，之後才向客戶端返回成功，簡單地說remote_apply表示本地WAL已落盤，備庫WAL已落盤並且已完成重做，這個設置保證了擁有兩份持久化的WAL，同時備庫也完成了重做，這個選項帶來的事務響應時間最高，即性能最差。