PostgreSQL 连接数超限问题

以下是完整的 PostgreSQL 连接数超限问题解决方案文档：

PostgreSQL 连接数超限问题解决方案

一、错误原因分析

错误 FATAL: remaining connection slots are reserved for non-replication superuser connections 表示：

• PostgreSQL 数据库的连接数已达到 max_connections 限制。
• 剩余连接槽位仅保留给非复制超级用户（如 postgres），普通用户（如 idc）无法建立新连接。

关键机制：

• max_connections：数据库允许的最大连接数（默认值通常为 100）。
• superuser_reserved_connections：为超级用户预留的连接数（默认值为 3）。
• 当普通用户连接数达到 max_connections - superuser_reserved_connections 时，仅超级用户可建立新连接。

二、查看连接数与配置

1. 查看最大连接数：

   SHOW max_connections; -- 输出当前配置的最大连接数
   

2. 查看当前连接数：

   SELECT COUNT(*) FROM pg_stat_activity; -- 统计所有活跃连接数
   

3. 按数据库/用户/客户端地址分组统计：

   SELECT 
     datname AS "数据库",
     usename AS "用户",
     client_addr AS "客户端地址",
     COUNT(*) AS "连接数"
   FROM pg_stat_activity
   GROUP BY datname, usename, client_addr
   ORDER BY COUNT(*) DESC;
   

4. 查看特定用户的查询会话：

   SELECT datname, usename, query
   FROM pg_stat_activity
   WHERE usename = '目标用户名'; -- 替换为实际用户名
   

三、排查连接泄漏（应用侧问题）

1. 检查代码逻辑：

   • 确保数据库连接在使用后正确关闭（如 Java 中使用 finally 块）：

     Connection conn = null;
     try {
         conn = DriverManager.getConnection(url, username, password);
         // 执行数据库操作
     } catch (SQLException e) {
         // 异常处理
     } finally {
         if (conn != null && !conn.isClosed()) {
             conn.close(); // 确保连接关闭
         }
     }
     

2. 引入连接池：

   • 使用连接池（如 HikariCP、PGBouncer）管理连接，避免无限制创建新连接：

     <!-- HikariCP 配置示例 -->
     <dataSource>
         <jdbcUrl>jdbc:postgresql://localhost:5432/mydb</jdbcUrl>
         <username>idc</username>
         <password>password</password>
         <maximumPoolSize>50</maximumPoolSize> <!-- 限制最大连接数 -->
     </dataSource>
     

四、服务侧配置调整

1. 调整最大连接数

步骤：

1. 编辑配置文件：

   vi /etc/postgresql/版本号/main/postgresql.conf
   

   • 找到 max_connections = 100，修改为：

     max_connections = 200  # 根据服务器内存调整（每连接约消耗 10MB+ 内存）
     

2. 重启数据库：

   sudo systemctl restart postgresql
   

注意事项：

• 需根据服务器内存调整，公式参考：max_connections = 总内存 / 每连接内存占用（如 8GB 内存建议不超过 500）。
• 重启会中断所有连接，建议在低峰期操作。

2. 释放无效连接（谨慎操作）

超级用户权限执行：

-- 终止所有非活跃连接（谨慎操作，避免影响业务）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND pid != pg_backend_pid();

3. 使用连接池工具（推荐）

推荐工具：

• PGBouncer：轻量级连接池，适合高并发场景。

  # 安装（Ubuntu 示例）
  sudo apt-get install pgbouncer
  

  • 配置文件：

    [databases]
    mydb = host=localhost port=5432 dbname=mydb user=idc password=password
    
    [pgbouncer]
    listen_port = 6432
    max_client_conn = 100  # 客户端最大连接数
    pool_size = 20         # 数据库后端连接池大小
    

  • 启动服务：

    sudo systemctl start pgbouncer
    

五、长期优化建议

1. 监控连接数：

   -- 定期执行以下查询，监控连接数趋势
   SELECT 
     datname,
     COUNT(*) AS "连接数",
     NOW() AS "时间"
   FROM pg_stat_activity
   GROUP BY datname
   ORDER BY COUNT(*) DESC;
   

2. 限制用户连接数：

   -- 为特定用户设置连接上限
   ALTER ROLE idc WITH CONNECTION LIMIT 50;
   

3. 业务逻辑优化：
   • 减少长事务，避免事务阻塞连接。
   • 使用异步查询或批量操作减少连接占用。

通过以上步骤，可有效解决 PostgreSQL 连接数超限问题，并建立长期监控机制防止复发。