杀死一个 postgresql 会话/连接 [英] Kill a postgresql session/connection

问题描述

如何终止我所有的 postgresql 连接?

我正在尝试 rake db:drop 但我得到:

错误:数据库database_name"正在被其他用户访问详细信息:还有 1 个其他会话正在使用该数据库.

我尝试关闭从 ps -ef | 看到的进程grep postgres 但这也不起作用:

kill: kill 2358 failed: operation not allowed

解决方案

您可以使用 pg_terminate_backend() 终止连接.您必须是超级用户才能使用此功能.这在所有操作系统上都一样.

SELECTpg_terminate_backend(pid)从pg_stat_activity在哪里——不要破坏我自己的连接！pid <>pg_backend_pid()-- 不要终止与其他数据库的连接AND datname = 'database_name';

在执行此查询之前，您必须REVOKE CONNECT 权限以避免新的连接:

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;

<块引用>

如果您使用的是 Postgres 8.4-9.1，请使用 procpid 而不是 pid

SELECTpg_terminate_backend(procpid)从pg_stat_activity在哪里——不要破坏我自己的连接！procpid <>pg_backend_pid()-- 不要终止与其他数据库的连接AND datname = 'database_name';

How can I kill all my postgresql connections?

I'm trying a rake db:drop but I get:

ERROR:  database "database_name" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.

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

解决方案

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'
    ;

Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections:

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;

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屋！