連線池管理：PgBouncer、Pgpool-II 與應用層池化完全指南 | PostgreSQL

# Debian/Ubuntu
sudo apt install pgbouncer

# macOS（Homebrew）
brew install pgbouncer

# 查看版本
pgbouncer --version

[databases]
# 語法：別名 = host=主機 port=埠 dbname=資料庫名稱
mydb = host=127.0.0.1 port=5432 dbname=production

# 可設定多個資料庫
analytics = host=replica.internal port=5432 dbname=analytics

# 萬用字元（所有資料庫直接轉發）
* = host=127.0.0.1 port=5432

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

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

# Pooling 模式
pool_mode = transaction

# 連線數量控制
max_client_conn = 1000        # 最大客戶端連線數
default_pool_size = 20        # 每個 pool 的預設後端連線數
min_pool_size = 5             # 最小保持的連線數（避免冷啟動延遲）
reserve_pool_size = 5         # 緊急備用連線數
max_db_connections = 100      # 單一資料庫的最大後端連線總數

# 超時設定（秒）
server_idle_timeout = 600     # 後端閒置連線的最大生存時間
server_connect_timeout = 15   # 與後端建立連線的超時
query_wait_timeout = 120      # 等待可用連線的最大時間

# TLS 設定
server_tls_sslmode = prefer
client_tls_sslmode = prefer

# 日誌
logfile = /var/log/pgbouncer/pgbouncer.log
log_pooler_errors = 1

# 統計資訊
stats_period = 60

# userlist.txt 格式："username" "md5hash或明文密碼"
# 生成 md5 hash
echo -n "密碼username" | md5sum

# 範例
"appuser"  "md5abcdef1234567890abcdef1234567890"
"readonly" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxx"

# pgbouncer.ini
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

-- ❌ SET 指令（下次 Transaction 可能由不同後端處理）
SET search_path = myschema;

-- ❌ LISTEN/NOTIFY（訂閱在 Session 結束時消失）
LISTEN my_channel;

-- ❌ Advisory Lock（Session 級別的鎖無法跨 Transaction 保持）
SELECT pg_advisory_lock(12345);

-- ❌ Prepared Statement（需 Session 保持）
PREPARE my_stmt AS SELECT ...;

[databases]
mydb_session = host=127.0.0.1 dbname=production pool_mode=session
mydb_txn     = host=127.0.0.1 dbname=production pool_mode=transaction

# 連線到 PgBouncer 管理控制台
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- 查看所有 pool 的狀態
SHOW POOLS;
-- 關鍵欄位：cl_active, cl_waiting, sv_active, sv_idle, maxwait

-- 查看統計資訊
SHOW STATS;
-- 關鍵欄位：total_xact_count, total_query_count, avg_xact_time, avg_query_time

-- 查看客戶端/後端連線
SHOW CLIENTS;
SHOW SERVERS;

-- 線上重載設定（不中斷服務）
RELOAD;

-- 維護操作
PAUSE mydb;     -- 暫停接收新查詢
RESUME mydb;    -- 恢復服務
KILL mydb;      -- 關閉特定後端連線

# 高並行 OLTP 應用（如 Web API）
pool_mode = transaction
default_pool_size = 20      # CPU 核心數 × 2
max_client_conn = 2000
min_pool_size = 5

# 分析/報告應用（長查詢）
pool_mode = session
default_pool_size = 10
query_timeout = 3600        # 允許長達 1 小時的查詢
server_idle_timeout = 1800

# 混合環境：用不同 listen port 區分
# pgbouncer_oltp.ini → port 6432，transaction mode
# pgbouncer_analytics.ini → port 6433，session mode

# pgpool.conf
listen_addresses = '*'
port = 5432

# 後端節點
backend_hostname0 = 'primary.db.internal'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = 'replica1.db.internal'
backend_port1 = 5432
backend_weight1 = 2            # Replica 接收更多 SELECT 流量

# Pooling 設定
num_init_children = 32         # 子程序數
max_pool = 4                   # 每個子程序的後端連線數

# 負載平衡
load_balance_mode = on

# 健康檢查
health_check_period = 10
health_check_user = 'pgpool_health'

