PostgreSQL 报错 remaining connection slots are reserved for non-replication superuser connections

PostgreSQL Spring Boot About 2,315 words

错误日志

Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2825) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:175) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:313) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:263) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.Driver.makeConnection(Driver.java:443) ~[postgresql-42.6.1.jar!/:42.6.1]
    at org.postgresql.Driver.connect(Driver.java:297) ~[postgresql-42.6.1.jar!/:42.6.1]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-5.0.1.jar!/:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359) ~[HikariCP-5.0.1.jar!/:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201) ~[HikariCP-5.0.1.jar!/:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470) ~[HikariCP-5.0.1.jar!/:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-5.0.1.jar!/:na]

解释

普通用户的连接已满,保留用于非复制的超级用户连接。

当数据库剩余连接数<=为超级用户保留的连接数时,使用非超级用户连接数据库报错如上,此时只能使用超级用户连接数据库。

原因

Spring Boot设置里hikarimaximum-pool-size200,程序启动时直接开启了200个连接,导致报错。

解决

去除hikarimaximum-pool-size,使用默认配置。(默认maximum-pool-size10

查看剩余连接数

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;

查看最大连接数

postgres=# show max_connections;
 max_connections 
-----------------
 200
(1 row)

查看为超级用户保留的连接数

postgres=# show superuser_reserved_connections;
 superuser_reserved_connections 
--------------------------------
 3
(1 row)

查看当前连接数

postgres=# select count(1) from pg_stat_activity;
 count 
-------
     6
(1 row)
Views: 946 · Posted: 2024-07-16

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh