PgBouncer的auth_user占PostgreSQL三成连接致应用连接不足求助

问题分析 #

1. pgbouncer用户连接权限不足：pgbouncer用户没有查询pg_stat_activity的权限，导致无法显示连接的后端启动时间、查询时间等详细信息，该问题不影响连接池核心功能，但会阻碍排查过程。
2. userA空闲连接堆积（230个）：结合配置（pool_mode=transaction、default_pool_size=30、40个数据库），理论上单库最大后端连接数为30，总后端连接数可达1200，但当前230个空闲连接已占用大量资源，直接导致应用无法获取新连接。

解决步骤 #

一、解决pgbouncer用户权限问题（可选，用于排查） #

给pgbouncer用户授予查看连接状态的权限，执行以下SQL：

-- 授予监控角色，包含pg_stat_activity的查询权限
GRANT pg_monitor TO pgbouncer;

-- 或更细粒度授权
GRANT SELECT ON pg_stat_activity TO pgbouncer;

二、排查并解决连接堆积问题 #

1. 定位连接瓶颈

通过PgBouncer管理控制台查看连接池状态：

# 连接到PgBouncer admin库
psql -p 1234 -U pgbouncer pgbouncer

执行命令查看各数据库的连接池详情：

show pools;

重点关注cl_active（活跃客户端连接）、cl_waiting（等待的客户端连接）、sv_active（活跃后端连接）、sv_idle（空闲后端连接）字段，锁定连接堆积的数据库。

同时在PostgreSQL中查看userA的连接状态：

SELECT pid, datname, state, query_start, wait_event_type, wait_event
FROM pg_stat_activity
WHERE usename = 'userA';

若大量连接处于idle in transaction状态，说明应用未及时提交/回滚事务，导致连接被持续占用。

2. 优化应用连接与事务管理

• 检查应用代码，确保连接使用后及时关闭，避免连接泄漏；
• 确保事务执行完成后立即提交或回滚，异常分支中不要遗漏事务结束操作。

3. 调整PgBouncer配置

修改pgbouncer.ini，添加或调整以下参数：

[pgbouncer]
# 自动关闭超过5分钟的空闲后端连接
idle_timeout = 300
# 限制客户端连接的空闲时间（可选）
client_idle_timeout = 600

重启PgBouncer生效：

pg_ctl -D /path/to/pgbouncer restart

4. 优化PostgreSQL配置

在postgresql.conf中添加：

# 自动终止超过5分钟的空闲事务
idle_transaction_timeout = 300000

重启PostgreSQL生效。

5. 调整连接池大小参数

根据实际应用并发需求调整default_pool_size，若应用并非同时访问所有40个数据库，可适当降低该值（例如从30改为10），避免后端连接过度占用：

[pgbouncer]
default_pool_size = 10

三、验证效果 #

1. 重启PgBouncer和PostgreSQL后，观察pg_stat_activity中的空闲连接数是否下降；
2. 通过PgBouncer admin控制台的show pools;查看等待连接数是否减少；
3. 验证应用是否能正常获取连接。

内容的提问来源于stack exchange，提问作者tad