解決 PostgreSQL 連接數(shù)過多報錯的情景
一、問題描述
在使用 Navicat 連接 PostgreSQL 數(shù)據(jù)庫時，突然遭遇到了一個報錯：“FATAL: sorry, too many clients already”。這一錯誤提示表明數(shù)據(jù)庫連接數(shù)已經(jīng)達到上限，無法再創(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ù)量以及每個連接的詳細信息，包括數(shù)據(jù)庫名稱、進程 ID（PID）、應(yīng)用程序名稱和連接狀態(tài)。
1.2 查看連接超時時間配置
查看數(shù)據(jù)庫中空閑連接的超時時間配置，確保連接池設(shè)置合理。
2. 程序方面
通過查看程序中的數(shù)據(jù)庫連接池配置，特別是對于使用 Hikari 連接池的情況，確認連接池的設(shè)置是否合理，以及是否需要調(diào)整連接池的參數(shù)。
三、解決問題的步驟
-
查詢數(shù)據(jù)庫連接數(shù)量和連接狀態(tài)： 通過執(zhí)行相關(guān) SQL 查詢，了解數(shù)據(jù)庫的連接狀況。
-
查詢連接超時時間配置： 通過 SQL 查詢，查看數(shù)據(jù)庫中空閑連接的超時時間配置。
-
確認程序連接池配置： 檢查程序中使用的連接池配置，特別是 Hikari 連接池的參數(shù)，調(diào)整配置以確保連接池有效管理連接。
-
修改空閑連接超時時間（如有需要）： 如果需要，通過 SQL 命令修改數(shù)據(jù)庫中空閑連接的超時時間。
四、查詢數(shù)據(jù)庫連接數(shù)量
1. 查詢數(shù)據(jù)庫可用連接
首先，我使用以下 SQL 查詢語句查看當前數(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)
為了更詳細地了解當前連接狀態(tài)，我執(zhí)行了以下查詢，顯示了每個連接的數(shù)據(jù)庫名稱、進程 ID（PID）、應(yīng)用程序名稱和連接狀態(tài)：
SELECT datname, pid, application_name, state
FROM pg_stat_activity;
這提供了連接池中活躍連接的詳細信息，有助于定位可能導(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)整，確保連接池能夠更好地管理和釋放連接。文章來源：http://www.zghlxwxcb.cn/news/detail-833345.html
通過以上步驟，我成功解決了連接數(shù)過多的報錯問題，并優(yōu)化了數(shù)據(jù)庫連接管理。希望這些詳細的思路和步驟對您在類似問題的解決中有所幫助。文章來源地址http://www.zghlxwxcb.cn/news/detail-833345.html
到了這里，關(guān)于PostgreSQL 連接數(shù)過多報錯(too many clients already)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
