Ubuntu 22.04 PostgreSQL pgBouncer 連線池

pgBouncer 概念與優勢

pgBouncer 是一個輕量級的 PostgreSQL 連線池管理工具。在高併發的應用場景中，頻繁建立與關閉資料庫連線會消耗大量系統資源。pgBouncer 透過重複使用現有連線，大幅降低連線建立的開銷，提升整體系統效能。

為什麼需要連線池？

PostgreSQL 對每個客戶端連線都會建立一個獨立的後端程序（backend process），這個程序會佔用約 5-10 MB 的記憶體。當連線數量增加時：

記憶體消耗：1000 個連線可能消耗 5-10 GB 記憶體
程序開銷：每個連線都需要系統程序管理資源
連線延遲：建立新連線需要 TCP 握手、驗證等步驟

pgBouncer 的優勢

優勢	說明
輕量級	單一程序處理數千連線，記憶體佔用極低
高效能	使用 libevent 實現非同步 I/O
透明代理	應用程式無需修改即可使用
連線復用	減少連線建立開銷
連線限制	保護資料庫免受連線過載

安裝與基本設定

安裝 PostgreSQL 與 pgBouncer

首先更新系統套件並安裝必要軟體：

1
2
3
4
5
6
7
8
# 更新套件列表
sudo apt update

# 安裝 PostgreSQL 15
sudo apt install -y postgresql-15 postgresql-client-15

# 安裝 pgBouncer
sudo apt install -y pgbouncer

驗證安裝

1
2
3
4
5
# 檢查 PostgreSQL 狀態
sudo systemctl status postgresql

# 檢查 pgBouncer 版本
pgbouncer --version

基本設定

pgBouncer 的主要設定檔位於 /etc/pgbouncer/pgbouncer.ini。以下是基本設定範例：

 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
;; /etc/pgbouncer/pgbouncer.ini

[databases]
; 定義資料庫對應
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
; 監聽設定
listen_addr = 0.0.0.0
listen_port = 6432

; 認證設定
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; 連線池設定
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

; 日誌設定
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

; 管理介面
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

建立使用者認證檔

1
2
# 建立 userlist.txt
sudo nano /etc/pgbouncer/userlist.txt

檔案格式如下：

1
2
3
"username" "password"
"myuser" "md5e10adc3949ba59abbe56e057f20f883e"
"pgbouncer_admin" "admin_password"

產生 MD5 密碼雜湊：

1
2
# 使用 PostgreSQL 產生 MD5 密碼
echo -n "passwordusername" | md5sum | awk '{print "md5" $1}'

啟動 pgBouncer

1
2
3
4
5
6
7
8
# 啟動服務
sudo systemctl start pgbouncer

# 設定開機自動啟動
sudo systemctl enable pgbouncer

# 檢查狀態
sudo systemctl status pgbouncer

連線池模式

pgBouncer 提供三種連線池模式，適用於不同的應用場景：

Session 模式

1
pool_mode = session

特性：

客戶端連線期間，保持對後端連線的獨佔使用
連線在客戶端斷開後才會歸還池中
最接近原生 PostgreSQL 行為

適用場景：

需要使用 prepared statements
使用 LISTEN/NOTIFY
需要 session-level 設定

示意圖：

1
2
3
4
5
客戶端 A ──────────────────────► 後端連線 1
         (整個 session 獨佔)

客戶端 B ──────────────────────► 後端連線 2
         (整個 session 獨佔)

Transaction 模式

1
pool_mode = transaction

特性：

每個交易使用一個後端連線
交易完成後連線歸還池中
最佳的連線復用效率

適用場景：

大多數 Web 應用
微服務架構
需要高併發的場景

示意圖：

1
2
3
4
5
客戶端 A ─┬─ TX1 ──► 後端連線 1
          └─ TX2 ──► 後端連線 2 (可能不同)

客戶端 B ─┬─ TX1 ──► 後端連線 1 (復用)
          └─ TX2 ──► 後端連線 3

注意事項：

不支援跨交易的 prepared statements
不支援 LISTEN/NOTIFY
Session-level 設定不會保留

