pgpool-ll stream replication 架設教學

發表於 更新於 分類於 Disqus： 文章字數： 8.9k 所需閱讀時間 ≈ 16 分鐘

延續上次 Postgres Stream Replication 的筆記，這次來講講 pgpool-ll，這個東西可以想成是 postgres 的 proxy，並且可以做到 load balance 的效果。

在升級 Postgres 的版本之前，如果你的 Postgres 前面有掛 pgpool-ll，那麼升級後，就必須去更改 pgpool-ll 的設定，因此這邊決定會先來幾篇文章講講 pgpool-ll 的相關設定。

我們知道 Postgres 如果採用 Stream Replication 的話，是單個 primary 跟 多台 replica 的架構，而 primary 可以接受 read/write，但是 replica 只能接受 read。如果沒有 pgpool-ll 這種去做 read/write 的分配的話，我們就目前在自己的 service 的手動指定 read 要去哪一個 replica 的 pg，write 則是要去哪一個 primary 的 pg。

這樣其實很不方便，所以就需要 pgpool-ll 這種 proxy 作統一個管理。

pgpool-ll 介紹

先來講一下 pgpool 有第一代的版本，不過提供的功能非常少，現在也不會有人想用 pgpool-l 原因在於第一代沒有支援 load balance 及 自動故障轉移還有 priamry /replica 的模式支持。

第一代是還有提供 logical replication 的功能，不過基本上如果要專業的 logical replication 方案，我們會採用 pglogical 或者是 Postgres 原生的 logical replication。

而 pgpool-ll 跟 pgbouncer 兩個都是 Postgres 常見的 Connection Pooling 的解決方案。但兩者提供的功能還是有些區別，關於 pgbouncer 之後再開文章來介紹。

pgpool-ll 本身除了 connection pooling 的管理，其實還提供很多功能，例如 pgpool-ll 可以幫你做到以下事情：

  1. High-availability：也就是 failover 的功能，例如如果背後的 Postgres Cluster 的 Primary 故障了，pgpool-ll 會透過 health check 去做檢查，一但有問題會 promote replica Postgres to primary。也就是我們叫做自動故障轉移。

    另外為了避免 pgpool-ll 遇到單點故障的問題，pgpool-ll 可以支援架設 cluster 的方式，架設多個 pgpool-ll nodes，採用 quorum algorithm 來解決 split brain 等 problem 產生。

  2. Automated load balancing for primary and replicas：在 stream replication mode 下的 pgpool-ll 可以自動將 write/read 的 query 做 load balance 的效果，也是我們最期望想要的功能。

  3. Online Recovery：假設如果你的 primary 故障了，使用自動故障轉移機制，你可以將故障的點透過 Online Recovery 的功能將故障的節點的資料 recovery 回來，並且當作新的 replica PG

  4. In Memory Query Cache：例如會 cache 對應的 read query，而不會經過 Postgres，那當然會提高 read 的 performance。

安裝 pgpool-ll

目前 pgpool-ll 的最新版為 4.4.1。

  1. 在 mac 安裝的話可以透過 brew 很方便：
1
brew install pgpool-ii

  1. 或者是可以直接透過官方網站下載 source code 來進行 build：https://www.pgpool.net/mediawiki/index.php/Downloads

    1
    2
    3
    tar xf pgpool-II-4.4.1.tar.gz
    # 執行官方提供的腳本，並且可以給予對應的參數，可參考：https://www.pgpool.net/docs/44/en/html/install-pgpool.html
    ./configure

  2. 在 debian 上安裝：

    1
    apt-get install pgpool2

Demo Streaming Replication For pgpool-ll

這邊 Demo 完整的如何架設出 Postgres Cluster With Streaming Replication 並且透過 pgpool 來進行 load balance。

