PostgreSQL-由于与数据库的某些自动连接而无法删除数据库 [英] Postgresql - unable to drop database because of some auto connections to DB

问题描述

每当我尝试删除数据库时，我都会得到：

 错误：其他用户$ b正在访问数据库 pilot $ b详细信息：使用该数据库还有另外1个会话。 
  

当我使用时：

  SELECT pg_terminate_backend（pg_stat_activity.pid）
从pg_stat_activity 
 WHERE pg_stat_activity.datname ='TARGET_DB'; 
  

我终止了与该数据库的连接，但是如果之后尝试删除数据库，则有人会自动连接到该数据库并给出此错误。该怎么办呢？
除我之外，没有人使用此数据库。

解决方案

您可以防止以后的连接：

 撤销public上数据库thedb的连接； 
  

（以及其他用户/角色；请参见 \l + psql ）中的c $ c>）$ p
$ b

然后您可以终止与该数据库的所有连接，但您自己的除外：

  SELECT pid，pg_terminate_backend（pid）
 FROM pg_stat_activity 
其中datname = current_database（）AND pid<> ; pg_backend_pid（）; 
  

在旧版本中， pid 被称为 procpid ，因此您必须处理该问题。

自从您撤消了 CONNECT 权限，无论尝试进行自动连接的什么都不再能够这样做。

您现在可以删除数据库。

如果您使用超级用户连接进行正常操作，则此方法将无效，但如果这样做，则需要首先解决该问题。

Whenever I try to drop database I get:

ERROR:  database "pilot" is being accessed by other users
DETAIL:  There is 1 other session using the database.

When I use:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

I terminated the connection from that DB, but if I try to drop database after that somehow someone automatically connects to that database and gives this error. What could be doing that? No one uses this database, except me.

解决方案

You can prevent future connections:

REVOKE CONNECT ON DATABASE thedb FROM public;

(and possibly other users/roles; see \l+ in psql)

You can then terminate all connections to this db except your own:

SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = current_database() AND pid <> pg_backend_pid();

On older versions pid was called procpid so you'll have to deal with that.

Since you've revoked CONNECT rights, whatever was trying to auto-connect should no longer be able to do so.

You'll now be able to drop the DB.

This won't work if you're using superuser connections for normal operations, but if you're doing that you need to fix that problem first.

这篇关于PostgreSQL-由于与数据库的某些自动连接而无法删除数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！