Statement 模式

1
pool_mode = statement

特性：

每個 SQL 語句使用一個後端連線
語句執行完成後立即歸還
最高的連線復用率

適用場景：

僅執行單一語句的簡單查詢
不需要交易的應用

限制：

不支援多語句交易
不支援 prepared statements
僅適用於 autocommit 模式

模式比較

特性	Session	Transaction	Statement
連線復用率	低	中高	最高
Prepared Statements	支援	有限支援	不支援
多語句交易	支援	支援	不支援
LISTEN/NOTIFY	支援	不支援	不支援
Session 變數	保留	不保留	不保留

認證設定

pgBouncer 支援多種認證方式，確保連線安全：

auth_type 選項

1
2
3
[pgbouncer]
; 認證類型
auth_type = md5

可用的認證類型：

類型	說明
md5	MD5 密碼雜湊驗證
scram-sha-256	SCRAM-SHA-256 驗證（推薦）
plain	明文密碼（不建議用於生產環境）
trust	信任所有連線（僅用於測試）
hba	使用 pg_hba.conf 風格的認證

SCRAM-SHA-256 認證設定

PostgreSQL 14+ 預設使用 SCRAM-SHA-256：

1
2
3
[pgbouncer]
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

從 PostgreSQL 取得 SCRAM 密碼：

1
2
-- 在 PostgreSQL 中執行
SELECT usename, passwd FROM pg_shadow WHERE usename = 'myuser';

userlist.txt 格式：

1
"myuser" "SCRAM-SHA-256$4096:salt$StoredKey:ServerKey"

auth_query 動態認證

使用資料庫查詢進行認證，避免手動維護 userlist.txt：

1
2
3
4
[pgbouncer]
auth_type = md5
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
auth_user = pgbouncer_auth

建立認證用戶：

1
2
3
-- 在 PostgreSQL 中執行
CREATE USER pgbouncer_auth WITH PASSWORD 'secure_password';
GRANT SELECT ON pg_shadow TO pgbouncer_auth;

HBA 風格認證

建立 /etc/pgbouncer/pgbouncer_hba.conf：

1
2
3
4
# TYPE  DATABASE    USER        ADDRESS         METHOD
host    all         all         127.0.0.1/32    trust
host    all         all         192.168.1.0/24  md5
host    all         all         0.0.0.0/0       reject

設定檔：

1
2
3
[pgbouncer]
auth_type = hba
auth_hba_file = /etc/pgbouncer/pgbouncer_hba.conf

TLS/SSL 加密

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[pgbouncer]
; 客戶端 TLS
client_tls_sslmode = require
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_ca_file = /etc/pgbouncer/ca.crt

; 伺服器端 TLS
server_tls_sslmode = verify-full
server_tls_ca_file = /etc/pgbouncer/ca.crt

產生自簽憑證：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 產生私鑰
openssl genrsa -out /etc/pgbouncer/server.key 2048

# 產生憑證
openssl req -new -x509 -days 365 \
  -key /etc/pgbouncer/server.key \
  -out /etc/pgbouncer/server.crt \
  -subj "/CN=pgbouncer"

# 設定權限
chmod 600 /etc/pgbouncer/server.key
chown pgbouncer:pgbouncer /etc/pgbouncer/server.key

效能調校參數

連線池大小設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
[pgbouncer]
; 每個資料庫/使用者組合的預設連線池大小
default_pool_size = 20

; 最大客戶端連線數
max_client_conn = 1000

; 允許的最小連線池大小
min_pool_size = 5

; 保留的額外連線數
reserve_pool_size = 5

; 保留連線啟用的等待時間（秒）
reserve_pool_timeout = 3

連線池大小計算公式

建議的連線池大小計算：

1
default_pool_size = (CPU 核心數 × 2) + 有效磁碟數

例如，4 核心 CPU + 1 個 SSD：

1
default_pool_size = (4 × 2) + 1 = 9

逾時設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[pgbouncer]
; 客戶端連線逾時（秒）
client_login_timeout = 60

