暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / PgBouncer 从原理到实践

PgBouncer 从原理到实践

原创 岳麓丹枫 3天前
125

Table of Contents

第一部分：核心原理

1.1 PgBouncer 是什么

PgBouncer 是一个 PostgreSQL 的轻量级连接池中间件，用 C 语言编写，单进程事件驱动。

核心价值：

                    前端(便宜)              后端(昂贵)
                ┌──────────────┐      ┌──────────────────┐
  N 个客户端 ──►│  PgBouncer   │─────►│  PostgreSQL      │
  (max_client   │  连接池复用   │ M 个  │  max_connections │
   _conn)       └──────────────┘连接   └──────────────────┘
                N >> M (复用)

|| 指标 | 客户端到 PgBouncer | PgBouncer 到 PostgreSQL | ||------|-------------------|----------------------| || 内存开销 | 2 KB/连接 | 5~10 MB/连接 | || 建连成本 | 极低 | 高（PG fork 进程） | || 数量级 | 可达数千 | 通常数十到数百 |

1.2 为什么需要连接池

PostgreSQL 的连接模型是每个连接一个进程（backend process），这与 MySQL 的线程模型不同：

客户端连接 → PG 主进程 fork → backend 进程 (5~10MB)

连接数过多的代价：

  • 内存浪费：1000 个连接 × 10MB = 10GB 仅用于连接

  • 上下文切换开销：CPU 在大量进程间切换

  • max_connections 升高会降低整体性能

PgBouncer 的解决方案：让 1000 个客户端复用 100 个后端连接。

1.3 为什么有了应用层连接池，还需要 PgBouncer？

先搞懂一个类比

想象一家有 40 个包间的酒店，包间就是你的数据库。

场景一：只有应用层连接池（没有 PgBouncer）

假设你有 3 个服务员（应用实例），每个服务员身上挂着 10 串钥匙（连接池大小=10），每串钥匙能开任何一个包间。

服务员A（身上挂10串钥匙）
服务员B（身上挂10串钥匙）  
服务员C（身上挂10串钥匙）

总钥匙数 = 3 × 10 = 30 串钥匙，全部挂在服务员身上。

问题 1：钥匙浪费——服务员A 手里 10 串钥匙，某一时刻只有 2 个客人需要服务，剩下 8 串白挂。服务员B 那边突然来了 8 个客人，但他只有 10 串也快不够。能借吗？不能。 每个服务员各管各的。

问题 2：钥匙数量随服务员数量线性增长——10 个服务员 × 10 串 = 100 串钥匙，但实际同时服务客人可能 20-30 个。

问题 3：客人走后钥匙不马上还——客人点完菜在聊天（会话还没结束），钥匙一直被占着。这就是"会话级共享"。

场景二：加了 PgBouncer

在酒店大堂放一个 钥匙柜（PgBouncer），所有钥匙统一存在柜子里，只放 5 串。

好处 1：钥匙可以共享——跨服务员共享。 好处 2：用完马上还——客人点完菜（一个事务结束），服务员马上把钥匙还回柜子。这就是"事务级共享"。好处 3：加服务员不增加钥匙——10 个服务员共用柜子里的 5 串钥匙就够了。

对应到技术世界

|| 酒店类比 | 技术对应 | ||---------|---------| || 服务员 | 应用实例（Pod/进程） | || 钥匙串 | 数据库连接 | || 钥匙挂在服务员身上 | 应用层连接池（HikariCP/GORM） | || 钥匙柜 | PgBouncer | || 点菜动作 | 一个事务 | || 点完菜马上还钥匙 | 事务结束立即归还连接 |

应用层连接池解决的是"单个实例内部复用"，PgBouncer 解决的是"所有实例统一复用 + 更细粒度的复用"。

1.4 池模型：按 (database, user) 分池

PgBouncer 不是一个全局大池，而是按 (database, user) 二元组切分成多个独立小池：

PgBouncer 进程
├── pool(db_A, user_X)  ← 独立池, 互不共享
├── pool(db_A, user_Y)
├── pool(db_B, user_X)
└── pool(db_B, user_Y)

关键特性：

  • 每个池有独立的 pool_size 上限

  • 池是按需创建的：没有客户端连接时，池不存在

  • 连接是逐个按需建立的，不是预分配

  • 空闲连接通过 server_idle_timeout 逐步回收

pool_size 是预分配还是按需增长？

pool_size 是上限，不是预分配数量。答案是：一个个按需增长，上限 N 个。

以 pool_size=2 为例：

时间线                后端连接数    说明
─────────────────────────────────────────────────────────
PgBouncer 启动        0           池不存在, 不预分配
首个客户端连接进来     0→1         按需新建 1 个后端连接
客户端查询完成        1           连接保持(空闲), 不关闭
第二个客户端同时访问   1→2         再新建 1 个, 达到上限 2
第三个客户端访问       2(排队)     达上限, 客户端等待
所有客户端断开+空闲10分钟  2→1→0   server_idle_timeout 逐步关闭

如果想"预热"：用 min_pool_size

min_pool_size = 1            ; ★ 维持至少 1 个连接

重要前提：min_pool_size 仅在以下情况生效：

  1. 该 database 配置了 forced user（在 [databases] 段指定了 user=），或

  2. 至少有一个客户端已连接到该池

无 min_pool_size:     0 → 1 → 2 → (空闲) → 1 → 0
有 min_pool_size=1:   0 → 1 → 2 → (空闲) → 1 → 1 (维持不下于 1)

第二部分：三种 Pooling 模式

|| 模式 | 连接分配时机 | 连接归还时机 | 复用率 | 兼容性 | ||------|------------|------------|--------|--------| || session | 客户端连接时 | 客户端断开时 | 1:1（最低） | 100% 兼容 | || transaction | 事务开始时 | 事务结束时 | 5~50 倍 | 破坏会话级功能 | || statement | 语句执行时 | 语句执行后 | 最高 | 强制 autocommit |

模式选择决策树：

应用是否使用会话级功能?
├── 是 (临时表/SET/LISTEN/会话锁)
│   └── session pooling
└── 否
    └── 是否需要多语句事务?
        ├── 是 → transaction pooling  ★ 最常用
        └── 否 (纯 autocommit) → statement pooling

第三部分：编译安装与 systemd 管理

3.1 编译安装

环境: openEuler 22.03 (aarch64) | 日期: 2026-07-18

# 安装编译依赖
dnf install -y libevent-devel openssl-devel

# 下载源码
mkdir -p /tmp/pgbouncer_build && cd /tmp/pgbouncer_build
curl -sL -o pgbouncer-1.25.2.tar.gz \
  'https://github.com/pgbouncer/pgbouncer/releases/download/pgbouncer\_1\_25\_2/pgbouncer-1.25.2.tar.gz'
tar xzf pgbouncer-1.25.2.tar.gz && cd pgbouncer-1.25.2

# 配置编译
./configure --prefix=/postgresql/pgbouncer

# 编译（跳过 man 文档，避免缺 pandoc 报错）
make -j$(nproc) pgbouncer

# 安装
mkdir -p /postgresql/pgbouncer && make install

安装目录结构：

/postgresql/pgbouncer/
├── bin/pgbouncer                  # 主程序 (2.5MB)
├── conf/                          # 配置文件目录
├── run/                           # 运行时 PID 目录
├── log/                           # 日志目录
└── share/doc/pgbouncer/           # 示例和文档

验证安装：

