PostgreSQL 複寫 | 六小編 Editor Leon

準備好兩台裝好 PostgreSQL 的主機，一台叫 primary，一台叫 replica，複寫的概念就是 primary 資料庫的任何異動都會即時的反應到 replica，就像影之分身一樣，當然，如果兩台主機位於異地的話就沒那麼即時了，簡單介紹完複寫的概念就來進入正題吧。

Primary 設定

建立複寫帳號

建立帳號，授予複寫權限：

CREATE ROLE repuser WITH REPLICATION PASSWORD '<PASSWORD>' LOGIN;

配置複寫設定

配置 primary 複寫設定，以我的機台為例，配置文件在 /etc/postgresql/15/main/postgresql.conf。

配置文件分成幾個區段：

到「CONNECTION AND AUTHENTICATION」區段，修改以下項目：

listen_addresses，從 'localhost' 改為 primary 自身的 IP，或者有多張網卡的話可以大氣一點改為 *。

到「WRITE-AHEAD-LOG」區段，修改以下項目：

wal_level，原本是註解掉的，註解拿掉改為 replica。

上面的 write-ahead log (WAL)，簡單說就是資料庫的異動紀錄，所謂的複寫就是把 primary 的 WAL 傳給 replica，就好像跑步游泳一樣，A 做什麼 B 就做什麼。

找到「REPLICATION」區段，修改以下項目：

max_wal_senders，原本是被註解掉的，把註解拿掉，改值為 2。
max_replication_slots，原本是註解掉的，註解拿掉改為 2。

改完後重啟 PostgreSQL：

> sudo systemctl restart postgresql

建立 replication slot

Primary 自己的 WAL 是會隨著時間刪除的，但在 replicatoin slot 的機制下，它會等到 replica 有收到紀錄後才排入刪除，如果 replica 斷線的話 primary 會幫它保留紀錄，等到 replica 上線的那天（好像有點浪漫）。

進入 PostgreSQL 介面，建立一個 replication slot：

SELECT * FROM pg_create_physical_replication_slot('replica_1_slot');

建好離開回到 Linux shell。

設定連線與認證

PostgreSQL 用戶認證的配置文件在 /etc/postgresql/15/main/pg_hba.conf，最下面原本長類似下面這樣：

# Allow replication connections from localhost, by a user with the
# replication privilege.

# TYPE  DATABASE     USER  ADDRESS       METHOD

local   replication  all                 peer
host    replication  all   127.0.0.1/32  scram-sha-256
host    replication  all   ::1/128       scram-sha-256

最下面三行：

第一行表示所有有 replication 權限的用戶都可以從本機走 Unix socket 登入，登入認證走 Linux 系統認證。
第二行表示所有有 replication 權限的 127.0.0.1（本機）用戶都可以走 TCP socket 登入，以密碼認證。
第三行同上，只差在 IP 為本機 IPv6。

在我們的複寫場景，replica 是另外一台主機，所以幫它開一條路，加一行：

# TYPE  DATABASE     USER  ADDRESS  METHOD

host    replication  all   samenet  scram-sha-256

這裡的 samenet 表示與 primary 機同一網段的客戶端，例如 primary IP 為 192.168.100.212/23 的話，它的網段就是 192.168.100.1 - 192.168.101.254，

改完後重啟 PostgreSQL：

> sudo systemctl restart postgresql

這樣 replica 應該就可以連進來了。

Replica 設定

停止 replica PostgreSQL 服務

> sudo systemctl stop postgresql

砍 replica 資料庫

砍之前先備份：

> cd /var/lib/postgresql/15
> sudo -u postgres tar --create --file=main.tar main

身為影武者是不能有自己的身份的，把 replica 原有的資料庫檔案砍掉：

