PostgreSQL连接数过多报错:too many clients already的解决办法

2024-04-18 0 782
目录
  • 一、问题描述
  • 二、分析问题
    • 1.数据库层面
      • 1.1 查看连接数和连接状态
      • 1.2 查看连接超时时间配置
    • 2. 程序方面
    • 三、解决问题的步骤
      • 四、查询数据库连接数量
        • 1. 查询数据库可用连接
          • 2. 查询数据库连接状态
            • 3. 分组查询连接数量
              • 4. 根据PID关闭连接
                • 5. 空闲连接超时时间
                • 五、优化程序连接池配置

                  一、问题描述

                  在使用 Navicat 连接 PostgreSQL 数据库时,突然遭遇到了一个报错:“FATAL: sorry, too many clients already”。这一错误提示表明数据库连接数已经达到上限,无法再创建新连接。为了解决这一问题,我采取了一系列查询和配置调整的步骤,从数据库和程序连接池两个方面入手。

                  数据库版本和程序信息:

                  • 数据库版本:PostgreSQL 11.5

                  查看PostgreSQL 版本
                  SELECT version();

                  • 程序语言和框架:Java 和 Spring Boot

                  二、分析问题

                  1.数据库层面

                  1.1 查看连接数和连接状态

                  通过查询数据库连接数量和连接状态,了解数据库中活跃连接的数量以及每个连接的详细信息,包括数据库名称、进程 ID(PID)、应用程序名称和连接状态。

                  1.2 查看连接超时时间配置

                  查看数据库中空闲连接的超时时间配置,确保连接池设置合理。

                  2. 程序方面

                  通过查看程序中的数据库连接池配置,特别是对于使用 Hikari 连接池的情况,确认连接池的设置是否合理,以及是否需要调整连接池的参数。

                  三、解决问题的步骤

                • 查询数据库连接数量和连接状态:通过执行相关SQL查询,了解数据库的连接状况。

                • 查询连接超时时间配置:通过 SQL 查询,查看数据库中空闲连接的超时时间配置。

                • 确认程序连接池配置:检查程序中使用的连接池配置,特别是 Hikari 连接池的参数,调整配置以确保连接池有效管理连接。

                • 修改空闲连接超时时间(如有需要):如果需要,通过 SQL 命令修改数据库中空闲连接的超时时间。

                • 四、查询数据库连接数量

                  1. 查询数据库可用连接

                  首先,我使用以下 SQL 查询语句查看当前数据库的可用连接数与实际连接数的差异:

                  SELECT max_conn – now_conn AS resi_conn
                  FROM (
                  SELECT setting::int8 AS max_conn, (SELECT count(*) FROM pg_stat_activity) AS now_conn
                  FROM pg_settings WHERE name = \’max_connections\’
                  ) t;

                  这有助于了解数据库的连接状况,以及是否需要调整连接数上限。

                  2. 查询数据库连接状态

                  为了更详细地了解当前连接状态,我执行了以下查询,显示了每个连接的数据库名称、进程 ID(PID)、应用程序名称和连接状态:

                  SELECT datname, pid, application_name, state
                  FROM pg_stat_activity;

                  这提供了连接池中活跃连接的详细信息,有助于定位可能导致连接数过多的问题。

                  3. 分组查询连接数量

                  通过以下查询,我统计了每个数据库的连接数,这有助于发现是否有特定数据库占用了过多的连接:

                  SELECT datname, count(0)
                  FROM pg_stat_activity
                  GROUP BY datname;

                  4. 根据PID关闭连接

                  –根据PID关闭连接
                  select pg_terminate_backend(pid) from pg_stat_activity;

                  5. 空闲连接超时时间

                  修改空闲连接超时时间(如有需要): 如果需要,通过 SQL 命令修改数据库中空闲连接的超时时间。

                  — 设置控制在事务中处于空闲状态的会话的超时时间
                  ALTER SYSTEM SET idle_in_transaction_session_timeout = 30000;

                  — 查询控制在事务中处于空闲状态的会话的超时时间
                  SHOW idle_in_transaction_session_timeout;

                  — 设置控制空闲会话的超时时间
                  ALTER SYSTEM SET idle_session_timeout = \’300s\’;

                  — 查询空闲会话超时时间
                  SHOW idle_session_timeout;

                  五、优化程序连接池配置

                  检查程序中使用的连接池配置,特别是对于使用 Hikari 连接池的情况。以下是一些建议的配置项:

                  hikari.maximum-pool-size=10
                  hikari.connection-timeout=30000
                  hikari.minimum-idle=5
                  hikari.max-lifetime=1800000
                  hikari.idle-timeout=600000

                  参数配置详解:

                  • hikari.maximum-pool-size: 设置连接池允许的最大连接数。建议根据实际需求适度增加或减少,确保足够但不过多。
                  • hikari.connection-timeout: 连接超时时间,定义连接在空闲状态多久后被释放,以确保连接资源的有效利用。
                  • hikari.minimum-idle: 最小空闲连接数,确保连接池始终维持一定数量的活跃连接,降低连接的创建和销毁开销。
                  • hikari.max-lifetime: 连接生命周期,定义连接在被释放前可以存在的最长时间,防止连接长时间积累导致资源浪费。
                  • hikari.idle-timeout: 空闲连接超时时间,连接在池中空闲的最长时间,超过这个时间将被释放。

                  这些参数可以根据实际需求进行调整,确保连接池能够更好地管理和释放连接。

                  通过以上步骤,我成功解决了连接数过多的报错问题,并优化了数据库连接管理。希望这些详细的思路和步骤对您在类似问题的解决中有所帮助。

                  以上就是PostgreSQL连接数过多报错:too many clients already的解决办法的详细内容,更多关于PostgreSQL连接数过多报错的资料请关注悠久资源其它相关文章!

                  收藏 (0) 打赏

                  感谢您的支持,我会继续努力的!

                  打开微信/支付宝扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
                  点赞 (0)

                  悠久资源 PostgreSQL PostgreSQL连接数过多报错:too many clients already的解决办法 https://www.u-9.cn/database/postgresql/186576.html

                  常见问题

                  相关文章

                  发表评论
                  暂无评论
                  官方客服团队

                  为您解决烦忧 - 24小时在线 专业服务