; 客戶端閒置逾時（秒）
client_idle_timeout = 0

; 伺服器連線逾時（秒）
server_connect_timeout = 15

; 伺服器登入逾時（秒）
server_login_retry = 15

; 查詢逾時（秒，0 表示無限）
query_timeout = 0

; 查詢等待逾時（秒）
query_wait_timeout = 120

; 伺服器閒置逾時（秒）
server_idle_timeout = 600

; 伺服器生命週期（秒）
server_lifetime = 3600

記憶體設定

1
2
3
4
5
6
7
8
9
[pgbouncer]
; 封包緩衝區大小
pkt_buf = 4096

; 最大封包大小
max_packet_size = 2147483647

; 預先配置的封包緩衝區數量
sbuf_lookahead = 0

DNS 設定

1
2
3
4
5
6
7
8
9
[pgbouncer]
; DNS 快取時間（秒）
dns_max_ttl = 15

; DNS 區域快取時間（秒）
dns_zone_check_period = 0

; DNS NXDOMAIN 快取時間（秒）
dns_nxdomain_ttl = 15

最佳化設定範例

針對高負載 Web 應用的最佳化設定：

 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
31
32
33
34
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb pool_size=30

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/pgbouncer

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction

; 連線池設定
max_client_conn = 5000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3

; 逾時設定
client_login_timeout = 60
client_idle_timeout = 0
server_connect_timeout = 10
server_login_retry = 5
query_wait_timeout = 60
server_idle_timeout = 300
server_lifetime = 1800

; 日誌
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

多資料庫與多使用者設定

多資料庫設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
[databases]
; 基本設定
production = host=db1.example.com port=5432 dbname=production_db
staging = host=db2.example.com port=5432 dbname=staging_db
analytics = host=analytics.example.com port=5432 dbname=analytics_db

; 使用不同的連線池大小
highload_db = host=127.0.0.1 dbname=highload pool_size=50

; 強制使用特定使用者連線
shared_db = host=127.0.0.1 dbname=shared user=app_user password=secret

; 使用萬用字元
* = host=127.0.0.1 port=5432

; 連線到相同資料庫的別名
mydb_ro = host=replica.example.com port=5432 dbname=mydb pool_mode=session
mydb_rw = host=primary.example.com port=5432 dbname=mydb pool_mode=transaction

資料庫層級參數

每個資料庫可以設定獨立參數：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
[databases]
; 完整的資料庫設定
mydb = host=127.0.0.1 \
       port=5432 \
       dbname=mydb \
       user=myuser \
       password=mypassword \
       pool_size=20 \
       pool_mode=transaction \
       max_db_connections=100 \
       client_encoding=UTF8 \
       datestyle=ISO \
       timezone=Asia/Taipei

多使用者設定

userlist.txt 支援多個使用者：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
; 應用程式使用者
"webapp" "SCRAM-SHA-256$4096:..."
"api_service" "SCRAM-SHA-256$4096:..."
"batch_worker" "SCRAM-SHA-256$4096:..."

; 管理使用者
"pgbouncer_admin" "admin_secret"
"pgbouncer_stats" "stats_secret"

; 唯讀使用者
"readonly_user" "SCRAM-SHA-256$4096:..."

使用者層級限制

1
2
3
4
5
6
7
8
9
[pgbouncer]
; 預設的每使用者連線數限制
max_user_connections = 100

[users]
; 特定使用者設定
webapp = pool_mode=transaction max_user_connections=200
batch_worker = pool_mode=session max_user_connections=10
readonly_user = pool_mode=statement

多租戶架構

為每個租戶建立獨立的資料庫設定：

1
2
3
4
5
6
7
8
[databases]
; 租戶資料庫
tenant_001 = host=127.0.0.1 dbname=tenant_001 pool_size=10
tenant_002 = host=127.0.0.1 dbname=tenant_002 pool_size=10
tenant_003 = host=127.0.0.1 dbname=tenant_003 pool_size=10

; 共用資料庫
shared = host=127.0.0.1 dbname=shared pool_size=30 pool_mode=transaction

