渡鴉之丘: pgBouncer 簡介

PostgreSQL 對於一個連線的 Session，就是一個 postgres 程序，會開一個 Unix Socket 來進行。所以若是頻繁的斷線/連線，對 PostgreSQL 來說，開銷比較大。

這也使的 PostgreSQL 一直到 9.5 版為止，對於一個處在 Idle 狀態的 Session，沒有自動逾時斷線的機制，除非手動踢掉它。直到 9.6 版，才多設計一個預設不啟用 Transaction Timeout 的參數，idle_in_transaction_session_timeout，才比較「接近」逾時中止交易的機制。這些都算是傾向保留住 Session 的設計。

（註 1：嚴格來說，idle_in_transaction_session_timeout 這個參數不是逾時斷線，而是交易卡住強制中斷）

（註 2：idle_in_transaction_session_timeout 參數可以針對 Database 或 User 進行個別設置。）

因此對於短暫交易的活動型態，在 PostgreSQL 中，便會採用 Connection Pooling 程式，咬住一些 PostgreSQL 的 Session，然後負責應付外部連線，由它進行分配。

目前 Postgres 的 Connection Pooling 中介程式的專案，以 pgBouncer 與 pgPool-2 為主流。前一陣子的維護活動比較歇緩，不過兩者都正常維護中。

上述兩者都能作為中介的連線管理程式，那兩者有哪些不同？

相較於 pgPool-2，pgBouncer 是輕量級的 Connection Pooling Middleware，比較適合和資料庫裝在一起（或是與 Application Server 裝一起也行）；而 pgPool-II 則功能比較多樣，不只是 Connection Pooling 而已，例如負載平衡（Load Balancing）、資料「複寫」（SQL Replication）等多種功能。因此一般建議 pgPool-2 與資料庫分開配置（例如，與 Application Server 裝一起，或獨立一台電腦）。不過兩者的資源消耗都不會太大，一般不會消耗顯著的系統資源。

此外，兩者間的小差異還有，pgBouncer 的設定檔採用 Windows 風格的 ini 檔案，而 pgPool-2 則採用 PostgreSQL 風格的設定檔內容。

此處紀錄 pgBouncer 簡單使用紀錄，以符合一般「簡易配置」使用。

進行設置前，先簡介 pgBouncer 功能：

pgBouncer 的連線控制，分成三種層級

Session pooling：對於連線請求，分給一個 Session 到它結束
Transaction pooling：一次交易結束，才把連線交還 Pooling
Statement pooling：一個 Query 結束，就把連線交還 Pooling

其中 Session Pooling 為預設模式。

在 pgBouncer 收到連線請求，會查看其內的 Pool 是否有空出來的提供連接。沒有的話，在資料庫連線數還沒爆滿（達到 max_connections）的狀況下，就會建立一個連線到 Pool 並提供連線，否則就會安排等待有連線被釋回 Pool 中，再行連線。

pgBouncer 的組成，分為執行程式本身（/usr/bin/pgbouncer）以及設定檔 pgbouncer.ini 與 userlist.txt 兩個；預設 Port 為 6432（註：pgPool-2 則預設為 9999）供外部連線。只要把設定檔設置完畢，啟動服務即可。

以下為設置的筆記部份

試驗的環境

CentOS 7.2 x64（反正就是 Linux ~）
PostgreSQL 9.6，社群 YUM 發行版；裡面準備兩個邏輯資料庫 mydb1 和 mydb2
pgBouncer 1.7.2，社群 YUM 發行版

對於其他發行版（例如，EnterpriseDB 發行版、OpenSCG 發行版，或是 2ndQuadrant BDR 特別版。抑或更多的發行版、或是「某某-as-a-Service」類型產品等～族繁不及備載），設置方式是一樣的，只要稍微找一下相對應的程式檔位置即可。

設置 pgBouncer 之前，先描述一下 Postgres 的連線設定

postgresql.conf

listen_addresses = 'localhost,127.0.0.1,10.140.0.2'

pg_hba.conf

local   all       all                          peer
host    all       all       127.0.0.1/32       trust
host    all       all       10.140.0.2/32      trust

為了示範 pgBouncer 功能，上面特別設置 PostgreSQL 只有綁定本機 IP，而接受請求的服務範圍，也只限制本機的範圍

接著開始設置 pgBouncer。

首先是安裝 pgBouncer

[root@pgvm ~]# yum install pgbouncer
[root@pgvm ~]# chkconfig pgbouncer on

接著，設置設定檔 pgbouncer.ini：這個設定檔中，主要在設定連線相關設置、連線管理等，設定檔中都有詳述。在此編輯基本設定，省去中間註解

[root@pgvm ~]# cat /usr/lib/systemd/system/pgbouncer.service | grep "pgbouncer.ini"
Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer.ini
[root@pgvm ~]# vi /etc/pgbouncer/pgbouncer.ini
; 註解用分號開頭
[database]
; 連接本機上面的資料庫 my_db1
; 給外面連接的資料庫名稱可以和 my_db1 不同
; 在這我命名為 mydb
mydb = port=5432 dbname=my_db1


