使用 PostgreSQL 强制客户端断开连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/727194/
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
Force client disconnect using PostgreSQL
提问by Luke Francl
Is there a way to force clients to disconnect from PostgreSQL? I'm looking for the equivlent of DB2's force application all.
有没有办法强制客户端与 PostgreSQL 断开连接？我正在寻找 DB2 的force application all.
I'd like to do this on my development box because when I've got database consoles open, I can't load a database dump. I have to quit them first.
我想在我的开发箱上这样做，因为当我打开数据库控制台时，我无法加载数据库转储。我必须先退出他们。
采纳答案by Milen A. Radev
Combine pg_terminate_backendfunction and the pg_stat_activitysystem view.
结合pg_terminate_backend功能和pg_stat_activity系统视图。
回答by bonyiii
Kills idle processes in PostgreSQL 8.4:
在 PostgreSQL 8.4 中杀死空闲进程：
SELECT procpid, (SELECT pg_terminate_backend(procpid)) as killed from pg_stat_activity
WHERE current_query LIKE '<IDLE>';
回答by mirabilos
This SO answerbeautifully explains (full quote from araqnidbetween the horizontal rules, then me again):
这个 SO 答案很好地解释了（从araqnid水平规则之间的完整引用，然后是我）：
To mark database 'applogs' as not accepting new connections:
将数据库“applogs”标记为不接受新连接：
update pg_database set datallowconn = false where datname = 'applogs';
Another possibility would be to revoke 'connect' access on the database for the client role(s).
另一种可能性是撤销客户端角色对数据库的“连接”访问权限。
Disconnect users from database = kill backend. So to disconnect all other users from "applogs" database, for example:
断开用户与数据库的连接 = 终止后端。因此，要断开所有其他用户与“applogs”数据库的连接，例如：
select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'applogs' and procpid <> pg_backend_pid();
Once you've done both of those, you are the only user connected to 'applogs'. Although there might actually be a delay before the backends actually finish disconnecting?
完成这两项操作后，您就是唯一连接到“应用程序日志”的用户。尽管在后端实际完成断开连接之前实际上可能会有延迟？
Update from MarkJL: There is indeed a delay before the backends finish disconnecting.
来自MarkJL 的更新：在后端完成断开连接之前确实存在延迟。
Now me again: That being said, mind that the procpidcolumn was renamed to pidin PostgreSQL 9.2 and later.
现在我再说一遍：话虽如此，请注意该procpid列pid在 PostgreSQL 9.2 及更高版本中被重命名为。
I think that this is much more helpful than the answer by Milen A. Radev which, while technically the same, does not come with usage examples and real-life suggestions.
我认为这比 Milen A. Radev 的回答更有帮助，虽然技术上相同，但没有提供使用示例和现实生活中的建议。
回答by avances123
I post my answer because I couldn't use any of them in my script, server 9.3:
我发布了我的答案，因为我无法在我的脚本服务器 9.3 中使用它们中的任何一个：
psql -U postgres -c "SELECT pid, (SELECT pg_terminate_backend(pid)) as killed from pg_stat_activity WHERE datname = 'my_database_to_alter';"
In the next line, you can do anything yo want with 'my_database_to_alter'. As you can see, yo perform the query from the "postgres" database, which exists almost in every postgresql installation.
在下一行中，您可以使用“my_database_to_alter”做任何您想做的事情。如您所见，您可以从“postgres”数据库执行查询，该数据库几乎存在于每个 postgresql 安装中。
Doing by superuser and outside the problem-database itself worked perfect for me.
由超级用户和问题数据库本身之外的操作对我来说非常完美。
回答by johnjamesmiller
probably a more heavy handed approach then should be used but:
可能应该使用更严厉的方法，但是：
for x in `ps -eF | grep -E "postgres.*idle"| awk '{print }'`;do kill $x; done
回答by Luke Francl
I found this threadon the mailing list. It suggests using SIGTERM to cause the clients to disconnect.
我在邮件列表上找到了这个线程。它建议使用 SIGTERM 使客户端断开连接。
Not as clean as db2 force application all.
没有那么干净db2 force application all。
