解决 PostgreSQL 连接数过多报错的情景
一、问题描述
在使用 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: 空闲连接超时时间，连接在池中空闲的最长时间，超过这个时间将被释放。
这些参数可以根据实际需求进行调整，确保连接池能够更好地管理和释放连接。文章来源：https://www.toymoban.com/news/detail-833345.html
通过以上步骤，我成功解决了连接数过多的报错问题，并优化了数据库连接管理。希望这些详细的思路和步骤对您在类似问题的解决中有所帮助。文章来源地址https://www.toymoban.com/news/detail-833345.html
到了这里，关于PostgreSQL 连接数过多报错(too many clients already)的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