; 另外一個示範透過走網路連接資料庫
; 上面限制了請求的網路範圍
yourdb = host=127.0.0.1 port=5432 dbname=my_db2


[pgbouncer]
listen_addr = *
listen_port = 6432
; 用帳號密碼管理
auth_type = md5
; 要用 hba 方式管理，上面一列要換成下面一列
; auth_type = hba
auth_file = /etc/pgbouncer/userlist.txt
; auth_hba_file = /etc/pgbouncer/pgbouncer_hba.conf
pool_mode = session

pgbouncer.ini 可以分成兩段落，第一段是資料庫的段落 [database]，第二段是 pgbouncer 段落 [pgbouncer]。

[database] 設置要連接的資料庫連線資訊，在上面的範例可見，透過 pgbouncer，我們多了一層限制連線的方式；而 [pgbouncer] 是所有連線功能設置的部份，包含認證設定、Connection Pooling 行為等。

接著設置登入管理的方式：有帳號密碼對應與 pg_hba.conf 形式的管理兩部份。兩種可以依照需求設置，之間沒有相互依賴的要求。

1. 設置帳號密碼對應 userlist.txt：

執行 mkauth.py 這個 python script，對資料庫裡面現有的帳號密碼都進行編碼，存到 userlist.txt 裡面。不過這個模組需要 psycopg2 這個 Python 連線 PostgreSQL 的模組；此外 userlist.txt 的擁有者得要是 pgbouncer 這個作業系統帳號

[root@pgvm ~]# rpm -ql pgbouncer | grep mkauth
/etc/pgbouncer/mkauth.py
/etc/pgbouncer/mkauth.pyc
/etc/pgbouncer/mkauth.pyo
[root@pgvm ~]# yum install python-psycopg2
[root@pgvm ~]# sudo /etc/pgbouncer/mkauth.py /etc/pgbouncer/userlist.txt "host=localhost port=5432 dbname=postgres user=postgres"
[root@pgvm ~]# chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
[root@pgvm ~]# cat /etc/pgbouncer/userlist.txt
"admin" "md545f2603610af569b6155c45067268c6b" ""
"user1" "md5f37d4d45a1eef9033a8a5c80d4d201be" ""

另外一個設定方式是手動設定。先進去 Postgres，用 md5 把密碼字串作編碼，以下以帳號 admin，密碼 1234 為例

postgres=# SELECT 'md5'||md5('1234'||'admin');
              ?column?               
-------------------------------------
 md545f2603610af569b6155c45067268c6b

把上面內容貼到 /etc/pgbouncer/userlist.txt 裡面。延續上面例子，檔案內容填寫如下（記得檢查 userlist.txt 的擁有者！）

"admin" "md545f2603610af569b6155c45067268c6b"
"user1" "md5f37d4d45a1eef9033a8a5c80d4d201be"

2. 如果偏好 Host-Based Authentication 的話，可以啟用 auth_hba_file，然後編輯裡面內容，然後在 pgbouncer.ini 進行相關設置。這個檔案的擁有者記得也要是 pgbouncer 才行。

# 允許10.140.0.xxx 子網段連進來
host    all       all       10.140.0.0/24      md5

以下用帳號密碼管理的部份進行示範。

啟動 pgBouncer 程序

[root@pgvm ~]# service pgbouncer start

試一下

psql -p 6432 -U user1 -d mypgdb -h 127.0.0.1
Password for user user1: 
psql (9.6.1)
Type "help" for help.
mypgdb=> \q

連線成功。

在這裡遇到無法登入成功的人，記得檢查一下 /etc/pgbouncer/userlist.txt 的檔案擁有者～

pgBouncer 有提供管理界面。如果要管理的話，可以連到 pgbouncer 這個「Database」裡面。以下簡單列舉一下 Connection Pooling 狀態：

[postgres@pgvm ~]$ psql -p 6432 -U postgres -d pgbouncer -h 127.0.0.1
psql (9.6.1, server 1.7.2/bouncer)
Type "help" for help.
pgbouncer=# show pools;
 database  |   user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | pool_mode 
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+-----------
 pgbouncer | pgbouncer |         1 |          0 |         0 |       0 |       0 |         0 |        0 |       0 | statement
(1 row)

有趣的是，其實資料庫裡面，並沒有存在 pgbouncer 這個資料庫：其實 pgbouncer 這個資料庫連線，只是一個只能透過 pgbouncer 連線進去的管理界面，裡面有很多可供調整的設定（參考 pgBouncer Admin Console）。

以上便是 pgBouncer 的使用簡介。使用 pgBouncer，便可以免於開立過大資料庫連線資源，使效能不能有恰當的表現；而透過 Connection Pooling 協助管理連線，便可以不用使資料庫有耗費資源在連線上。

參考資料：