/postgresql/pgbouncer/bin/pgbouncer --version
# PgBouncer 1.25.2 / libevent 2.1.12-stable / adns: evdns2 / tls: OpenSSL 1.1.1wa

3.2 systemd 启停管理

systemd Type 选择（重要）

PgBouncer 的 systemd Type 选择取决于启动方式，必须匹配：

|| 方案 | Type | ExecStart | 说明 | ||------|------|-----------|------| || 推荐 | notify | /.../pgbouncer /.../pgbouncer.ini | 1.23+ 支持 sd_notify，精确感知就绪 | || 可用 | forking | /.../pgbouncer -d /.../pgbouncer.ini | 加 -d 参数 fork 到后台 | || 可用 | simple | /.../pgbouncer /.../pgbouncer.ini | 不加 -d，前台运行 |

常见错误：Type=forking 但 ExecStart 不加 -d——PgBouncer 不会 fork，systemd 认为启动失败。

Service 文件（推荐 Type=notify）

[Unit]
Description=PgBouncer - PostgreSQL Connection Pooler
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=pgbouncer
Group=pgbouncer
ExecStart=/postgresql/pgbouncer/bin/pgbouncer /postgresql/pgbouncer/conf/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -INT $MAINPID
PIDFile=/postgresql/pgbouncer/run/pgbouncer.pid
Restart=on-failure
RestartSec=3
LimitNOFILE=65536
LimitNPROC=4096

[Install]
WantedBy=multi-user.target

注意：使用 Type=notify 需要编译时启用 systemd 支持。如果编译时 systemd = no，改用 Type=simple 或 Type=forking + -d。

  • 如果 Type 使用 simple
[Unit]
Description=PgBouncer - PostgreSQL Connection Pooler
After=network-online.target
Wants=network-online.target

[Service]
Type=simple
User=pgbouncer
Group=pgbouncer
# simple 模式：进程必须前台运行，去掉 -d
ExecStart=/postgresql/pgbouncer/bin/pgbouncer /postgresql/pgbouncer/conf/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -INT $MAINPID
# simple 模式下 PIDFile 不影响服务状态判断，但保留可用于 ExecReload/ExecStop 定位
PIDFile=/postgresql/pgbouncer/run/pgbouncer.pid
Restart=on-failure
RestartSec=3
LimitNOFILE=65536
LimitNPROC=4096

[Install]
WantedBy=multi-user.target

创建运行用户

useradd -r -s /sbin/nologin -d /postgresql/pgbouncer/run pgbouncer
mkdir -p /postgresql/pgbouncer/{conf,run,log}
chown -R pgbouncer:pgbouncer /postgresql/pgbouncer

启停命令

systemctl daemon-reload
systemctl start pgbouncer
systemctl status pgbouncer
systemctl reload pgbouncer     # 热重载（不中断连接）
systemctl enable pgbouncer     # 开机自启
journalctl -u pgbouncer -f     # 查看日志

3.3 一键安装脚本

#!/bin/bash
set -e
VERSION="1.25.2"
INSTALL_PREFIX="/postgresql/pgbouncer"

dnf install -y libevent-devel openssl-devel
mkdir -p /tmp/pgbouncer_build && cd /tmp/pgbouncer_build
curl -sL -o "pgbouncer-${VERSION}.tar.gz" \
  "https://github.com/pgbouncer/pgbouncer/releases/download/pgbouncer\_${VERSION//./_}/pgbouncer-${VERSION}.tar.gz"
tar xzf "pgbouncer-${VERSION}.tar.gz" && cd "pgbouncer-${VERSION}"
./configure --prefix="$INSTALL_PREFIX"
make -j$(nproc) pgbouncer
mkdir -p "$INSTALL_PREFIX" && make install
mkdir -p "$INSTALL_PREFIX"/{conf,run,log}
cp "$INSTALL_PREFIX"/share/doc/pgbouncer/pgbouncer.ini "$INSTALL_PREFIX"/conf/
cp "$INSTALL_PREFIX"/share/doc/pgbouncer/userlist.txt "$INSTALL_PREFIX"/conf/
"$INSTALL_PREFIX/bin/pgbouncer" --version

第四部分：配置详解

4.1 完整配置示例1

以"40 个 database、1 个 user、常态 1-2 连接/库、峰值 5/库"的场景为例：

[databases]
;; 方案 A: 所有库在同一台 PG (用通配符)
* = host=127.0.0.1 port=5432

;; 方案 B: 库分布在不同 PG (逐条列出, 不配 pool_size)
; db_app_main    = host=10.0.0.1 port=5432 dbname=db_app_main

;; 方案 C: 某库需要单独调优 (覆盖通配符)
; db_hot = host=127.0.0.1 port=5432 pool_size=5

[pgbouncer]
;; ---- 基础监听 ----
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql

;; ---- 池化模式 ----
pool_mode = transaction          ;; ★ 生产首选

;; ---- 池大小 ----
default_pool_size = 3            ;; 常态每池上限
min_pool_size = 0                ;; 不预热
reserve_pool_size = 1            ;; 突发额外加 1
reserve_pool_timeout = 3

;; ---- 前端连接 ----
max_client_conn = 1000

;; ---- 跨池上限 ----
max_db_connections = 5           ;; ★ 单库后端连接硬上限

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

;; ---- 连接健康检查与生命周期 ----
server_reset_query = DISCARD ALL
server_reset_query_always = 0       ;; transaction 模式下默认不自动执行
server_check_delay = 30
server_check_query = select 1
server_lifetime = 3600
server_idle_timeout = 600
server_connect_timeout = 15
server_login_retry = 15
client_login_timeout = 60

;; ---- 危险超时 ----
query_timeout = 0
query_wait_timeout = 120
client_idle_timeout = 0
idle_transaction_timeout = 60       ;; ★ 事务空闲60秒强制断开
transaction_timeout = 0
cancel_wait_timeout = 10

;; ---- 日志 ----
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
log_stats = 1
stats_period = 60

;; ---- 管理控制台 ----
admin_users = dbadmin
stats_users = dbmonitor

;; ---- 预处理语句 (transaction 模式, 1.21+) ----
;; 默认值为 0（禁用），设为 > 0 时启用协议级 prepared statement 跟踪
;; 注意：这是每连接独立跟踪的上限，不是全局共享
max_prepared_statements = 200

;; ---- 底层网络 ----
pkt_buf = 4096
listen_backlog = 128
tcp_keepalive = 1
;; tcp_keepidle = 60
;; tcp_keepintvl = 10
;; tcp_keepcnt = 6

示例2

;; ============================================================
;; pgbouncer.ini - 生产环境配置模板
;; ============================================================

[databases]
;; 方案 A: 所有库在同一台 PG (用通配符)
* = host=127.0.0.1 port=54321

;; 方案 B: 库分布在不同 PG (逐条列出, 不配 pool_size)
; db_app_main    = host=10.0.0.1 port=5432 dbname=db_app_main
; db_app_order   = host=10.0.0.1 port=5432 dbname=db_app_order
; db_tenant_01   = host=10.0.0.2 port=5432 dbname=db_tenant_01

;; 方案 C: 某库需要单独调优 (覆盖通配符)
; db_hot = host=127.0.0.1 port=5432 pool_size=5

