一、問題描述

在使用 Navicat 連接 PostgreSQL 數(shù)據(jù)庫時，突然遭遇到了一個報錯：“FATAL: sorry, too many clients already”。這一錯誤提示表明數(shù)據(jù)庫連接數(shù)已經(jīng)達(dá)到上限，無法再創(chuàng)建新連接。為了解決這一問題，我采取了一系列查詢和配置調(diào)整的步驟，從數(shù)據(jù)庫和程序連接池兩個方面入手。

數(shù)據(jù)庫版本和程序信息：

• 數(shù)據(jù)庫版本：PostgreSQL 11.5

查看PostgreSQL 版本
SELECT version();

• 程序語言和框架：Java 和 Spring Boot

二、分析問題

1. 數(shù)據(jù)庫層面

1.1 查看連接數(shù)和連接狀態(tài)

通過查詢數(shù)據(jù)庫連接數(shù)量和連接狀態(tài)，了解數(shù)據(jù)庫中活躍連接的數(shù)量以及每個連接的詳細(xì)信息，包括數(shù)據(jù)庫名稱、進程 ID（PID）、應(yīng)用程序名稱和連接狀態(tài)。

1.2 查看連接超時時間配置

查看數(shù)據(jù)庫中空閑連接的超時時間配置，確保連接池設(shè)置合理。

2. 程序方面

通過查看程序中的數(shù)據(jù)庫連接池配置，特別是對于使用 Hikari 連接池的情況，確認(rèn)連接池的設(shè)置是否合理，以及是否需要調(diào)整連接池的參數(shù)。

三、解決問題的步驟

1. 查詢數(shù)據(jù)庫連接數(shù)量和連接狀態(tài)： 通過執(zhí)行相關(guān) SQL 查詢，了解數(shù)據(jù)庫的連接狀況。

2. 查詢連接超時時間配置： 通過 SQL 查詢，查看數(shù)據(jù)庫中空閑連接的超時時間配置。

3. 確認(rèn)程序連接池配置： 檢查程序中使用的連接池配置，特別是 Hikari 連接池的參數(shù)，調(diào)整配置以確保連接池有效管理連接。

4. 修改空閑連接超時時間（如有需要）： 如果需要，通過 SQL 命令修改數(shù)據(jù)庫中空閑連接的超時時間。

四、查詢數(shù)據(jù)庫連接數(shù)量

1. 查詢數(shù)據(jù)庫可用連接

首先，我使用以下 SQL 查詢語句查看當(dāng)前數(shù)據(jù)庫的可用連接數(shù)與實際連接數(shù)的差異：

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;

這有助于了解數(shù)據(jù)庫的連接狀況，以及是否需要調(diào)整連接數(shù)上限。

2. 查詢數(shù)據(jù)庫連接狀態(tài)

為了更詳細(xì)地了解當(dāng)前連接狀態(tài)，我執(zhí)行了以下查詢，顯示了每個連接的數(shù)據(jù)庫名稱、進程 ID（PID）、應(yīng)用程序名稱和連接狀態(tài)：

SELECT datname, pid, application_name, state
FROM pg_stat_activity;

這提供了連接池中活躍連接的詳細(xì)信息，有助于定位可能導(dǎo)致連接數(shù)過多的問題。

3. 分組查詢連接數(shù)量

通過以下查詢，我統(tǒng)計了每個數(shù)據(jù)庫的連接數(shù)，這有助于發(fā)現(xiàn)是否有特定數(shù)據(jù)庫占用了過多的連接：

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

4. 根據(jù)PID關(guān)閉連接

--根據(jù)PID關(guān)閉連接
select pg_terminate_backend(pid) from pg_stat_activity;

5. 空閑連接超時時間

修改空閑連接超時時間（如有需要）： 如果需要，通過 SQL 命令修改數(shù)據(jù)庫中空閑連接的超時時間。

-- 設(shè)置控制在事務(wù)中處于空閑狀態(tài)的會話的超時時間
ALTER SYSTEM SET idle_in_transaction_session_timeout = 30000;

-- 查詢控制在事務(wù)中處于空閑狀態(tài)的會話的超時時間
SHOW idle_in_transaction_session_timeout;

-- 設(shè)置控制空閑會話的超時時間
ALTER SYSTEM SET idle_session_timeout = '300s';

-- 查詢空閑會話超時時間
SHOW idle_session_timeout;

五、優(yōu)化程序連接池配置

檢查程序中使用的連接池配置，特別是對于使用 Hikari 連接池的情況。以下是一些建議的配置項：

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

參數(shù)配置詳解:

• hikari.maximum-pool-size: 設(shè)置連接池允許的最大連接數(shù)。建議根據(jù)實際需求適度增加或減少，確保足夠但不過多。
• hikari.connection-timeout: 連接超時時間，定義連接在空閑狀態(tài)多久后被釋放，以確保連接資源的有效利用。
• hikari.minimum-idle: 最小空閑連接數(shù)，確保連接池始終維持一定數(shù)量的活躍連接，降低連接的創(chuàng)建和銷毀開銷。
• hikari.max-lifetime: 連接生命周期，定義連接在被釋放前可以存在的最長時間，防止連接長時間積累導(dǎo)致資源浪費。
• hikari.idle-timeout: 空閑連接超時時間，連接在池中空閑的最長時間，超過這個時間將被釋放。

這些參數(shù)可以根據(jù)實際需求進行調(diào)整，確保連接池能夠更好地管理和釋放連接。

通過以上步驟，我成功解決了連接數(shù)過多的報錯問題，并優(yōu)化了數(shù)據(jù)庫連接管理。希望這些詳細(xì)的思路和步驟對您在類似問題的解決中有所幫助。

以上就是PostgreSQL連接數(shù)過多報錯：too many clients already的解決辦法的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL連接數(shù)過多報錯的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論