這邊用 Postgres 12 來做示範：

  1. 設定 priamry db

    • Initdb

      1
      initdb -D /tmp/primary_db;

    • 設定 postgresql.conf (這邊簡單設定)

      1
      2
      3
      listen_addresses = '*'
      port = 5432
      wal_level = replica

    • 啟動 postgres

      1
      pg_ctl -D /tmp/primary_db start

    • 登入 psql 建立 replication user

      1
      2
      3
      psql -p 5432 postgres
      # 原始的 plain password 是 repuserrepuser
      CREATE USER repuser REPLICATION LOGIN PASSWORD 'md58ab1a75fe519fbd497653a855134aef7';

      1. 因為給 md5 password 需要在 prefix 上加入 md5，原因來自於：

        For an MD5 encrypted password, rolpassword column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user’s password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe.

        https://www.postgresql.org/docs/current/catalog-pg-authid.html

        另外還有 Postgres 對於 md5 的格式是 md5 (password+user)，所以不要搞錯了，如果你是將 password 自行 md5 後再丟進去，之後你會發現登入不進去，因為格式要求與 Postgres 的不同。

        另外如果你沒有加 md5 的 prefix，Postgres 只會當作照存原始的 password，也就是你丟進去的：8ab1a75fe519fbd497653a855134aef7，登入的時候也是要同樣輸入這串 8ab1a75fe519fbd497653a855134aef7 才可以登入成功。

        當然你 password 這邊設定 md5 在 pg_hba.conf 也是要改成用 md5 來登入才可以。

      2. 產生 md5 的方式有一下幾種方式 ref：

        Linux:

        1
        2
        echo -n "md5"; echo -n "repuserrepuser" | md5sum | awk '{print $1}'
        md58ab1a75fe519fbd497653a855134aef7

        NOTE: The -n is critical to avoid including the newline character in your hash!

        MacOS:

        1
        2
        echo -n "md5"; md5 -qs "repuserrepuser"                                                                                                                                                                                   
        md58ab1a75fe519fbd497653a855134aef7

        Python 2:

        1
        2
        3
        >>> import hashlib
        >>> print("md5" + hashlib.md5("repuserrepuser").hexdigest())
        md58ab1a75fe519fbd497653a855134aef7

        Python 3:

        as above, but use binary strings

        1
        2
        print("md5" + hashlib.md5(b"repuserrepuser").hexdigest())
        md58ab1a75fe519fbd497653a855134aef7

    • 設定 pg_hba.conf

      1
      2
      3
      4
      5
      6
      # 如果想測試 psql 登入是不是可以用 md5 登入進去，可以將 local Type 的 Method 改成 md5
      # local   all             all                                     md5
      # 這個設定除了安全的考量之外，另外可以拿來測試 pgpool 對 postgres 進行連線的時候也是需要 md5 的驗證
      host    all             all             127.0.0.1/32            md5
      # 這個是為了讓 replica 可以與 primary 進行連線
      host    replication     repuser         127.0.0.1/32            md5

    • 重啟 postgres

      1
      pg_ctl -D /tmp/primary_db restart

  2. 設定 replica db

    • 透過 pg_basebackup 來設定

      1
      2
      pg_basebackup -h localhost -U repuser --checkpoint=fast \
      -D /tmp/replica_db/ -R  --slot=some_name -C -W --port=5432

    • 設定 postgresql.conf

      1
      2
      listen_addresses = '*'
      port = 5433

    • 啟動 postgres

      1
      pg_ctl -D /tmp/replica_db start

      接著如果看到這些 log 就代表有正常開啟 stream replication

      1
      2
      3
      4
      5
      2023-01-08 16:39:16.008 CST [34555] LOG:  entering standby mode
      2023-01-08 16:39:16.012 CST [34555] LOG:  redo starts at 0/5000028
      2023-01-08 16:39:16.013 CST [34555] LOG:  consistent recovery state reached at 0/5000100
      2023-01-08 16:39:16.013 CST [34554] LOG:  database system is ready to accept read only connections
      2023-01-08 16:39:16.024 CST [34559] LOG:  started streaming WAL from primary at 0/6000000 on timeline 1

  3. 設定 pgpool

    pgpool 預設的設定檔等位置會在 /usr/local/etc 裡面

    • 設定 pgpool.conf

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      # 將官方提供 sample 檔案複製一份
      cp pgpool.conf.sample pgpool.conf

      backend_clustering_mode = 'streaming_replication'
      listen_addresses = '*'
      port = 9999

      pcp_listen_addresses = 'localhost'                              
      pcp_port = 9898

      backend_hostname0 = '127.0.0.1'
      backend_port0 = 5432
      backend_weight0 = 0
      backend_data_directory0 = '/tmp/primary_db/'
      backend_hostname1 = '127.0.0.1'
      backend_port1 = 5433
      backend_weight1 = 1
      backend_data_directory1 = '/tmp/replica_db/'

      enable_pool_hba = on
      pool_passwd = 'pool_passwd'

      log_statement = on
      log_per_node_statement = on

      pid_file_name = 'pgpool.pid'

      sr_check_user = 'repuser'
      health_check_period = 10
      health_check_user = 'repuser'

      • 在 streaming_replication 模式下，backend_hostname0 會視為是 primary postgres，而之後填的 backend_hostname1 會視為是 replica postgres

      • pcp 是 pgpool 本身另外一個接口，可以使用 pgpool 提供 pcp 相關 tool 來操作 pgpool，例如：pcp_stop_pgpool 用來停止 pgpool。相關 pcp 工具可以參考：https://tatsuo-ishii.github.io/pgpool-II/current/pcp-commands.html

        那麼 pcp 這邊也需要身份驗證，相關的 username 與 password 對應需要在 pcp.conf 設定：

        1
        2
        # USERID:MD5PASSWD
        postgres:e8a48653851e28c69d0506508fb27fc5

        裡面會有預設的 username => postgres 跟 password => postgres，密碼必須透過 md5 來進行 hash。

        也可以透過 .pcppass file 來設定 username 與 password 的對應關係

      • backend_weight 是比重越大，則 load balance 的佔比更大，如果將 primary postgres 的 weight 設為 0，那會變成所有的 write 只會到 primary，所有的 read 只會到 replica，你的 primary 將不接受 read 請求。

        這邊主要是根據需求再去做調整就好，一般情況如果希望 priamry 跟 replica 都能接受 read 請求那就都設為 weight = 1 就可以了。

      • pgpool 本身也有做類似於 postgres hba 的身份驗證機制：

        enable_pool_hba = on 是代表啟用 hba 身份驗證機制，pool_passwd 填的是檔案的位置，裡面的檔案內容代表的是 hba 內記錄的 username 與 password 的對應關係。

        但要記住的是 pgpool 的 hba 是 client 與 pgpool 的驗證，不關後面的 postgres 的事情，也就是說 client 端到真正後面的 postgres 會經過兩次的驗證，一個 pgpool 的 hba 一個是 postgres 的 hba。因此這兩邊的 hba 會必須設定一樣。

        這邊一樣 pgpool hba 有提供 postgres md5 的方式來驗證，我們可以透過 pgpool 提供的工具：pg_md5 來產生 username 與 password 的對應關係，再將其 md5 後存入 pool_passwd 檔案裡面。

        1
        pg_md5 --config-file=/usr/local/etc/pgpool.conf --md5auth --username=repuser repuser

        以我們剛才的情境，我們的 health check user 跟 sr_check_user 是 repuser 所以要加上這個設定。如果你沒加上這個設定當你啟動 pgpool 會出現這種錯誤：

        1
        2
        2023-01-14 15:30:07.043: main pid 9064: LOG:  find_primary_node: make_persistent_db_connection_noerror failed on node 0
        2023-01-14 15:30:07.046: main pid 9064: LOG:  find_primary_node: make_persistent_db_connection_noerror failed on node 1

        而 Postgres 那邊會噴出的錯誤是：

        1
        2
        3
            Connection matched pg_hba.conf line 90: "host    all             all             127.0.0.1/32            md5"
        2023-01-14 15:33:21.714 CST [9580] FATAL:  password authentication failed for user "repuser"
        2023-01-14 15:33:21.714 CST [9580] DETAIL:  Password does not match for user "repuser".

        原因就在於 pgpool 嘗試問 Postgres 存不存在，但是是用 repuser 的身份去問的，但你又沒有設定 pool_passwd 的相關設定，那自然問訪問 Postgres 密碼失敗。被擋在外面。

        另外情況是你 sr_check 過了，但你 health_check_user 是用另外一個 user 身份的話，當你啟動 pgpool，會出現 health check 的錯誤：

        1
        2
        3
        2023-01-14 15:38:05.848: health_check pid 10105: LOG:  process started
        2023-01-14 15:38:05.850: health_check0 pid 10104: LOG:  health check failed on node 0 but failover is disallowed for the node
        2023-01-14 15:38:05.850: health_check1 pid 10105: LOG:  health check failed on node 1 but failover is disallowed for the node

        所以總得來說，sr_check_user 跟 health_check_user 一定都要在 pool_passwd 與 postgres 兩邊的密碼設定一樣且 pool_hba.conf 跟 postgresql.conf 設定也要一致，例如都要是 md5 method 來進行訪問。

        所以在我們的例子下，pool_hba.conf 設定如下：

        1
        2
        # IPv4 local connections:
        host    all         all         127.0.0.1/32          md5

      • log_statement 跟 log_per_node_statement 是這邊為了 demo 方便看，所以將 log 的機制打開，在 production 環境要再斟酌

      • 設定 pid_file_name 如果不設定的話，原本的路徑會需要自己先 mkdir /var/run/pgpool，這邊為了方便設定就將路徑改為與設定檔同路徑就可以了

      • sr_check_user 設定 stream replication 的 user 名稱，pgpool 會透過這個 user 去檢查目前 stream replication 的相關進度所以也可以設定 sr_check_period 的檢查頻率

      • health_check_period 跟 health_check_user 用來定期檢查底下 Postgres 的健康狀況如何

      這些設定檔都搞定後，就可以啟動 pgpool：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      # 如果直接跑 pgpool 是會直接運行在 background 但是不會 print logs 因為 detaches the terminal
      pgpool
      # 如果要顯示 log 且運行在 background 要這樣
      pgpool -n &
      # 如果要將 log 導向 file, -d 代表開啟 debug message log
      pgpool -n -d > /tmp/pgpool.log 2>&1 &
      # 要停止的話，這樣是屬於 graceful shutdown pgpool 會等待 client 斷開連接，然後自行終止
      pgpool stop
      # 想要強制關閉
      pgpool -m fast stop

      ok，接著要來體驗一下 read/write 分離的 load balance 的效果之前：

      在思考一件事情，我們的 application 一定會需要連線到 pgpool 再到 postgres，那本身 application 的 postgres user 也是要另外設定在 pgpool 跟 postgres 上的。

      所以我們在 primary server 上 create user：

      1
      2
      # plain password 就是 kennykenny
      CREATE USER repuser REPLICATION LOGIN PASSWORD 'md565d66c00e5b4c41450f47d900d549297';

      再來就是要在 pool_passwd 設定：

      1
      pg_md5 --config-file=/usr/local/etc/pgpool.conf --md5auth --username=kenny kenny

      接著我們就可以透過 psql 進入 pgpool 裡面：

      1
      2
      3
      4
      5
      6
      7
      8
      psql -p 9999 postgres
      CREATE TABLE users (id int primary key);
      # pgpool 對應的 log 可以發現他是將上面 create table 送往 node id 0 也就是我們的 priamry pg
      2023-01-14 16:07:11.917: psql pid 13069: LOG:  DB node id: 0 backend pid: 13185 statement: CREATE TABLE users (id int primary key);
      # 接著 read
      SELECT * FROM users;
      # pgpool 對應的 log 可以發現他是將上面的 select 送到 node id 1 也就是我們的 replica pg
      2023-01-14 16:08:09.951: psql pid 13069: LOG:  DB node id: 1 backend pid: 13186 statement: SELECT * FROM users;

      根據簡單的測試結果就可以知道 read/write 有幫我們做 load balance 的效果。那基本上如果你有多台 replica pg，本身也會幫你做 read 的 load balance 的效果，他會根據你所設定的每個 PG 的 weight 按照比例去分配。

總結

好的，算是做個有點詳細的筆記，關於 Postgres 與 pgpool 的基礎設定，以及展示了 load balance 的效果，關於 pgpool 的文檔讀起來都還算輕鬆。所以值得一看～

還有一篇 pgpool 常見的錯誤的原因及解決方案可以參考，挺棒的。