Java org.postgresql.util.PSQLException：致命：抱歉，已经有太多客户端
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2757549/
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
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
提问by lakshmi
I am trying to connect to a Postgresql database, I am getting the following Error:
我正在尝试连接到 Postgresql 数据库，但出现以下错误：
Error:org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
错误：org.postgresql.util.PSQLException：致命：抱歉，已经有太多客户端
What does the error mean and how do I fix it?
错误是什么意思，我该如何解决？
My server.propertiesfile is following:
我的server.properties文件如下：
serverPortData=9042
serverPortCommand=9078
trackConnectionURL=jdbc:postgresql://127.0.0.1:5432/vTrack?user=postgres password=postgres
dst=1
DatabaseName=vTrack
ServerName=127.0.0.1
User=postgres
Password=admin
MaxConnections=90
InitialConnections=80
PoolSize=100
MaxPoolSize=100
KeepAliveTime=100
TrackPoolSize=120
TrackMaxPoolSize=120
TrackKeepAliveTime=100
PortNumber=5432
Logging=1
采纳答案by leonbloy
We don't know what server.propertiesfile is that, we neither know what SimocoPoolSizemeans (do you?)
我们不知道那个server.properties文件是什么，我们也不知道SimocoPoolSize是什么意思（你知道吗？）
Let's guess you are using some custom pool of database connections. Then, I guess the problem is that your pool is configured to open 100 or 120 connections, but you Postgresql server is configured to accept MaxConnections=90. These seem conflictive settings. Try increasing MaxConnections=120.
假设您正在使用一些自定义的数据库连接池。然后，我猜问题是您的池配置为打开 100 或 120 个连接，但您的 Postgresql 服务器配置为接受MaxConnections=90. 这些似乎相互矛盾的设置。尝试增加MaxConnections=120.
But you should first understand your db layer infrastructure, know what pool are you using, if you really need so many open connections in the pool. And, specially, if you are gracefully returning the opened connections to the pool
但是你应该首先了解你的db层基础设施，知道你使用的是什么池，如果你真的需要池中这么多开放的连接。而且，特别是，如果您优雅地将打开的连接返回到池中
回答by Delan Azabani
The offending lines are the following:
违规行如下：
MaxConnections=90
InitialConnections=80
You can increase the values to allow more connections.
您可以增加这些值以允许更多连接。
回答by JK Tomar
No need to increase the MaxConnections & InitialConnections. Just close your connections after after doing your work. For example if you are creating connection:
无需增加 MaxConnections 和 InitialConnections。完成工作后关闭您的连接。例如，如果您正在创建连接：
try {
connection = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1/"+dbname,user,pass);
} catch (SQLException e) {
e.printStackTrace();
return;
}
After doing your work close connection:
完成工作后关闭连接：
try {
connection.commit();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
回答by Eric Leschinski
An explanation of the following error:
对以下错误的解释：
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already.
Summary:
概括：
You opened up more than the allowed limit of connections to the database. You ran something like this: Connection conn = myconn.Open();inside of a loop, and forgot to run conn.close();. Just because your class is destroyed and garbage collected does not release the connection to the database. The quickest fix to this is to make sure you have the following code with whatever class that creates a connection:
您打开的数据库连接数超出了允许的限制。你运行了这样的东西：Connection conn = myconn.Open();在一个循环内，忘了运行conn.close();。仅仅因为您的类被销毁并且垃圾收集不会释放与数据库的连接。对此最快的解决方法是确保您具有以下代码，其中包含创建连接的任何类：
protected void finalize() throws Throwable
{
try { your_connection.close(); }
catch (SQLException e) {
e.printStackTrace();
}
super.finalize();
}
Place that code in any class where you create a Connection. Then when your class is garbage collected, your connection will be released.
将该代码放置在您创建连接的任何类中。然后当你的类被垃圾收集时，你的连接将被释放。
Run this SQL to see postgresql max connections allowed:
运行此 SQL 以查看允许的 postgresql 最大连接数：
show max_connections;
The default is 100. PostgreSQL on good hardware can support a few hundred connections at a time. If you want to have thousands, you should consider using connection pooling software to reduce the connection overhead.
默认值为 100。良好硬件上的 PostgreSQL 一次可以支持几百个连接。如果你想拥有数千个，你应该考虑使用连接池软件来减少连接开销。
Take a look at exactly who/what/when/where is holding open your connections:
看看究竟是谁/什么/何时/何地打开了您的连接：
SELECT * FROM pg_stat_activity;
The number of connections currently used is:
当前使用的连接数为：
SELECT COUNT(*) from pg_stat_activity;
Debugging strategy
调试策略
You could give different usernames/passwords to the programs that might not be releasing the connections to find out which one it is, and then look in pg_stat_activity to find out which one is not cleaning up after itself.
Do a full exception stack trace when the connections could not be created and follow the code back up to where you create a new
Connection, make sure every code line where you create a connection ends with aconnection.close();
您可以为可能不会释放连接的程序提供不同的用户名/密码，以找出它是哪一个，然后查看 pg_stat_activity 以找出哪个程序自己没有清理。
当无法创建连接时执行完整的异常堆栈跟踪并按照代码返回到创建新连接的位置
Connection，确保创建连接的每个代码行都以connection.close();
How to set the max_connections higher:
如何将 max_connections 设置得更高：
max_connections in the postgresql.conf sets the maximum number of concurrent connections to the database server.
postgresql.conf 中的 max_connections 设置到数据库服务器的最大并发连接数。
- First find your postgresql.conf file
- If you don't know where it is, query the database with the sql:
SHOW config_file; - Mine is in:
/var/lib/pgsql/data/postgresql.conf - Login as root and edit that file.
- Search for the string: "max_connections".
- You'll see a line that says
max_connections=100. - Set that number bigger, check the limit for your postgresql version.
- Restart the postgresql database for the changes to take effect.
- 首先找到你的 postgresql.conf 文件
- 如果你不知道它在哪里，用sql查询数据库：
SHOW config_file; - 我的是在：
/var/lib/pgsql/data/postgresql.conf - 以 root 身份登录并编辑该文件。
- 搜索字符串：“max_connections”。
- 你会看到一行写着
max_connections=100。 - 将该数字设置得更大，检查您的 postgresql 版本的限制。
- 重新启动 postgresql 数据库以使更改生效。
What's the maximum max_connections?
最大 max_connections 是多少？
Use this query:
使用此查询：
select min_val, max_val from pg_settings where name='max_connections';
I get the value 8388607, in theory that's the most you are allowed to have, but then a runaway process can eat up thousands of connections, and surprise, your database is unresponsive until reboot. If you had a sensible max_connections like 100. The offending program would be denied a new connection.
我得到了值8388607，理论上这是您允许拥有的最大值，但是失控的进程可能会消耗数千个连接，而且令人惊讶的是，您的数据库在重新启动之前没有响应。如果你有一个合理的 max_connections 像 100。有问题的程序将被拒绝一个新的连接。
回答by Brayan Escobedo
You need to close all your connexions for example: If you make an INSERT INTO statement you need to close the statement and your connexion in this way:
例如，您需要关闭所有连接：如果您执行 INSERT INTO 语句，则需要以这种方式关闭该语句和您的连接：
statement.close();
Connexion.close():
And if you make a SELECT statement you need to close the statement, the connexion and the resultset in this way:
如果你做一个 SELECT 语句，你需要以这种方式关闭语句、连接和结果集：
resultset.close();
statement.close();
Connexion.close();
I did this and it worked
我这样做了，它奏效了
