京公网安备11010502063487号“FATAL: sorry, too many clients already” 的标准答案是调大 max_connections。这篇在本机自己起一个 PostgreSQL 16,把上限压到 10 来复现:看超级用户保留的名额怎么影响普通用户看到的报错、怎么找出谁占着连接、调大为什么要重启、角色和数据库层面的限制报什么,以及 idle_session_timeout 的效果。
max_connections|10; superuser_reserved_connections|3
普通用户 app 成功占住 7 个连接,第 8 个失败:
psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
超级用户 postgres 又占了 3 个之后,第 4 个失败:
psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: sorry, too many clients already
此时普通用户再连(退出码 2):psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: sorry, too many clients already先是 remaining connection slots are reserved…(只剩超级用户的名额),全满才是 sorry, too many clients already。
$ select usename, state, count(*) from pg_stat_activity ... group by 1,2
app|active|5
postgres|active|3
$ 最久没变化的 3 个连接:
usename | state | query | running_for
---------+--------+-------------------------------+-----------------
app | active | select 1; select pg_sleep(60) | 00:00:0x
app | active | select 1; select pg_sleep(60) | 00:00:0x
app | active | select 1; select pg_sleep(60) | 00:00:0x
(3 rows)
pg_terminate_backend 终止 app 的连接数:5
之后连接数:3pg_stat_activity 按用户和状态统计;pg_terminate_backend 立刻释放。
reload 之后:max_connections|10|t|postmaster 重启之后:max_connections|30|f
max_connections 的 context 是 postmaster,reload 不生效。
角色限制 2,app 的第 3 个连接失败:
psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: too many connections for role "app"
数据库限制 1,appdb 的第 2 个连接失败:
psql: error: connection to server at "127.0.0.1", port 9261 failed: FATAL: too many connections for database "appdb"
超级用户连 appdb(退出码 0):?column? ---------- 1 (1 row)报错文字不同,不会因为调大 max_connections 解决。
psql 里先查一次,闲置 3.2 s(idle_session_timeout = 2s),再查一次:
r
----------------
first query ok
(1 row)
FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
连接数回到 1(只有查询自己)idle_session_timeout 让服务端关闭闲置连接,客户端连接池要处理这种关闭。