> sudo -u postgres rm --recursive --force /var/lib/postgresql/15/main/*

備份 primary 資料庫

在 replica 執行下面指令，把當前的 primary 資料庫備份到 replica 身上：

> sudo -u postgres bash

(postgres_shell)> pg_basebackup \
--host=192.168.100.212 \
--username=repuser --password \
--pgdata=/var/lib/postgresql/15/main \
--progress --verbose

以 primary 資料庫重建 replica

把 primary 備份到 replica 之後，要以這份 primary 資料庫為基礎重建 replica，讓兩者有相同的基礎。

Touch 一個空檔案：

> sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

稍後 PostgreSQL 服務啟動時偵測到這個檔案就會啟動還原模式重建資料庫。

但此刻還不急著把服務叫起來，還有更多的配置等著我們…。

配置複寫設定

這邊來配置 replica 端的複寫設定，修改檔案 /etc/postgresql/15/main/postgresql.conf。

到「WRITE-AHEAD-LOG」區段，修改以下項目：

wal_level，原本是註解掉的，註解拿掉改為 replica。

找到「REPLICATION」區段，修改以下項目：

max_wal_senders，原本是被註解掉的，把註解拿掉，改值為 2。
max_replication_slots，原本是註解掉的，註解拿掉改為 2。
primary_conninfo，原本是註解掉的，取消註解，填入 'host=192.168.100.212 port=5432 user=repuser password=<PASSWORD> application_name=r1'。
primary_slot_name，原本是註解掉的，取消註解，填入 replica_1_slot。

其中：

前兩項的設定值與 primary 相同。
第三項的 application_name=r1 這部份的 r1 是讓 replica 與 primary 溝通的識別名稱，後面還會用到。

啟動 replica PostgreSQL 服務

終於可以啟動了：

> sudo systemctl start postgresql

確認複寫狀態

首先看剛啟動的 replica 的 log 確認情況：

> tail /var/log/postgresql/postgresql-15-main.log

應該會看到類似下面的訊息：

2023-03-14 10:00:12.276 CST [9401] LOG:  starting PostgreSQL 15.2 (Ubuntu 15.2-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
2023-03-14 10:00:12.277 CST [9401] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-14 10:00:12.277 CST [9401] LOG:  listening on IPv6 address "::", port 5432
2023-03-14 10:00:12.279 CST [9401] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-03-14 10:00:12.283 CST [9404] LOG:  database system was interrupted; last known up at 2023-03-14 09:06:15 CST
2023-03-14 10:00:12.349 CST [9404] LOG:  entering standby mode
2023-03-14 10:00:12.352 CST [9404] LOG:  redo starts at 0/C000028
2023-03-14 10:00:12.353 CST [9404] LOG:  consistent recovery state reached at 0/C000138
2023-03-14 10:00:12.353 CST [9401] LOG:  database system is ready to accept read-only connections
2023-03-14 10:00:12.378 CST [9405] LOG:  started streaming WAL from primary at 0/D000000 on timeline 1

可以看到，此時 replica 作為分身，除了複寫外，只允許讀取，不能再手動增改資料了。

然後進 replica 的 PostgreSQL 查詢複寫狀態：

\x # Turn on the expanded display mode

SELECT * FROM pg_stat_wal_receiver;

那 \x 只是為了美化輸出格式，美化後的輸出應該像下面這樣：

-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 9405
status                | streaming
receive_start_lsn     | 0/D000000
receive_start_tli     | 1
written_lsn           | 0/D000148
flushed_lsn           | 0/D000148
received_tli          | 1
last_msg_send_time    | 2023-03-14 10:22:14.280002+08
last_msg_receipt_time | 2023-03-14 10:22:14.30126+08
latest_end_lsn        | 0/D000148
latest_end_time       | 2023-03-14 10:00:12.385886+08
slot_name             | replica_1_slot
sender_host           | 192.168.100.212
sender_port           | 5432
conninfo              | user=repuser password=******** channel_binding=prefer dbname=replication host=192.168.100.212 port=5432 application_name=r1 fallback_application_name=15/main sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

其中 status 可以看到為同步串流中。

然後來看看 primary 這邊。

進 primary 的 PostgreSQL 查詢複寫狀態：

\x # Turn on the expanded display mode

SELECT * FROM pg_stat_replication;

結果如下：

-[ RECORD 1 ]----+------------------------------
pid              | 27576
usesysid         | 16388
usename          | repuser
application_name | r1
client_addr      | 192.168.100.213
client_hostname  | 
client_port      | 50768
backend_start    | 2023-03-14 10:00:12.366893+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/D000148
write_lsn        | 0/D000148
flush_lsn        | 0/D000148
replay_lsn       | 0/D000148
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-03-14 10:13:23.53618+08

其中：

state 可以看到為同步串流中。
幾個 -lag 都為空，表示 replica 沒有落後。

以上都沒有問題，複寫機制就建立成功了，所有對 primary 的操作都會同步反應到 replica，目前這樣的模式只能算是熱備份，還不能稱為高可用，因為 primary 單點故障不會有人即時代替。

Primary 設定

建立複寫帳號

配置複寫設定

建立 replication slot

設定連線與認證

Replica 設定

停止 replica PostgreSQL 服務

砍 replica 資料庫

備份 primary 資料庫

以 primary 資料庫重建 replica

配置複寫設定

啟動 replica PostgreSQL 服務

確認複寫狀態

Share