;; 高并发库：覆盖通配符的 default_pool_size
foundation    = host=127.0.0.1 port=54321 pool_size=4
efc           = host=127.0.0.1 port=54321 pool_size=3
is            = host=127.0.0.1 port=54321 pool_size=3
imos          = host=127.0.0.1 port=54321 pool_size=2
sw            = host=127.0.0.1 port=54321 pool_size=2
ras           = host=127.0.0.1 port=54321 pool_size=2
accesscontrol = host=127.0.0.1 port=54321 pool_size=2
cds           = host=127.0.0.1 port=54321 pool_size=2


[pgbouncer]
;; ---- 基础监听 ----
listen_addr = 0.0.0.0
listen_port = 5432
unix_socket_dir = /tmp


;; ---- 池化模式 ----
;; ★ 生产首选
pool_mode = transaction

;; ---- 池大小 ----
;; 常态每池上限
default_pool_size = 1
;; 不预热 (0=禁用)
min_pool_size = 0
;; 突发额外加 1
reserve_pool_size = 1
;; 3秒拿不到才动用 reserve
reserve_pool_timeout = 3

;; ---- 前端连接 ----
;; 客户端总连接上限
max_client_conn = 1000

;; ---- 跨池上限 ----
;; ★ 单库后端连接硬上限(0 表示不限制)
;; max_db_connections = 0
;; max_user_connections = 100     ;; 单用户后端连接上限 (可选)


;; ============================================================
;; 认证配置
;; ============================================================
;; 推荐使用 SCRAM
auth_type = scram-sha-256
auth_file = /usr/local/pgbouncer/conf/userlist.txt

;; 进阶: 从 PG 动态查询用户密码 (免维护 userlist.txt)
;; auth_user = pgbouncer_auth
;; auth_query = SELECT rolname, rolpassword FROM pg_authid WHERE rolname = $1 AND rolcanlogin
;; auth_dbname = postgres


;; ============================================================
;; 连接健康检查与生命周期
;; ============================================================
;; session 模式清理 (transaction 模式不执行)
server_reset_query = DISCARD ALL
;; 不强制在 transaction 模式执行
server_reset_query_always = 0
;; 30秒内复用不检查
server_check_delay = 30
;; 健康检查语句
server_check_query = select 1
;; 后端连接最多活 1 小时
server_lifetime = 1800
;; 空闲 10 分钟关闭
server_idle_timeout = 120
;; 连 PG 超时
server_connect_timeout = 15
;; 登录失败重试间隔
server_login_retry = 15
;; 客户端登录超时
client_login_timeout = 60


;; ============================================================
;; 危险超时 (谨慎使用, 按需开启)
;; ============================================================
;; 查询执行超时 (0=禁用)
query_timeout = 0
;; 客户端等连接超时, 防止无限排队
query_wait_timeout = 30
;; 客户端空闲超时 (0=禁用)
client_idle_timeout = 0
;; ★ 事务空闲 60秒强制断开
idle_transaction_timeout = 60
;; 取消请求超时
cancel_wait_timeout = 10


;; ============================================================
;; 日志
;; ============================================================
logfile = /usr/local/pgbouncer/conf/pgbouncer.log
pidfile = /usr/local/pgbouncer/conf/pgbouncer.pid

; log_connections = 1
; log_disconnections = 1
log_pooler_errors = 1
log_stats = 1
;; 每 60 秒输出统计
stats_period = 60

;; 可选: syslog
;; syslog = 1
;; syslog_ident = pgbouncer
;; syslog_facility = daemon


;; ============================================================
;; 管理控制台
;; ============================================================
admin_users = postgres
stats_users = postgres


;; ============================================================
;; 预处理语句 (transaction 模式)
;; ============================================================
;; 跟踪的预处理语句数量, 设置为 0 禁用 扩展查询(Extended Query) , 使用 简单查询(Simple Query)
#max_prepared_statements = 400


;; ============================================================
;; 底层网络 (通常不需要改)
;; ============================================================
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 6


;; 忽略JDBC等驱动发送的extra_float_digits参数
ignore_startup_parameters = extra_float_digits

通配符 * 的用法

* = host=127.0.0.1 port=5432 含义：任何客户端请求的 database 名自动映射到 127.0.0.1:5432 上的同名 database。通配符和具体条目可共存，具体条目优先于通配符。

事务模式下 Extended Protocol 配置示例

pool_mode = transaction

;; 正确的参数名是 max_prepared_statements（不是 max_prepare_statements）
max_prepared_statements = 200

;; 某些 ORM（如 pgx、rust-postgres）需要在启动时设置此参数
ignore_startup_parameters = extra_float_digits

4.2 认证文件 userlist.txt

;; 格式: "username" "password"
;; 密码支持三种格式: 明文 / MD5 / SCRAM-SHA-256

"postgres" "SCRAM-SHA-256$4096:base64salt$base64storedkey:base64serverkey"
"app_user" "SCRAM-SHA-256$4096:base64salt$base64storedkey:base64serverkey"
"dbadmin"  "SCRAM-SHA-256$4096:base64salt$base64storedkey:base64serverkey"

从 PG 导出：SELECT rolname, rolpassword FROM pg_authid WHERE rolcanlogin;

4.3 PostgreSQL 侧配套配置

# postgresql.conf
max_connections = 150
superuser_reserved_connections = 5

# pg_hba.conf
host    all       all       127.0.0.1/32  scram-sha-256

4.4 系统级配置

ulimit -n 65535   # max_client_conn=1000 至少需要 >= 3000
# /etc/security/limits.conf: * soft nofile 65535 / * hard nofile 65535
# systemd: LimitNOFILE=65536

4.5 不确定哪些库需要单独调优时的简化策略

用全局 default_pool_size + max_db_connections 兜底，不要提前猜：

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
pool_mode = transaction
default_pool_size = 3           ; 常态 1-2, 给点余量设 3
max_db_connections = 5          ; ★ 任何库都不会超过 5

跑一周后用 SHOW POOLS 观察哪个库频繁排队，再单独给它配 pool_size=5。

第五部分：Transaction 模式的限制（重点）

5.1 不可用的功能

