PostgreSQL 连接数过多报错(too many clients already)

解决 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 连接池的情况，确认连接池的设置是否合理，以及是否需要调整连接池的参数。

三、解决问题的步骤

1. 查询数据库连接数量和连接状态： 通过执行相关 SQL 查询，了解数据库的连接状况。

2. 查询连接超时时间配置： 通过 SQL 查询，查看数据库中空闲连接的超时时间配置。

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

4. 修改空闲连接超时时间（如有需要）： 如果需要，通过 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: 空闲连接超时时间，连接在池中空闲的最长时间，超过这个时间将被释放。

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

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