Postgresql - 由于某些自动连接到数据库而无法删除数据库

Postgresql - unable to drop database because of some auto connections to DB

提问by Andrius

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.

我终止了与该数据库的连接，但是如果我在此之后尝试删除数据库，有人会自动连接到该数据库并给出此错误。那能做什么？除了我，没有人使用这个数据库。

回答by Craig Ringer

You can prevent future connections:

您可以阻止将来的连接：

REVOKE CONNECT ON DATABASE thedb FROM public;

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

（以及可能的其他用户/角色;参见\l+于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 pidwas called procpidso you'll have to deal with that.

在旧版本上pid被调用，procpid所以你必须处理它。

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

由于您已撤销CONNECT权限，因此尝试自动连接的任何内容都不再能够这样做。

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.

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

回答by Suneel Kumar

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.

First You need to revoke

首先你需要撤销

REVOKE CONNECT ON DATABASE TARGET_DB FROM public;

Then use:

然后使用：

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

It will surely work.

它肯定会起作用。

回答by Dinesh Pallapa

I found a solution for this problem try to run this command in terminal

我找到了解决此问题的方法尝试在终端中运行此命令

ps -ef | grep postgres

kill process by this command

用这个命令杀死进程

sudo kill -9 PID

回答by Dinesh Pallapa

Simply check what is the connection, where it's coming from. You can see all this in:

只需检查连接是什么，它来自哪里。您可以在以下位置看到所有这些：

select * from pg_stat_activity where datname = 'TARGET_DB';

Perhaps it is your connection?

也许这是你的联系？

回答by Suman Astani

It means other user is accessing the database... Simply, restart the postgresql. This command will do the trick

这意味着其他用户正在访问数据库......简单地，重新启动postgresql。这个命令可以解决问题

root@kalilinux:~#sudo service postgresql restart

Then Try dropping the database:

然后尝试删除数据库：

postgres=# drop database test_database

This will do the trick. Happy coding

这将解决问题。快乐编码

回答by Andrew

pgAdmin 4solution using UI

使用 UI 的pgAdmin 4解决方案

First enable show activity on dashboard if you haven't:

如果您还没有，请首先在仪表板上启用显示活动：

File > Preferences > Dashboards > Display > Show Activity > true

Now disable all the processes using the db:

现在使用数据库禁用所有进程：

1. Click the DB name
2. Click Dashboard > Sessions
3. Click refresh icon
4. Click the delete (x) icon beside each process to end them

1. 单击数据库名称
2. 单击仪表板 > 会话
3. 点击刷新图标
4. 单击每个进程旁边的删除 (x) 图标以结束它们

Should now be able to delete the db.

现在应该可以删除数据库了。

回答by ScotchAndSoda

If no potential impact on other services on your machine, simply service postgresql restart

如果对您机器上的其他服务没有潜在影响，只需 service postgresql restart

回答by amoljdv06

Solution:
1. Shut down Pg server

2. It will disconnect all active connection
3. Restart Pg Server
4. Try your command

解决方案：
1. 关闭 Pg 服务器 2. 它将断开所有活动连接 3. 重新启动 Pg 服务器 4. 尝试您的命令

回答by OdkoPP

Simple as that

就那么简单

sudo service postgresql restart

回答by Sagan

In my case, I am using AWS Redshift (based on Postgres). And it appears there are no other connections to the DB, but I am getting this same error.

就我而言，我使用的是 AWS Redshift（基于 Postgres）。似乎没有其他连接到数据库，但我收到了同样的错误。

ERROR:  database "XYZ" is being accessed by other users

In my case, it seems the database cluster is still doing some processing on the database, and while there are no other external/user connections, the database is still internally in use. I found this by running the following:

就我而言，似乎数据库集群仍在对数据库进行一些处理，虽然没有其他外部/用户连接，但数据库仍在内部使用。我通过运行以下命令发现了这一点：

SELECT * FROM stv_sessions;

So my hack was to write a loop in my code, looking for rows with my database name in it. (of course the loop is not infinite, and is a sleepy loop, etc)

所以我的 hack 是在我的代码中编写一个循环，寻找其中包含我的数据库名称的行。（当然循环不是无限的，是一个困循环等）

SELECT * FROM stv_sessions where db_name = 'XYZ';

If rows found, proceed to delete each PID, one by one.

如果找到行，则继续逐个删除每个 PID。

SELECT pg_terminate_backend(PUT_PID_HERE);

If no rows found, proceed to drop the database

如果没有找到行，继续删除数据库

DROP DATABASE XYZ;

Note: In my case, I am writing Java unit/system tests, where this could be considered acceptable. This is not acceptable for production code.

注意：就我而言，我正在编写 Java 单元/系统测试，这被认为是可以接受的。这对于生产代码是不可接受的。

---

---

Here is the complete hack, in Java (ignore my test/utility classes).

这是 Java 中的完整 hack（忽略我的测试/实用程序类）。

  int i = 0;
  while (i < 10) {
    try {
      i++;
      logStandardOut("First try to delete session PIDs, before dropping the DB");
      String getSessionPIDs = String.format("SELECT stv_sessions.process, stv_sessions.* FROM stv_sessions where db_name = '%s'", dbNameToReset);
      ResultSet resultSet = databaseConnection.execQuery(getSessionPIDs);
      while (resultSet.next()) {
        int sessionPID = resultSet.getInt(1);
        logStandardOut("killPID: %s", sessionPID);
        String killSessionPID = String.format("select pg_terminate_backend(%s)", sessionPID);
        try {
          databaseConnection.execQuery(killSessionPID);
        } catch (DatabaseException dbEx) {
          //This is most commonly when a session PID is transient, where it ended between my query and kill lines
          logStandardOut("Ignore it, you did your best: %s, %s", dbEx.getMessage(), dbEx.getCause());
        }
      }

      //Drop the DB now
      String dropDbSQL = String.format("DROP DATABASE %s", dbNameToReset);
      logStandardOut(dropDbSQL);
      databaseConnection.execStatement(dropDbSQL);
      break;
    } catch (MissingDatabaseException ex) {
      //ignore, if the DB was not there (to be dropped)
      logStandardOut(ex.getMessage());
      break;
    } catch (Exception ex) {
      logStandardOut("Something went wrong, sleeping for a bit: %s, %s", ex.getMessage(), ex.getCause());
      sleepMilliSec(1000);
    }
  }