|| 功能 | 原因 | ||------|------| || **SET** / **RESET** | 会话级参数不跨事务保留 | || **LISTEN** | 通知通道绑定到特定后端连接 | || **WITH HOLD CURSOR** | 可保持游标跨事务, 连接已归还 | || SQL 级 **PREPARE**/**DEALLOCATE** | 预处理语句绑定到连接 | || 临时表（**PRESERVE/DELETE ROWS**） | 行数据跨事务不保证 | || **LOAD** 语句 | 加载的库绑定到连接 | || 会话级咨询锁 | pg_advisory_lock() 绑定到连接 |

5.2 仍可用的功能

|| 功能 | 说明 | ||------|------| || 启动参数 | client_encoding、DateStyle、Timezone 等（仅通过连接串/启动参数设置生效，事务内 **SET** 不保留） | || NOTIFY | 单次通知可用 | || 普通游标 | WITHOUT HOLD 在事务内可用 | || 协议级预处理语句 | 需配 max_prepared_statements > 0（1.21+） | || ON COMMIT DROP 临时表 | 事务结束时自动删除 |

5.3 会话级状态的处理建议

方案 1: 改用 session pooling (牺牲复用率)
方案 2: 应用改造, 将会话状态存到应用层或 Redis
方案 3: 混合模式 (部分库 session, 部分库 transaction)

混合模式配置：

[databases]
db_app_main = host=... pool_mode=transaction
db_report   = host=... pool_mode=session

5.4 server_reset_query 在 transaction 模式下的行为

  • server_reset_query_always = 0（默认）：transaction 模式下默认不自动执行 DISCARD ALL

  • 设 server_reset_query_always=1 会每次事务结束都执行 DISCARD ALL，会破坏应用状态，不推荐

5.5 PgBouncer 在 事务模式下,  Extended Protocol 的已知限制

|| 限制 | 替代方案 | ||------|----------| || 不支持命名 Portal / DECLARE CURSOR | WITH HOLD 或 pool_mode=session | || 不支持 LISTEN/NOTIFY | pool_mode=session | || 不支持 SET/RESET 跨事务 | 连接串参数或 server_reset_query | || 不支持临时表跨事务 | pool_mode=session | || 不支持 PREPARE TRANSACTION（两阶段提交） | 直连 PG 或 pool_mode=session |

5.6 PgBouncer 事务模式下，用扩展协议会陷入两条路径都报错的死局：

正确做法

PgBouncer 事务模式 + 扩展协议 = 不可用，必须配简单协议(涉及业务层修改)：

config.PreferSimpleProtocol = true

简单协议不走 Parse/Bind/Execute，没有 prepared statement，连接归还后状态完全干净，零冲突风险。

路径 A：PgBouncer 归还时执行 DISCARD ALL

1. 客户端 pgx 在连接 C1 上：Parse("S_1", SQL) → 缓存 prepared statement 描述
2. 事务结束 → PgBouncer DISCARD ALL → S_1 从 PostgreSQL 服务端删除
3. 客户端下次拿到连接 C2，pgx 客户端缓存仍认为 S_1 存在
4. pgx 直接发 Bind/Execute("S_1") → ❌ ERROR: prepared statement "S_1" does not exist

路径 B：PgBouncer 归还时不执行 DISCARD ALL

1. 客户端 A 在连接 C1 上：Parse("S_1", SQL_A) → COMMIT
2. 连接 C1 归还池，S_1 还留在 C1 上（没清理）
3. 客户端 B 拿到 C1：尝试 Parse("S_1", SQL_B) → ❌ ERROR: prepared statement "S_1" already exists
   （同名但不同 SQL，冲突）

根因

prepared statement 是 session 级对象，它的生命周期绑定在后端连接上，而不是客户端事务上。PgBouncer 事务模式复用连接时：

状态 问题
服务端有 statement，客户端缓存匹配 碰巧正常（同一连接、同一 SQL）
服务端有 statement，客户端缓存不匹配（换连接了） ❌ SQL 不对
服务端无 statement（被 DISCARD），客户端缓存以为有 ❌ does not exist
服务端有 statement，新客户端想创建同名 ❌ already exists

只有"碰巧"场景正常，其他全部报错。

第六部分：参数详解与配置经验

6.1 池大小参数

|| 参数 | 作用 | 配置经验 | ||------|------|---------| || default_pool_size | 每池默认上限 | 常态连接数 × 1.5~2 | || min_pool_size | 每池最小连接数 | 0（默认）；设 1 可抗突发但有前提 | || reserve_pool_size | 突发额外连接 | 1~3 | || reserve_pool_timeout | 何时动用 reserve | 3~5 秒 | || max_db_connections | 单库硬上限 | 锁死峰值 | || max_user_connections | 单用户硬上限 | 多应用共用时使用 |

核心公式：Σ pool_size ≤ PG max_connections × 0.8

6.2 max_client_conn 的设计逻辑

从 5 个视角交叉验证 max_client_conn = 1000：

|| 推算视角 | 计算逻辑 | 得出值 | ||---------|---------|--------| || 原直连峰值放大 | 200 × 5 倍 | 1000 | || transaction 复用率 | 60 × 15 倍 | 900 | || 应用部署规模 | 20 台 × 50 | 1000 | || 突发容灾 | 500 × 2 | 1000 | || 资源开销验证 | 1000 × 2KB = 2MB | 无瓶颈 |

6.3 超时参数

|| 参数 | 默认值 | 推荐值 | 说明 | ||------|--------|--------|------| || server_idle_timeout | 600s | 600s | 空闲连接回收 | || server_lifetime | 3600s | 3600s | 连接最大生命周期 | || query_wait_timeout | 120s | 60s | 客户端排队超时 | || idle_transaction_timeout | 0 | 60s | 事务空闲超时（推荐开启） | || client_idle_timeout | 0 | 0 | 客户端空闲超时 |

三个超时容易混淆：

客户端连接进来, 不做事      → client_idle_timeout
客户端 BEGIN 后不做事       → idle_transaction_timeout
客户端 BEGIN 后一直执行慢查询 → transaction_timeout

6.4 参数取值优先级

1. [databases] 段的 pool_size=      (最高优先级)
2. [users] 段的 pool_size=
3. default_pool_size                  (兜底)

第七部分：TCP Keepalive 参数详解

tcp_keepalive = 1       # 总开关: 启用 TCP keepalive
tcp_keepidle = 60       # 连接空闲 60 秒后, 开始发送探测包
tcp_keepintvl = 10      # 每次探测间隔 10 秒
tcp_keepcnt = 6         # 连续 6 次探测无响应, 判定连接已死

总死亡检测时间 = tcp_keepidle + tcp_keepintvl × tcp_keepcnt = 60 + 10 × 6 = 120 秒

如果不设这几个参数，Linux 默认要 2 小时 + 11 分钟才发现死连接，太慢！生产环境建议显式设置。

第八部分：认证机制详解

8.1 两种用户，两套认证模型

|| 用户类型 | 密码从哪来 | 是否要在 PG 中存在 | ||---------|-----------|-----------------| || 业务用户 (app_user) | 从 PG 复制过来，必须一致 | ✅ 必须 | || admin/stats 用户 | 你自己定义 | ❌ 不需要 |

admin_users / stats_users 只需要在 PgBouncer 的 userlist.txt 中配置即可，不需要在 PG 实例中创建。连接到 pgbouncer 虚拟库时，请求不会转发到 PostgreSQL。

8.2 权限区别

|| 角色 | 能做什么 | 不能做什么 | ||------|---------|-----------| || admin_users | 所有 SHOW + PAUSE/RESUME/RELOAD/KILL/RECONNECT/SHUTDOWN | - | || stats_users | 所有 SHOW 命令（只读） | 不能执行管理命令 |

8.3 admin/stats 用户的密码怎么来

你自己定。三种方式：

方法 1：明文密码（最简单）

"dbadmin"  "MyAdminPass2024!"
"dbmonitor" "MyMonitorPass2024!"

方法 2：SCRAM-SHA-256 哈希（推荐生产用）

方式 A：临时在 PG 中创建用户，拷贝哈希，再删除

CREATE USER tmp_dbadmin PASSWORD 'MyAdminPass2024!';
SELECT rolpassword FROM pg_authid WHERE rolname = 'tmp_dbadmin';
DROP USER tmp_dbadmin;

方式 B：用 PgBouncer 自带的命令（1.23+）

pgbouncer --scram "MyAdminPass2024!"

完整操作流程

ADMIN_PASS="MyAdminPass2024!"
psql -p 5432 -U postgres -c "CREATE USER tmp_gen PASSWORD '${ADMIN_PASS}';"
ADMIN_HASH=$(psql -p 5432 -U postgres -tAc "SELECT rolpassword FROM pg_authid WHERE rolname='tmp_gen'")
psql -p 5432 -U postgres -c "DROP USER tmp_gen;"

cat >> /etc/pgbouncer/userlist.txt << EOF
"dbadmin" "${ADMIN_HASH}"
EOF

chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
chmod 600 /etc/pgbouncer/userlist.txt
psql -p 6432 pgbouncer -c "RELOAD;"

8.4 auth_user / auth_query 机制

只适用于业务用户，admin/stats 用户必须在 userlist.txt 中手动配置。

auth_user = pgbouncer_auth
auth_query = SELECT rolname, rolpassword FROM pg_authid WHERE rolname = $1
auth_dbname = postgres

第九部分：Extended Protocol 与 Prepared Statement 详解

这是 PgBouncer 最容易混淆的概念。PgBouncer 1.25 transaction 模式支持 Extended Protocol，也支持协议级 named Prepared Statement（通过 max_prepared_statements），但它不是完整的 PostgreSQL session prepared statement 语义，因此不能保证所有驱动、所有 prepared statement 使用方式都兼容。

9.1 两种预处理语句的本质区别

┌─────────────────────────────────────────────────────────────┐
│  类型 1: SQL 级预处理语句 (SQL-level PREPARE)              │
│                                                             │
│  客户端发 SQL 命令:                                         │
│    PREPARE stmt1 AS SELECT * FROM users WHERE id = $1;     │
│    EXECUTE stmt1(1);                                        │
│    DEALLOCATE stmt1;                                        │
│                                                             │
│  transaction 模式: ❌ 不支持 (连接归还后语句丢失)           │
│  配置参数: 无                                               │
│  PgBouncer 角色: 直接转发（不跟踪）                         │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│  类型 2: 协议级预处理语句 (Protocol-level prepared stmt)   │
│                                                             │
│  客户端用扩展查询协议 (Extended Query Protocol):           │
│    Parse 消息 + Bind 消息 + Execute 消息                   │
│                                                             │
│  transaction 模式: ✅ 支持 (需配 max_prepared_statements>0) │
│  配置参数: max_prepared_statements                          │
│  PgBouncer 角色: 内部跟踪+重写+透明准备                     │
└─────────────────────────────────────────────────────────────┘

|| 维度 | SQL 级 PREPARE | 协议级 prepared statement | ||------|---------------|--------------------------| || 客户端怎么发 | PREPARE stmt AS ... SQL 文本 | 驱动用扩展查询协议（Parse/Bind/Execute 消息） | || transaction 模式 | ❌ 不支持 | ✅ 支持 | || 跨连接复用 | 不能 | 能（PgBouncer 透明处理） | || 典型使用者 | 手动写 PREPARE SQL | JDBC/psycopg3/pgx 等驱动 |

9.2 max_prepared_statements 的作用与工作机制

参数说明

max_prepared_statements = 200

  • 默认值为 0（禁用）。设为 > 0 时启用协议级 prepared statement 跟踪。

  • 这是每个后端连接独立跟踪的上限，不是全局共享的。

  • 不是"每个客户端 200 个"，也不是"全局共享 200 个"

工作机制

1. 客户端用扩展协议发 Parse "SELECT * FROM users WHERE id=$1"
2. PgBouncer 内部记录查询字符串, 分配内部名称: pgb_xxx
3. PgBouncer 转发给 PG: Parse pgb_xxx "SELECT * FROM users WHERE id=$1"
4. 事务结束, 后端连接归还到池
5. 下个事务可能用不同的后端连接
6. 客户端再次执行同一预处理语句: Bind/Execute pgb_xxx
7. PgBouncer 发现当前后端连接上没有准备 pgb_xxx → 透明地先 Parse 再 Execute

关键机制：

  1. PgBouncer 自动对客户端 Prepared Statement 名称加前缀（pgb_），避免命名冲突

  2. 事务结束时，自动 DEALLOCATE 该连接上所有残留的 Prepared Statement

  3. 确保连接归还池时处于"干净"状态

关于命名前缀：PgBouncer 源码中使用小写前缀 pgb_。在 PostgreSQL 日志中显示为大写 PGBOUNCER_xxx，这是 PG 日志格式化的结果。

9.3 常见驱动兼容性

|| 驱动/语言 | 默认协议 | 是否默认用协议级 prepared statement | 注意事项 | ||-----------|----------|----------------------------------|----------| || libpq © | Simple | 否 | PQexecParams 使用 Extended Protocol | || pgx (Go) | Extended | 是（CacheStatement 模式） | 需 max_prepared_statements > 0 | || psycopg2 (Python) | Simple | 否 | psycopg2 没有协议级 prepared statement 支持 | || psycopg3 (Python) | Extended | 是 | prepare_threshold 控制何时自动 Prepare | || JDBC (Java) | Extended | 是（prepareThreshold=5） | 连接串加 prepareThreshold=0 可禁用 | || node-postgres | Extended | 是 | 默认用扩展协议 | || rust-postgres | Extended | 是 | 需 ignore_startup_parameters = extra_float_digits | || psql 命令行 | Simple | 否 | 用简单查询 |

重要：psycopg2 默认使用简单查询协议，不使用 Extended Protocol 的 prepared statement。prepare_threshold 是 psycopg3 的参数，psycopg2 没有此参数。

9.4 如何判断 pgx 是否发送 Named Prepared Statement

pgx v5 有多种 QueryExecMode：

|| 模式 | 是否创建 Named Prepared Statement | 协议 | ||------|----------------------------------|------| || QueryExecModeSimpleProtocol | ❌ | Simple Query | || QueryExecModeExec | ❌（每次 Parse unnamed） | Extended | || QueryExecModeCacheStatement | ✅ | Extended + Named Prepared | || QueryExecModeDescribeExec | ❌ | Extended | || QueryExecModeCacheDescribe | ❌（缓存 Describe） | Extended |

方法1：开启 PostgreSQL 日志（推荐）

# postgresql.conf
log_statement = all
log_min_duration_statement = 0

  • Named Statement：LOG: execute stmtcache_1: SELECT ... → ✅

  • Unnamed：LOG: execute <unnamed>: SELECT ... → Extended 但 ❌ unnamed

  • Simple Protocol：LOG: statement: SELECT ... WHERE id=10 → 无 execute 关键字

方法2：查看 pg_prepared_statements

select * from pg_prepared_statements;
-- 通过 PgBouncer transaction 模式时，必须连接到同一个 backend 才能看到

方法3：抓 PostgreSQL 协议包（最准确）

tcpdump -i eth0 port 5432 -w pg.pcap
# 用 Wireshark 分析 Parse 消息中的 Statement 字段

9.5 验证 Extended Protocol 是否生效

SELECT name, statement, prepare_time
FROM pg_prepared_statements
WHERE name LIKE 'pgb_%';
-- 看到 pgb_ 前缀 → Extended Protocol 正在工作

第十部分：Prepared Statement 排查流程

10.1 报错 “prepared statement does not exist” 的五大原因

原因 1: PgBouncer 版本 < 1.21 (功能根本不存在)     ← 最常见
原因 2: max_prepared_statements = 0 (功能被禁用)    ← 很常见（默认就是 0）
原因 3: 应用用了 SQL 级 PREPARE (而非协议级)        ← 常见
原因 4: 值确实配小了 (唯一查询数超过上限)            ← 偶发
原因 5: 客户端驱动兼容性问题                        ← 特定场景

原因 1：版本 < 1.21

max_prepared_statements 功能从 1.21.0 才引入。检查：pgbouncer -V

原因 2：max_prepared_statements = 0

默认值是 0（不是 200），即默认禁用。修复：max_prepared_statements = 200 + RELOAD;

原因 3：SQL 级 PREPARE

max_prepared_statements 只跟踪协议级预处理语句，不跟踪 SQL 级的。

解决方案：改用协议级或普通 SQL，或切换到 session pooling。

原因 4：值配小了

max_prepared_statements = 唯一查询数 × 1.5 (留余量)

原因 5：驱动兼容性问题

|| 驱动 | 问题 | 解决方案 | ||------|------|---------| || PHP/PDO | 需 PHP 8.4+ 且 libpq 17 | PDO::ATTR_EMULATE_PREPARES => true | || JDBC | prepareThreshold 间歇报错 | prepareThreshold=0 | || PgBouncer 1.22.0 | 已知 bug | 升级到 1.22.1+ |

10.2 完整排查流程

步骤 1: pgbouncer -V → 版本 < 1.21? → 升级
步骤 2: SHOW CONFIG → max_prepared_statements = 0? → 改为 200
步骤 3: 查应用用 SQL 级还是协议级 → SQL 级? → 改用协议级或普通 SQL
步骤 4: 统计唯一查询数 > 配置值? → 调大
步骤 5: 查驱动兼容性 → 不兼容? → 升级或禁用服务端 prepared
步骤 6: verbose = 2 查看详细日志

10.3 快速止血方案

方案 A：禁用服务端 prepared statement（最快）

|| 驱动 | 禁用方法 | ||------|---------| || JDBC | 连接串加 prepareThreshold=0 | || PHP PDO | PDO::ATTR_EMULATE_PREPARES => true | || psycopg3 | prepare=False | || Go pgx | prefer_simple_protocol=true |

方案 B：临时切 session 模式

pool_mode = session  # 牺牲复用率

方案 C：升级 PgBouncer + 启用跟踪（根治）

apt install --only-upgrade pgbouncer
# max_prepared_statements = 200 (在配置文件中显式设置)
systemctl restart pgbouncer

第十一部分：Go(pgx/GORM) 与 PgBouncer 的兼容方案

11.1 方案 A：稳定优先（不使用 Prepared Statement）

目标：GORM + pgx + PgBouncer transaction，不要使用 Prepared Statement

架构：GORM → database/sql → pgx → Extended Protocol (QueryExecModeExec) → PgBouncer → PostgreSQL

重要：PreferSimpleProtocol: false 并不等于 QueryExecModeExec。pgx v5 的 DefaultQueryExecMode 默认值是 QueryExecModeCacheStatement。要确保走 Exec 模式，需要显式设置 config.DefaultQueryExecMode = pgx.QueryExecModeExec。

PgBouncer 配置

pool_mode = transaction
max_prepared_statements = 0      ;; 关闭 tracking
max_client_conn = 1000
default_pool_size = 50

Go代码

package main

import (
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
}

func main() {
	dsn := "host=127.0.0.1 user=testuser password=123456 dbname=testdb port=6432"

	db, err := gorm.Open(
		postgres.New(postgres.Config{
			DSN:                   dsn,
			PreferSimpleProtocol: false,
			// 注意: 仅 PreferSimpleProtocol:false 不够,
			// 需显式创建 pgx config 并设 DefaultQueryExecMode = QueryExecModeExec
		}),
		&gorm.Config{
			PrepareStmt: false,  // GORM层关闭缓存
		},
	)
	if err != nil {
		panic(err)
	}

	db.AutoMigrate(&User{})
	user := User{Name: "Tom", Age: 20}
	db.Create(&user)
	db.Model(&User{}).Where("id=?", user.ID).Update("age", 30)

	var result User
	db.Where("id=?", user.ID).First(&result)
	fmt.Println(result)
}

11.2 方案 B：性能优先（pgx statement cache + PgBouncer tracking）

PgBouncer 配置

pool_mode = transaction
max_prepared_statements = 500
max_client_conn = 1000
default_pool_size = 50

不要设 server_reset_query_always=1。

Go代码

GORM 的 PreferSimpleProtocol 不够表达 CacheStatement，推荐显式创建 pgx config：

package main

import (
	"fmt"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
}

func main() {
	dsn := "postgres://testuser:123456@127.0.0.1:6432/testdb"

	config, err := pgx.ParseConfig(dsn)
	if err != nil {
		panic(err)
	}

	// pgx层: 显式设 CacheStatement
	config.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement

	sqlDB := stdlib.OpenDB(*config)

	db, err := gorm.Open(
		postgres.New(postgres.Config{Conn: sqlDB}),
		&gorm.Config{PrepareStmt: false},
	)
	if err != nil {
		panic(err)
	}

	db.AutoMigrate(&User{})
	user := User{Name: "Tom", Age: 20}
	db.Create(&user)
	db.Model(&User{}).Where("id=?", user.ID).Update("age", 40)

	var result User
	db.Where("id=?", user.ID).First(&result)
	fmt.Println(result)
}

11.3 两个方案对比

|| | 方案A | 方案B | ||------|------|------| || GORM PrepareStmt | false | false | || 手工 PrepareContext | ❌ 不允许 | ⚠️ 可以但增加风险 | || pgx 模式 | Exec | CacheStatement | || max_prepared_statements | 0 | 500 | || Extended Protocol | ✅ | ✅ | || Named Prepared | ❌ | ✅ | || 性能 | 中 | 高 | || 稳定性 | 最高 | 较高 |

方案A/B 的默认前提：应用不显式调用 PrepareContext，也不启用 GORM PrepareStmt。

11.4 PrepareContext 与方案 A/B 的关系

如果代码中显式使用 **PrepareContext()**，那么：

  • 方案 A：不成立——PrepareContext 会无条件向 PG 发 Parse，绕过 pgx 的 DefaultQueryExecMode

  • 方案 B：也不成立——不再是 pgx CacheStatement 的控制路径

pgx 源码机制

PrepareContext 把 statement 注册进连接级缓存 preparedStatements，后续执行时 exec() 先查缓存，命中就直接走 **execPrepared**（Extended），完全绕过 **DefaultQueryExecMode**。

唯一的"后门"：无参数的查询强制 Simple Protocol。

// conn.go:481
// Always use simple protocol when there are no arguments.
if len(arguments) == 0 {
    mode = QueryExecModeSimpleProtocol
}

GORM 的 PrepareStmt 也一样

db, _ := gorm.Open(postgres.New(...), &gorm.Config{PrepareStmt: true})

它内部等价于 conn.PrepareContext(ctx, sql)。因此对于 PgBouncer transaction，建议 **PrepareStmt: false**。

完整修正后的方案定义

|| | 方案A | 方案B | ||------|------|------| || GORM PrepareStmt | false | false | || 手工 PrepareContext | ❌ 不允许 | ⚠️ 可以但增加风险 | || pgx CacheStatement | ❌ | ✅ | || max_prepared_statements | 0 | 500 | || transaction pool | 稳定 | 性能更高 |

第十二部分：Go(pgx/GORM) 走 Simple Protocol 的实测与坑

环境：gorm.io/driver/postgres v1.6.0 + github.com/jackc/pgx/v5 v5.6.0，通过 PgBouncer 6432 端口连接 PostgreSQL 15。 结论：仅靠改配置无法做到——只要代码里显式调用了 sqlDB.PrepareContext，无论 PreferSimpleProtocol 取 true 还是 false，实际都走 Extended Protocol。

12.1 三种方案

方案 A：PreferSimpleProtocol:false + PrepareContext → 全部 execute（Extended）

postgres.New(postgres.Config{
    DSN: "postgres://test:123456@192.168.139.12:6432/test",
    PreferSimpleProtocol: false,
})
selStmt, _ := sqlDB.PrepareContext(ctx, "SELECT id, name FROM users WHERE id = $1")
selStmt.QueryRowContext(ctx, id).Scan(&u.ID, &u.Name)

方案 B：PreferSimpleProtocol:true + PrepareContext → 业务 SQL 仍为 execute（Extended），仅无参查询 statement

仅改一行 PreferSimpleProtocol: true，业务代码不动。凡是经过 **PrepareContext** 的 SQL，两种配置毫无区别，都锁死在 Extended。

方案 C：PreferSimpleProtocol:true + 去掉 PrepareContext → 全部 statement:（真正 Simple）

PreferSimpleProtocol: true,
// 不再 PrepareContext，直接执行
sqlDB.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", id).Scan(&u.ID, &u.Name)
sqlDB.ExecContext(ctx, "INSERT INTO users (name) VALUES ($1)", name)

12.2 实测 PG 日志对比

方案 A：全 execute PGBOUNCER_xxx: ...

方案 B：业务 SQL 全 execute PGBOUNCER_xxx: ...；仅无参的 QueryContext 走 statement:

方案 C：全 statement: SELECT ... WHERE id = '1'（参数内联，无 parse/bind/execute）

12.3 为什么"半简单协议"会失效

PreferSimpleProtocol 只在 SQL 不在 preparedStatements 缓存时才生效；一旦显式 PrepareContext，该 SQL 就被锁死为 Extended Protocol。源码关键路径：

// conn.go:486
if sd, ok := c.preparedStatements[sql]; ok {  // PrepareContext 已写入，必命中
    return c.execPrepared(ctx, sd, arguments)  // 直接发 Bind+Execute，mode 被忽略！
}

12.4 结论

|| 方案 | 配置 | 业务代码 | 是否真正 Simple | ||------|------|---------|----------------| || A | false + PrepareContext | 保留 | ❌ Extended | || B | true + PrepareContext | 保留 | ❌ 实际仍 Extended | || C | true + 无 PrepareContext | 改用 ExecContext | ✅ 真正 Simple |

行动建议：

  1. 若想真正走 Simple Protocol：必须去掉 PrepareContext（方案 C）

  2. 若保留 PrepareContext：PreferSimpleProtocol 是无效配置，建议设回 false

  3. Simple Protocol 在 PgBouncer transaction 模式下天然规避 prepared statement 问题

12.5 完整可复现代码

go.mod

module go-demo

go 1.26.1

require (
	gorm.io/driver/postgres v1.6.0
	gorm.io/gorm v1.31.2
)

require (
	github.com/jackc/pgx/v5 v5.6.0 // indirect
	github.com/jackc/puddle/v2 v2.2.2 // indirect
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.5 // indirect
	golang.org/x/crypto v0.31.0 // indirect
	golang.org/x/sync v0.10.0 // indirect
	golang.org/x/text v0.21.0 // indirect
)

方案 A 完整 main.go

package main

import (
	"context"
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type User struct {
	ID   int
	Name string
}

func main() {
	db, err := gorm.Open(
		postgres.New(postgres.Config{
			DSN:                   "postgres://test:123456@192.168.139.12:6432/test",
			PreferSimpleProtocol: false, // 带 PrepareContext 时 true/false 均走 Extended
		}),
		&gorm.Config{PrepareStmt: false},
	)
	if err != nil {
		panic(err)
	}

	sqlDB, _ := db.DB()
	ctx := context.Background()

	// STEP 1: PrepareContext 一次，QueryRowContext 多次
	selStmt, _ := sqlDB.PrepareContext(ctx, "SELECT id, name FROM users WHERE id = $1")
	defer selStmt.Close()

	for _, id := range []int{1, 2, 3} {

		var u User
		selStmt.QueryRowContext(ctx, id).Scan(&u.ID, &u.Name)
		fmt.Printf("  [id=%d] -> %+v\n", id, u)
	}

	// STEP 2: PrepareContext 一次，ExecContext 三次
	insStmt, _ := sqlDB.PrepareContext(ctx, "INSERT INTO users (name) VALUES ($1)")
	defer insStmt.Close()

	for _, name := range []string{"Alice_PrepStmt", "Bob_PrepStmt", "Charlie_PrepStmt"} {

		insStmt.ExecContext(ctx, name)
	}

	// STEP 3: PrepareContext 一次，ExecContext 两次
	updStmt, _ := sqlDB.PrepareContext(ctx, "UPDATE users SET name = $1 WHERE name = $2")
	defer updStmt.Close()

	for _, u := range []struct{ newName, oldName string }{

		{"Alice_Renamed", "Alice_PrepStmt"},
		{"Bob_Renamed", "Bob_PrepStmt"},
	} {
		updStmt.ExecContext(ctx, u.newName, u.oldName)
	}

	// STEP 4: PrepareContext 一次，ExecContext 三次
	delStmt, _ := sqlDB.PrepareContext(ctx, "DELETE FROM users WHERE name = $1")
	defer delStmt.Close()

	for _, name := range []string{"Alice_Renamed", "Bob_Renamed", "Charlie_PrepStmt"} {

		delStmt.ExecContext(ctx, name)
	}

	// STEP 5: 验证最终数据
	rows, _ := sqlDB.QueryContext(ctx, "SELECT id, name FROM users ORDER BY id")
	defer rows.Close()
	for rows.Next() {
		var u User
		rows.Scan(&u.ID, &u.Name)
		fmt.Printf("  id=%d  name=%s\n", u.ID, u.Name)
	}
}

方案 B

与方案 A 完全相同，仅第 20 行改为 PreferSimpleProtocol: true

方案 C 完整 main.go

package main

import (
	"context"
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type User struct {
	ID   int
	Name string
}

func main() {
	db, err := gorm.Open(
		postgres.New(postgres.Config{
			DSN:                   "postgres://test:123456@192.168.139.12:6432/test",
			PreferSimpleProtocol: true, // 去掉 PrepareContext 后此配置才真正生效
		}),
		&gorm.Config{PrepareStmt: false},
	)
	if err != nil {
		panic(err)
	}

	sqlDB, _ := db.DB()
	ctx := context.Background()

	// 不再 PrepareContext，直接 ExecContext/QueryRowContext

	for _, id := range []int{1, 2, 3} {

		var u User
		sqlDB.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", id).Scan(&u.ID, &u.Name)
		fmt.Printf("  [id=%d] -> %+v\n", id, u)
	}


	for _, name := range []string{"Alice_PrepStmt", "Bob_PrepStmt", "Charlie_PrepStmt"} {

		sqlDB.ExecContext(ctx, "INSERT INTO users (name) VALUES ($1)", name)
	}


	for _, u := range []struct{ newName, oldName string }{

		{"Alice_Renamed", "Alice_PrepStmt"},
		{"Bob_Renamed", "Bob_PrepStmt"},
	} {
		sqlDB.ExecContext(ctx, "UPDATE users SET name = $1 WHERE name = $2", u.newName, u.oldName)
	}


	for _, name := range []string{"Alice_Renamed", "Bob_Renamed", "Charlie_PrepStmt"} {

		sqlDB.ExecContext(ctx, "DELETE FROM users WHERE name = $1", name)
	}

	rows, _ := sqlDB.QueryContext(ctx, "SELECT id, name FROM users ORDER BY id")
	defer rows.Close()
	for rows.Next() {
		var u User
		rows.Scan(&u.ID, &u.Name)
		fmt.Printf("  id=%d  name=%s\n", u.ID, u.Name)
	}
}

编译运行命令

# 交叉编译
GOOS=linux GOARCH=arm64 CGO_ENABLED=0 go build -o demo-extended .   # 方案A
GOOS=linux GOARCH=arm64 CGO_ENABLED=0 go build -o demo-semisimple . # 方案B
GOOS=linux GOARCH=arm64 CGO_ENABLED=0 go build -o demo-simple .     # 方案C

# 抓 PG 日志
ssh root@192.168.139.12 "wc -c < /postgresql/pg15.18/data/log/postgresql-2026-07-19_000000.log > /tmp/logsize.txt"
ssh root@192.168.139.12 "timeout 60 /tmp/demo-extended"
ssh root@192.168.139.12 "LOG=/postgresql/pg15.18/data/log/postgresql-2026-07-19_000000.log; SIZE=\$(cat /tmp/logsize.txt); tail -c +\$((SIZE+1)) \$LOG | grep -E 'LOG:  (parse |bind |execute |statement:)'"

第十三部分：运维与监控

13.1 启动与停止

pgbouncer -d /etc/pgbouncer/pgbouncer.ini    # 启动（后台）
pgbouncer -v /etc/pgbouncer/pgbouncer.ini     # 前台启动（调试）
kill -HUP $(cat /var/run/postgresql/pgbouncer.pid)  # 热重载配置
kill -TERM $(cat /var/run/postgresql/pgbouncer.pid) # 安全停止
kill -QUIT $(cat /var/run/postgresql/pgbouncer.pid) # 立即停止

13.2 管理控制台

psql -p 6432 -U dbadmin pgbouncer

13.3 核心监控命令

SHOW POOLS;    -- ★ 最常用: cl_active, cl_waiting, sv_active, sv_idle, maxwait
SHOW STATS;    -- avg_query_time, avg_wait_time, total_query_count
SHOW TOTALS;   -- 汇总统计
SHOW SERVERS;  -- 后端连接详情
SHOW CLIENTS;  -- 客户端连接详情
SHOW DATABASES;
SHOW CONFIG;
SHOW LISTS;
SHOW MEM;
SHOW STATE;

13.4 SHOW POOLS 关键指标解读

|| 指标 | 健康值 | 异常信号 | ||------|--------|---------| || cl_waiting | 0 | > 0 说明池不够用 | || maxwait | 0 | 持续增长说明严重不足 | || sv_idle | ≤ pool_size | 长期等于 pool_size 说明池过大 | || sv_active | < pool_size | 长期等于 pool_size 说明池过小 |

13.5 常见运维场景

数据库重启

PAUSE db_main;     -- 暂停
-- PG 侧: systemctl restart postgresql
RESUME db_main;    -- 恢复

修改配置后生效

RELOAD;
WAIT_CLOSE db_main;  -- 如修改了 pool_size 等参数

PG 故障转移

RECONNECT db_main;   -- 渐进式
WAIT_CLOSE db_main;
-- 或紧急: KILL db_main; → 修改配置 → RELOAD; → RESUME db_main;

13.6 信号速查

|| 信号 | 等价命令 | 说明 | ||------|---------|------| || SIGHUP | RELOAD | 重新加载配置 | || SIGTERM | SHUTDOWN WAIT_FOR_CLIENTS | 等待客户端断开后关闭 | || SIGINT | SHUTDOWN WAIT_FOR_SERVERS | 等待后端连接释放后关闭 | || SIGQUIT | SHUTDOWN | 立即关闭 | || SIGUSR1 | PAUSE | 暂停 | || SIGUSR2 | RESUME | 恢复 |

第十四部分：生产最佳实践

14.1 安全最佳实践

auth_type = scram-sha-256           # 使用 SCRAM 认证
user = pgbouncer                     # 非 root 用户运行
admin_users = dbadmin               # 管理权限分离
stats_users = monitor
listen_addr = 10.0.0.5              # 只监听内网
# client_tls_sslmode = require      # 跨网络时启用 TLS

14.2 性能调优清单

idle_transaction_timeout = 60       # 防漏 COMMIT 卡连接
query_wait_timeout = 60             # 防客户端无限排队
max_prepared_statements = 200       # transaction 模式 (1.21+)
server_lifetime = 3600              # 定期重建连接

14.3 监控告警

-- 告警 1: cl_waiting > 0 持续 → 池不够
-- 告警 2: cur_client / max_client_conn > 80% → 前端接近上限
-- 告警 3: pg_stat_activity count / max_connections > 80% → 后端接近上限
-- 告警 4: idle in transaction 过多 → 应用漏 COMMIT

14.4 常见踩坑清单

|| 坑 | 症状 | 解决 | ||----|------|------| || max_client_conn ≈ max_connections | 复用价值丧失 | 前者远大于后者 | || Σ pool_size > max_connections | 部分池拿不到连接 | 前者 ≤ 后者 × 0.8 | || 应用连接池 > PgBouncer pool_size | 延迟尖峰 | 应用侧 ≤ PgBouncer | || transaction 模式用临时表 | 报错或数据丢失 | 改 session 或改造应用 | || 忘记设 max_db_connections | 单库吃光 PG 连接 | 锁死单库上限 | || ulimit -n 太小 | 大量连接时报错 | 调到 65535 | || server_reset_query_always=1 | 破坏应用状态 | 保持 0 | || DDL 后预处理语句报错 | cached plan must not change result type | 执行 RECONNECT | || 忘记留给 superuser 的连接 | DBA 连不上 | superuser_reserved_connections=5 | || PrepareContext + PreferSimpleProtocol:true | 仍走 Extended Protocol | 必须去掉 PrepareContext |

第十五部分：核心配置速查表

|| 参数 | 默认值 | 生产推荐 | 说明 | ||------|--------|---------|------| || pool_mode | session | transaction | 池化模式 | || max_client_conn | 100 | 1000~5000 | 前端连接上限 | || default_pool_size | 20 | 2~5 | 常态每池上限 | || max_db_connections | 0（无限） | 峰值 | 单库硬上限 | || reserve_pool_size | 0 | 1~3 | 突发缓冲 | || reserve_pool_timeout | 5 | 3 | 动用 reserve 的等待 | || server_idle_timeout | 600 | 600 | 空闲回收 | || server_lifetime | 3600 | 3600 | 连接最大生命周期 | || query_wait_timeout | 120 | 60 | 客户端排队超时 | || idle_transaction_timeout | 0 | 60 | 事务空闲超时 | || max_prepared_statements | 0 | 200 | 预处理语句跟踪（1.21+，默认禁用） | || auth_type | md5 | scram-sha-256 | 认证方式 | || server_reset_query | DISCARD ALL | DISCARD ALL | session 模式清理 |

一句话总结

PgBouncer 的核心价值是"压缩后端、释放前端"：用少量昂贵的 PG 后端连接，支撑大量便宜的客户端连接。生产环境首选 transaction pooling 模式，配合 default_pool_size（管常态）、max_db_connections（锁峰值）、idle_transaction_timeout（防漏 COMMIT）、max_prepared_statements（支持协议级预处理语句，默认为 0 需显式开启），并确保 Σ pool_size ≤ PG max_connections × 0.8。用 SHOW POOLS 的 **maxwait** 和 **cl_waiting** 做调优依据，而非拍脑袋。

墨力计划postgresql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论