如果存在活动连接，如何删除 PostgreSQL 数据库？

Question

我需要编写一个脚本来删除 PostgreSQL 数据库。可能有很多连接，但脚本应该忽略它。

当存在打开的连接时，标准 DROP DATABASE db_name 查询不起作用。

我该如何解决这个问题？

Answer 1

这将删除除您之外的现有连接：

查询 pg_stat_activity 并获取您想要终止的 pid 值，然后向它们发出 SELECT pg_terminate_backend(pid int) 。

PostgreSQL 9.2 及更高版本：

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 及更低版本：

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND procpid <> pg_backend_pid();

一旦您断开所有连接，您将必须断开连接并从另一个数据库（也不是您的数据库）的连接发出 DROP DATABASE 命令。试图下降。

请注意将 procpid 列重命名为 pid。请参阅此邮件列表主题。

Answer 2

在 PostgreSQL 9.2 及更高版本中，要断开除会话之外的所有内容与所连接的数据库的连接：

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

在旧版本中是相同的，只需将 pid 更改为 procpid。要断开与其他数据库的连接，只需将 current_database() 更改为您想要断开用户连接的数据库的名称。

在断开用户连接之前，您可能需要REVOKE数据库用户的CONNECT权限，否则用户将继续重新连接，而您将永远没有机会删除数据库。请参阅此评论 以及与之相关的问题，如何从数据库中分离所有其他用户。

如果您只想断开空闲用户的连接，请参阅此问题。

Answer 3

PostgreSQL 13 引入了 FORCE 选项。

删除数据库

DROP DATABASE 删除数据库...此外，如果其他人连接到目标数据库，此命令将失败，除非您使用 FORCE 选项如下所述。

力量

尝试终止与目标数据库的所有现有连接。如果目标数据库中存在准备好的事务、活动逻辑复制槽或订阅，它不会终止。

DROP DATABASE db_name WITH (FORCE);

Answer 4

Easy Peasy。

我只是在 Ubuntu 中重新启动服务来断开连接的客户端。

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;

Answer 5

您可以使用 pg_terminate_backend(int) 函数在删除数据库之前终止所有连接。

您可以使用系统视图 pg_stat_activity 获取所有正在运行的后端

我不完全确定，但以下内容可能会终止所有会话：

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'

当然，您可能没有将自己连接到该数据库

Answer 6

根据您的 postgresql 版本，您可能会遇到一个错误，该错误会使 pg_stat_activity 忽略掉线用户的活动连接。这些连接也不会显示在 pgAdminIII 内。

如果您正在进行自动测试（您还创建用户），这可能是一种可能的情况。

在这种情况下，您需要恢复到如下查询：

 SELECT pg_terminate_backend(procpid) 
 FROM pg_stat_get_activity(NULL::integer) 
 WHERE datid=(SELECT oid from pg_database where datname = 'your_database');

注意：在 9.2+ 中，您将把 procpid 更改为 pid。

Answer 7

我注意到 postgres 9.2 现在调用列 pid 而不是 procpid。

我倾向于从 shell 中调用它：

#!/usr/bin/env bash
# kill all connections to the postgres server
if [ -n "$1" ] ; then
  where="where pg_stat_activity.datname = '$1'"
  echo "killing all connections to database '$1'"
else
  echo "killing all connections to database"
fi

cat <<-EOF | psql -U postgres -d postgres 
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
${where}
EOF

希望有帮助。感谢 @JustBob 的 sql。

Answer 8

PostgreSQL 9.2 及更高版本：

SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'

Answer 9

这是我的黑客... =D

# Make sure no one can connect to this database except you!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "UPDATE pg_database SET datallowconn=false WHERE datname='<DATABASE_NAME>';"

# Drop all existing connections except for yours!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<DATABASE_NAME>' AND pid <> pg_backend_pid();"

# Drop database! =D
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "DROP DATABASE <DATABASE_NAME>;"

我提出这个答案是因为包含一个命令（上面）来阻止新连接，并且因为任何使用该命令的尝试...

REVOKE CONNECT ON DATABASE <DATABASE_NAME> FROM PUBLIC, <USERS_ETC>;

...都无法阻止新连接！

感谢@araqnid @GoatWalker！ =D

https://stackoverflow.com/a/3185413/3223785

Answer 10

在 Linux 命令提示符中，我首先通过绑定此命令来停止正在运行的所有 postgresql 进程
sudo /etc/init.d/postgresql restart

键入命令
bg 检查其他 postgresql 进程是否仍在运行

，然后使用 dropdb dbname 删除数据库

sudo /etc/init.d/postgresql restart
bg
dropdb dbname

这在 Linux 命令提示符下对我有用

Answer 11

就我而言，我必须执行一个命令来删除所有连接，包括

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()

终止所有连接的活动管理员连接，并向我显示致命的“错误”消息：

FATAL：由于管理员命令 SQL 状态而终止连接：57P01< /code>

之后就可以删除数据库了

Answer 12

除了我使用 pgAdmin4 登录并在仪表板上断开了除 pgAdmin4 之外的所有连接，然后能够通过右键单击数据库和属性来重命名并输入新名称之外，没有任何效果对我有用。