杀死一个postgresql会话/连接 [英] Kill a postgresql session/connection
问题描述
如何杀死所有我的postgresql连接?
How can I kill all my postgresql connections?
我试图 rake db:drop
我得到:
ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
我试过关闭从 ps -ef | grep postgres
,但这不起作用:
I've tried shutting down the processes I see from a ps -ef | grep postgres
but this doesn't work either:
kill: kill 2358 failed: operation not permitted
推荐答案
您可以使用 pg_terminate_backend()来终止连接。您必须是超级用户才能使用此功能。这在所有操作系统上都是一样的。
You can use pg_terminate_backend() to kill a connection. You have to be superuser to use this function. This works on all operating systems the same.
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name'
;
在执行此查询之前,您必须 REVOKE CONNECT权限以避免新连接:
Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections:
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
如果你使用Postgres 8.4-9.1,使用procpid而不是pid
If you're using Postgres 8.4-9.1 use procpid instead of pid
SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
procpid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name'
;
这篇关于杀死一个postgresql会话/连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!