-- 自動判斷路由目標
SELECT * FROM users WHERE id = 1;       -- → Replica（讀）
INSERT INTO orders VALUES (...);         -- → Primary（寫）
BEGIN; SELECT FOR UPDATE ...; COMMIT;   -- → Primary（寫入語意）
SELECT nextval('my_sequence');          -- → Primary（序列操作）

-- 手動指定路由
/*NO LOAD BALANCE*/ SELECT * FROM users;   -- 強制走 Primary

# odyssey.conf
listen {
    host "*"
    port 6432
}

storage "pg_server" {
    type "remote"
    host "127.0.0.1"
    port 5432
}

database "mydb" {
    user "appuser" {
        authentication "md5"
        password "secret"
        storage "pg_server"
        pool "transaction"
        pool_size 20
        pool_timeout 4000
        pool_ttl 60
    }
}

// Spring Boot application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=appuser

// HikariCP 設定
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.leak-detection-threshold=60000

// 程式碼方式設定
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("appuser");
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);
config.setKeepaliveTime(60000);      // 防止 idle 斷線
config.setMaxLifetime(1800000);      // 30 分鐘後強制重建

HikariDataSource dataSource = new HikariDataSource(config);

from sqlalchemy import create_engine

# 標準設定（QueuePool，預設）
engine = create_engine(
    "postgresql+psycopg2://appuser:secret@localhost:5432/mydb",
    pool_size=5,           # 核心連線數
    max_overflow=10,       # 超過 pool_size 時允許額外建立的連線數
    pool_timeout=30,       # 等待可用連線的超時秒數
    pool_recycle=1800,     # 連線生存時間（秒）
    pool_pre_ping=True,    # 借出前先 ping，確保連線有效
)

# 查看連線池狀態
pool = engine.pool
print(f"Pool size: {pool.size()}")
print(f"Checked in: {pool.checkedin()}")
print(f"Checked out: {pool.checkedout()}")
print(f"Overflow: {pool.overflow()}")

// TypeORM DataSource 設定
const AppDataSource = new DataSource({
    type: 'postgres',
    host: 'localhost',
    port: 5432,
    username: 'appuser',
    database: 'mydb',
    extra: {
        max: 10,                         // 最大連線數
        min: 2,                          // 最小連線數
        idleTimeoutMillis: 600000,       // 閒置超時
        connectionTimeoutMillis: 30000,  // 建立連線超時
        maxUses: 7500,                   // 每條連線最多使用次數後重建
    },
});

-- 觀察實際連線使用情況
SELECT COUNT(*) AS active_connections,
       state,
       wait_event_type
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND backend_type = 'client backend'
GROUP BY state, wait_event_type
ORDER BY active_connections DESC;

# PgBouncer 層
server_idle_timeout = 600        # 後端閒置超過 600 秒則關閉
server_check_query = SELECT 1    # 健康檢查查詢
server_check_delay = 30          # 每 30 秒測試一次閒置連線

# SQLAlchemy 層
engine = create_engine(url, pool_pre_ping=True)

-- PostgreSQL 伺服器端
-- postgresql.conf
tcp_keepalives_idle = 60        -- 閒置 60 秒後開始發送 keepalive
tcp_keepalives_interval = 10    -- keepalive 間隔 10 秒
tcp_keepalives_count = 3        -- 3 次無回應後斷線

# 方案一：PgBouncer 1.21+ 內建追蹤
[pgbouncer]
max_prepared_statements = 100

# 方案二：禁用 Server-side Prepared Statements
engine = create_engine(
    url,
    connect_args={"options": "-c plan_cache_mode=force_generic_plan"}
)

# RDS Proxy 連線字串
# 原本直連：
DATABASE_URL = "postgresql://user:pass@my-db.abc123.rds.amazonaws.com:5432/mydb"

# 透過 RDS Proxy：
DATABASE_URL = "postgresql://user:pass@my-db.proxy-abc123.rds.amazonaws.com:5432/mydb"

# prometheus.yml
scrape_configs:
  - job_name: 'pgbouncer'
    static_configs:
      - targets: ['pgbouncer-exporter:9127']