引起：org.postgresql.util.PSQLException：致命：剩余的连接槽是为非复制超级用户连接保留的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47651864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
提问by franco
I use c3p0-0.9.5.2.jar and mchange-commons-java-0.2.11.jar to manage the pool connection, And I use postgreSql 9.3.
我使用 c3p0-0.9.5.2.jar 和 mchange-commons-java-0.2.11.jar 来管理池连接，我使用 postgreSql 9.3。
I get these messages at least once a day in my Prod environment :
在我的 Prod 环境中，我每天至少收到一次这些消息：
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:692)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
... 212 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1469)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 215 more
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:712)
I have this config in my aplication :
我的应用程序中有这个配置：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="org.postgresql.Driver"/>
<property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/Test"/>
<property name="user" value="postgres"/>
<property name="password" value="postgres"/>
<!-- pool sizing -->
<!-- pool sizing -->
<property name="initialPoolSize" value="32" />
<property name="minPoolSize" value="30" />
<property name="maxPoolSize" value="300" />
<property name="acquireIncrement" value="10" />
<property name="maxStatements" value="0" />
<!-- retries -->
<property name="acquireRetryAttempts" value="30" />
<property name="acquireRetryDelay" value="1000" /> <!-- 1s -->
<property name="breakAfterAcquireFailure" value="false" />
<!-- refreshing connections -->
<property name="maxIdleTime" value="180" /> <!-- 3min -->
<property name="maxConnectionAge" value="10" /> <!-- 1h -->
<!-- timeouts and testing -->
<property name="checkoutTimeout" value="0" /> <!-- 60s -->
<property name="idleConnectionTestPeriod" value="60" /> <!-- 60 -->
<property name="testConnectionOnCheckout" value="true" />
<property name="preferredTestQuery" value="SELECT 1" />
<property name="testConnectionOnCheckin" value="true" />
</bean>
</beans>
in postgresql.conf I have this config :
在 postgresql.conf 我有这个配置：
max_connections = 300
shared_buffers = 32GB
my server has this performance: 24 cpu, 256 GB memory
我的服务器有这样的性能：24 cpu，256 GB 内存
the number of users using the application is around 1300
使用该应用程序的用户数量约为 1300
is there anyone who can help me to solve this problem
有没有人可以帮我解决这个问题
thank you in advance
先感谢您
回答by Laurenz Albe
There are superuser_reserved_connectionsconnections slots (3 by default) that are reserved for superusers so that they can connect even in a situation where all connection slots are taken.
有一些superuser_reserved_connections连接槽（默认为3 个）是为超级用户保留的，这样即使在所有连接槽都被占用的情况下，他们也可以连接。
So you effectively only have 297 slots available.
因此，您实际上只有 297 个可用插槽。
Either reduce the maximum number of connections of your connection pool or increase max_connectionsin PostgreSQL.
要么减少连接池的最大连接数，要么增加max_connectionsPostgreSQL。
By the way, 300 is much too high. You should use a much lower setting with a connection pool (unless you have hundreds of cores in your database machine).
顺便说一句，300太高了。您应该对连接池使用低得多的设置（除非您的数据库机器中有数百个内核）。
