PgBouncer 连接池优化教程

PgBouncer 是 PostgreSQL 最流行的轻量级连接池中间件。PostgreSQL 为每个客户端连接创建一个独立的后端进程，当并发连接数较多时会消耗大量内存和系统资源。PgBouncer 通过复用数据库连接，可以让数百甚至数千个客户端连接共享少量的 PostgreSQL 后端连接，显著降低资源消耗。在搬瓦工 VPS 内存有限的环境下，PgBouncer 是必不可少的优化组件。更多 PostgreSQL 教程可参考流复制配置。

一、为什么需要连接池

PostgreSQL 连接开销：每个连接对应一个后端进程，约占 5-10MB 内存。100 个连接就是 500MB-1GB。
连接创建延迟：新建连接需要认证、初始化，耗时数十毫秒。
搬瓦工 VPS 限制：1GB 内存方案最多支持 50-100 个 PostgreSQL 直连。使用 PgBouncer 后可以支持数千个应用连接。

二、安装 PgBouncer

apt update
apt install pgbouncer -y
pgbouncer --version

三、配置 PgBouncer

编辑 /etc/pgbouncer/pgbouncer.ini：

[databases]
; 数据库映射
myapp = host=127.0.0.1 port=5432 dbname=myapp
; 通配符（所有数据库）
* = host=127.0.0.1 port=5432

[pgbouncer]
; 监听配置
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; 池化模式
pool_mode = transaction

; 连接池大小
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; 连接限制
max_client_conn = 1000
max_db_connections = 50
max_user_connections = 50

; 超时设置
server_idle_timeout = 300
client_idle_timeout = 0
client_login_timeout = 60
query_timeout = 0
query_wait_timeout = 120

; 日志
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

; 管理
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

3.1 配置认证文件

# 从 PostgreSQL 获取用户密码哈希
su - postgres
psql -c "SELECT '\"' || usename || '\" \"' || passwd || '\"' FROM pg_shadow WHERE usename IN ('myapp_user', 'pgbouncer_admin');" -t -A

# 将输出写入 /etc/pgbouncer/userlist.txt
# 格式为: "username" "password_hash"

或使用 auth_query 方式自动查询（推荐生产环境）：

# 在 pgbouncer.ini 中设置
auth_type = scram-sha-256
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

3.2 启动 PgBouncer

chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
chmod 640 /etc/pgbouncer/userlist.txt
systemctl start pgbouncer
systemctl enable pgbouncer

# 验证连接（通过 PgBouncer 端口 6432）
psql -h 127.0.0.1 -p 6432 -U myapp_user -d myapp

四、池化模式详解

PgBouncer 提供三种池化模式，选择哪种取决于你的应用特点：

4.1 Session 模式

pool_mode = session

客户端获取连接后独占直到断开。最安全但复用效果最差。适合使用了 PREPARE、临时表、SET 等会话级特性的应用。

4.2 Transaction 模式（推荐）

pool_mode = transaction

客户端在事务期间独占连接，事务结束后连接归还池。复用效果好，是大多数 Web 应用的最佳选择。注意：不能使用 PREPARE 语句和会话级变量。

4.3 Statement 模式

pool_mode = statement

每条 SQL 语句执行后连接立即归还。复用效果最好但限制最多——不支持事务（每条语句自动提交）。仅适合简单的只读查询场景。

五、监控 PgBouncer

# 连接管理数据库
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

# 查看连接池状态
SHOW POOLS;

# 查看活跃的客户端连接
SHOW CLIENTS;

# 查看活跃的服务端连接
SHOW SERVERS;

# 查看统计信息
SHOW STATS;

# 查看详细统计
SHOW STATS_TOTALS;

# 查看配置
SHOW CONFIG;

关键监控指标：

cl_active：当前活跃的客户端连接数。
cl_waiting：等待可用服务端连接的客户端数。如果持续大于 0，说明连接池不够大。
sv_active：当前正在使用的服务端连接数。
sv_idle：空闲的服务端连接数。
avg_query：平均查询时间（微秒）。

六、按搬瓦工方案推荐参数

# 搬瓦工 1GB 内存方案
default_pool_size = 10
min_pool_size = 2
reserve_pool_size = 3
max_client_conn = 500
max_db_connections = 20

# 搬瓦工 2GB 内存方案
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
max_client_conn = 1000
max_db_connections = 50

# 搬瓦工 4GB+ 内存方案
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 10
max_client_conn = 2000
max_db_connections = 100

七、高可用配置

PgBouncer 配合 HAProxy 可以实现读写分离和负载均衡：

# /etc/haproxy/haproxy.cfg 示例
frontend pg_write
    bind *:5433
    default_backend pg_primary

frontend pg_read
    bind *:5434
    default_backend pg_replicas

backend pg_primary
    option pgsql-check user haproxy
    server primary 192.168.1.10:6432 check

backend pg_replicas
    balance roundrobin
    option pgsql-check user haproxy
    server replica1 192.168.1.20:6432 check
    server replica2 192.168.1.30:6432 check

这种架构下，应用写操作连接 5433 端口（路由到主库的 PgBouncer），读操作连接 5434 端口（轮询到从库的 PgBouncer）。

八、应用端配置示例

8.1 Python（SQLAlchemy）

from sqlalchemy import create_engine

# 连接 PgBouncer 而不是直接连 PostgreSQL
engine = create_engine(
    "postgresql://myapp_user:password@localhost:6432/myapp",
    pool_size=5,         # 应用层连接池可以设小
    max_overflow=10,
    pool_pre_ping=True   # 检测连接是否有效
)

8.2 Node.js（pg 库）

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 6432,           // PgBouncer 端口
  database: 'myapp',
  user: 'myapp_user',
  password: 'password',
  max: 10               // 应用层池大小可以设小
});

九、在线管理操作

# 在线重新加载配置（不断连接）
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "RELOAD;"

# 暂停所有数据库（维护时使用）
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "PAUSE;"

# 恢复
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "RESUME;"

# 禁用数据库（拒绝新连接）
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "DISABLE myapp;"

# 重新启用
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "ENABLE myapp;"

十、常见问题

PREPARE 语句不可用：在 transaction 模式下不支持。Django 需要设置 DISABLE_SERVER_SIDE_CURSORS = True。
连接等待超时：增大 default_pool_size 或 max_db_connections，或优化查询减少连接占用时间。
认证失败：确保 userlist.txt 中的密码格式正确，auth_type 匹配 PostgreSQL 配置。
SET 命令不生效：transaction 模式下，事务外的 SET 命令会丢失。使用 SET LOCAL 在事务内设置。

十一、替代方案对比

PgBouncer：最轻量，单进程单线程，内存占用极低（几 MB），功能专注于连接池。
Pgpool-II：功能更多（负载均衡、复制），但更重更复杂。
Odyssey：Yandex 开源，多线程架构，高并发性能更好。
应用层连接池：如 HikariCP（Java）、SQLAlchemy Pool（Python），只解决单应用问题。

总结

PgBouncer 是搬瓦工 VPS 上运行 PostgreSQL 的必备优化组件。通过连接池复用，可以在有限内存下支持大量并发连接，显著提升数据库的承载能力。推荐使用 transaction 模式，配合合理的池大小参数即可。更多 PostgreSQL 教程可参考流复制配置、JSON 查询和全文搜索。选购搬瓦工 VPS 请查看全部方案，使用优惠码 NODESEEK2026 享受 6.77% 折扣，通过 bwh81.net 进入官网。