如何杀死 Postgresql 中的空闲连接。？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30931683/
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
How to kill the idle connection in Postgresql.?
提问by Priya
I am using java servlets along with pgadmin 9.1.Problem is the connection in servlets was not closed properly so it leads to blank screen if it reaches the maximum connection.I don't want every user to extend maximum connections in pgadmin...i have used the below query in starting and end ending point of servlet but its showing error like..connection terminated due to administrator command..
我正在使用 java servlet 和 pgadmin 9.1。问题是 servlet 中的连接没有正确关闭，因此如果达到最大连接会导致黑屏。我不希望每个用户都在 pgadmin 中扩展最大连接...我在 servlet 的起点和终点使用了以下查询，但它显示的错误如..connection因管理员命令而终止..
ResultSet rs_1q=st_Query3.executeQuery("SELECT pg_terminate_backend(pg_stat_activity.procpid)FROM pg_stat_activity WHERE pg_stat_activity.current_query = '<IDLE>' AND procpid <> pg_backend_pid();");
回答by K.Shivashankar
Generally, as @Rahul points out, its not advisable to kill connections. But if it's your last resort, this is how to terminate idle connections:
通常，正如@Rahul 指出的那样，不建议终止连接。但如果这是你最后的手段，这是终止空闲连接的方法：
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'databasename'
AND pid <> pg_backend_pid()
AND state in ('idle');
回答by Rahul Tripathi
I dont think that killing a connection is an ideal solution as there may be certain connection which might be genuinely waiting for the transaction to get completed. Instead I would suggest you to set a timeout for your transacion.
我不认为杀死连接是一个理想的解决方案，因为可能存在某些可能真正等待事务完成的连接。相反，我建议您为您的交易设置超时。
However if you are desperate to kill the idle connections then you can try like this:
但是，如果您不顾一切地杀死空闲连接，那么您可以尝试这样：
Use shell script and do "ps auxwww|grep 'idle in transaction'"which will return list of all "idle in
transaction." processes.
Then you can use "awk" and find of each line of output to get the
the process id, and finally you can use a "kill <pid>"to each process.
使用 shell 脚本并执行 "ps auxwww|grep 'idle in transaction'"这将返回所有“事务空闲”的列表。过程。然后你可以使用“awk”并查找每一行输出来获取进程ID，最后你可以"kill <pid>"对每个进程使用a 。