使用設定檔包含：

1
2
3
4
5
6
; /etc/pgbouncer/pgbouncer.ini
[pgbouncer]
conffile = /etc/pgbouncer/pgbouncer.ini

; 包含租戶設定
%include /etc/pgbouncer/tenants.ini

1
2
3
4
5
; /etc/pgbouncer/tenants.ini
[databases]
tenant_001 = host=127.0.0.1 dbname=tenant_001
tenant_002 = host=127.0.0.1 dbname=tenant_002
; ... 更多租戶

監控與管理

管理控制台

連線到 pgBouncer 管理介面：

1
2
# 使用 psql 連線
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

常用管理命令

 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
-- 顯示連線池狀態
SHOW POOLS;

-- 顯示客戶端連線
SHOW CLIENTS;

-- 顯示伺服器連線
SHOW SERVERS;

-- 顯示資料庫設定
SHOW DATABASES;

-- 顯示統計資訊
SHOW STATS;

-- 顯示詳細統計
SHOW STATS_TOTALS;

-- 顯示記憶體使用
SHOW MEM;

-- 顯示目前設定
SHOW CONFIG;

-- 顯示版本
SHOW VERSION;

管理操作

 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
-- 重新載入設定
RELOAD;

-- 暫停資料庫（等待現有連線完成）
PAUSE mydb;

-- 暫停所有資料庫
PAUSE;

-- 恢復資料庫
RESUME mydb;

-- 恢復所有資料庫
RESUME;

-- 關閉閒置連線
KILL mydb;

-- 取消所有查詢
CANCEL mydb;

-- 優雅關閉
SHUTDOWN;

-- 等待連線完成後關閉
SHUTDOWN WAIT_FOR_SERVERS;

監控腳本

建立 /usr/local/bin/pgbouncer-stats.sh：

 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
31
32
33
34
35
36
37
38
#!/bin/bash

PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"
PGBOUNCER_USER="pgbouncer_stats"

# 取得連線池統計
get_pool_stats() {
    psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER pgbouncer -c "SHOW POOLS;" -t
}

# 取得統計資訊
get_stats() {
    psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER pgbouncer -c "SHOW STATS;" -t
}

# 檢查連線數
check_connections() {
    local result=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER pgbouncer -t -c \
        "SELECT sum(cl_active) as active, sum(cl_waiting) as waiting FROM SHOW POOLS;")
    echo "Active/Waiting connections: $result"
}

case "$1" in
    pools)
        get_pool_stats
        ;;
    stats)
        get_stats
        ;;
    connections)
        check_connections
        ;;
    *)
        echo "Usage: $0 {pools|stats|connections}"
        exit 1
        ;;
esac

Prometheus 監控整合

使用 pgbouncer_exporter 匯出指標：

1
2
3
4
# 安裝 pgbouncer_exporter
wget https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.7.0/pgbouncer_exporter-0.7.0.linux-amd64.tar.gz
tar xzf pgbouncer_exporter-0.7.0.linux-amd64.tar.gz
sudo mv pgbouncer_exporter-0.7.0.linux-amd64/pgbouncer_exporter /usr/local/bin/

建立 systemd 服務：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# /etc/systemd/system/pgbouncer-exporter.service
[Unit]
Description=pgBouncer Exporter
After=network.target

[Service]
Type=simple
User=prometheus
ExecStart=/usr/local/bin/pgbouncer_exporter \
  --pgBouncer.connectionString="postgres://pgbouncer_stats:password@127.0.0.1:6432/pgbouncer?sslmode=disable"
Restart=always

[Install]
WantedBy=multi-user.target

Grafana Dashboard

重要監控指標：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Prometheus 查詢範例
panels:
  - title: "Active Connections"
    query: "pgbouncer_pools_client_active"

  - title: "Waiting Clients"
    query: "pgbouncer_pools_client_waiting"

  - title: "Server Connections"
    query: "pgbouncer_pools_server_active + pgbouncer_pools_server_idle"

  - title: "Query Duration (avg)"
    query: "rate(pgbouncer_stats_queries_duration_seconds_total[5m]) / rate(pgbouncer_stats_queries_total[5m])"

  - title: "Queries per Second"
    query: "rate(pgbouncer_stats_queries_total[1m])"

日誌分析

設定詳細日誌：

1
2
3
4
5
6
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
verbose = 0

使用 logrotate 管理日誌：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# /etc/logrotate.d/pgbouncer
/var/log/pgbouncer/*.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    create 0640 pgbouncer pgbouncer
    postrotate
        /usr/bin/killall -HUP pgbouncer 2>/dev/null || true
    endscript
}

高可用架構

架構一：HAProxy + pgBouncer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
                    ┌─────────────┐
                    │   HAProxy   │
                    │  (VIP: 80)  │
                    └──────┬──────┘
                           │
              ┌────────────┼────────────┐
              │            │            │
       ┌──────▼──────┐ ┌───▼────┐ ┌─────▼─────┐
       │ pgBouncer 1 │ │ pgB 2  │ │ pgB 3     │
       │   :6432     │ │ :6432  │ │ :6432     │
       └──────┬──────┘ └───┬────┘ └─────┬─────┘
              │            │            │
              └────────────┼────────────┘
                           │
                    ┌──────▼──────┐
                    │ PostgreSQL  │
                    │  Primary    │
                    └─────────────┘

HAProxy 設定：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# /etc/haproxy/haproxy.cfg
frontend pgbouncer_front
    bind *:5432
    mode tcp
    default_backend pgbouncer_back

backend pgbouncer_back
    mode tcp
    balance roundrobin
    option tcp-check

    server pgb1 192.168.1.11:6432 check
    server pgb2 192.168.1.12:6432 check
    server pgb3 192.168.1.13:6432 check

架構二：pgBouncer + PostgreSQL 主從複製

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
                    ┌─────────────────────────────┐
                    │        pgBouncer            │
                    │  (讀寫分離)                  │
                    └─────────────┬───────────────┘
                                  │
                    ┌─────────────┴───────────────┐
                    │                             │
             ┌──────▼──────┐               ┌──────▼──────┐
             │ PostgreSQL  │               │ PostgreSQL  │
             │   Primary   │──Streaming───▶│   Replica   │
             │   (RW)      │   Replication │   (RO)      │
             └─────────────┘               └─────────────┘

pgBouncer 設定：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[databases]
; 寫入連線 -> Primary
mydb = host=primary.example.com port=5432 dbname=mydb

; 讀取連線 -> Replica
mydb_ro = host=replica.example.com port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction

應用程式連線策略：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
# Python 範例
import psycopg2

# 寫入連線
write_conn = psycopg2.connect(
    host="pgbouncer.example.com",
    port=6432,
    dbname="mydb",
    user="app_user"
)

# 讀取連線
read_conn = psycopg2.connect(
    host="pgbouncer.example.com",
    port=6432,
    dbname="mydb_ro",
    user="app_user"
)

架構三：Patroni + pgBouncer

使用 Patroni 實現自動故障轉移：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
                    ┌─────────────────────────────┐
                    │       Consul / etcd         │
                    │    (Service Discovery)      │
                    └─────────────┬───────────────┘
                                  │
                    ┌─────────────▼───────────────┐
                    │        pgBouncer            │
                    │   (動態後端更新)             │
                    └─────────────┬───────────────┘
                                  │
              ┌───────────────────┼───────────────────┐
              │                   │                   │
       ┌──────▼──────┐     ┌──────▼──────┐     ┌──────▼──────┐
       │   Patroni   │     │   Patroni   │     │   Patroni   │
       │ PostgreSQL  │     │ PostgreSQL  │     │ PostgreSQL  │
       │  (Leader)   │     │  (Replica)  │     │  (Replica)  │
       └─────────────┘     └─────────────┘     └─────────────┘

Consul Template 動態更新設定：

1
2
3
4
5
6
# consul-template 設定
template {
  source      = "/etc/pgbouncer/pgbouncer.ini.ctmpl"
  destination = "/etc/pgbouncer/pgbouncer.ini"
  command     = "systemctl reload pgbouncer"
}

模板檔案：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# /etc/pgbouncer/pgbouncer.ini.ctmpl
[databases]
{{- range service "postgres-primary" }}
mydb = host={{ .Address }} port={{ .Port }} dbname=mydb
{{- end }}

{{- range service "postgres-replica" }}
mydb_ro = host={{ .Address }} port={{ .Port }} dbname=mydb
{{- end }}

[pgbouncer]
listen_port = 6432
pool_mode = transaction

架構四：多區域高可用

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
                         ┌──────────────────┐
                         │   Global Load    │
                         │    Balancer      │
                         └────────┬─────────┘
                                  │
           ┌──────────────────────┴──────────────────────┐
           │                                             │
    ┌──────▼──────┐                              ┌──────▼──────┐
    │  Region A   │                              │  Region B   │
    │ ┌─────────┐ │                              │ ┌─────────┐ │
    │ │pgBouncer│ │                              │ │pgBouncer│ │
    │ └────┬────┘ │                              │ └────┬────┘ │
    │      │      │                              │      │      │
    │ ┌────▼────┐ │     ┌──────────────────┐     │ ┌────▼────┐ │
    │ │PostgreSQL│◄────▶│ Logical/Physical │◄────▶│PostgreSQL│ │
    │ │ Primary │ │     │   Replication    │     │ │ Standby │ │
    │ └─────────┘ │     └──────────────────┘     │ └─────────┘ │
    └─────────────┘                              └─────────────┘

健康檢查腳本

 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
31
32
#!/bin/bash
# /usr/local/bin/pgbouncer-healthcheck.sh

PGBOUNCER_HOST="${1:-127.0.0.1}"
PGBOUNCER_PORT="${2:-6432}"
TIMEOUT=5

# 檢查 pgBouncer 是否回應
check_pgbouncer() {
    timeout $TIMEOUT psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U pgbouncer_stats pgbouncer -c "SHOW VERSION;" > /dev/null 2>&1
    return $?
}

# 檢查後端連線
check_backend() {
    local waiting=$(timeout $TIMEOUT psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U pgbouncer_stats pgbouncer -t -c \
        "SELECT sum(sv_active) FROM pgbouncer.pools;" 2>/dev/null | tr -d ' ')

    if [ "$waiting" -gt 0 ]; then
        return 0
    else
        return 1
    fi
}

if check_pgbouncer && check_backend; then
    echo "OK"
    exit 0
else
    echo "CRITICAL"
    exit 2
fi

故障轉移自動化

使用 systemd 監控 pgBouncer：

1
2
3
4
5
6
# /etc/systemd/system/pgbouncer.service.d/override.conf
[Service]
Restart=always
RestartSec=5
StartLimitIntervalSec=60
StartLimitBurst=3

最佳實踐總結

設定建議

選擇適當的連線池模式
- Web 應用：使用 transaction 模式
- 批次處理：使用 session 模式
- 簡單查詢：考慮 statement 模式

合理設定連線數

1
2
default_pool_size = (CPU 核心數 × 2) + 有效磁碟數
max_client_conn = default_pool_size × 預期最大並發使用者數

啟用安全認證
- 生產環境使用 scram-sha-256
- 啟用 TLS 加密
- 限制管理介面存取
監控關鍵指標
- 等待連線數 (cl_waiting)
- 活躍伺服器連線 (sv_active)
- 查詢延遲
- 連線池使用率

常見問題排除

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 檢查 pgBouncer 狀態
sudo systemctl status pgbouncer

# 檢視日誌
sudo tail -f /var/log/pgbouncer/pgbouncer.log

# 測試連線
psql -h 127.0.0.1 -p 6432 -U myuser mydb

# 檢查連線池狀態
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"

透過本文的設定與架構指南，您可以在 Ubuntu 22.04 上建立高效能、高可用的 PostgreSQL 連線池解決方案。pgBouncer 作為輕量級的連線池工具，能有效提升資料庫連線效率，降低系統資源消耗，是生產環境中不可或缺的元件。