如何在有活动连接的情况下删除PostgreSQL数据库？

Question

我需要编写一个脚本来删除一个PostgreSQL数据库。可能存在许多连接到该数据库的进程，但是脚本应忽略它们。

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

我该如何解决这个问题？

- Roman Prykhodchenko

---

你使用的是哪个版本的PostgreSQL？ - Kuberchaun

问题：虽然您可以终止与数据库连接的会话，但它们可能重新连接得如此之快，以至于您仍然无法删除数据库。幸运的是，本文展示了如何锁定新连接，因此您可以终止当前连接并按计划删除数据库：http://dba.stackexchange.com/questions/11893/force-drop-db-while-others-may-be-connected - Max Murphy

我发现在dba.stackexchange上的这个答案非常有帮助 https://dba.stackexchange.com/a/11895/163539 -- 简洁而又足够解释清楚。 - hlongmore

Answer 1

此操作将断开除了你自己的所有连接： 查询pg_stat_activity并获取需要终止的进程id，然后针对它们执行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。请参见此邮件列表线程。

- Kuberchaun

---

当然，确保使用不是连接到“TARGET_DB”的数据库连接执行此操作，否则会出现“ERROR”。使用“postgres”连接效果良好。 - Rob

实际上它会逐个断开客户端连接，如果您的客户端在列表中间，它也会被断开。因此，一些连接将保持活动状态。因此，正确答案是Craig Ringer提供的（请参见下文）。SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pg_stat_activity.pid <> pg_backend_pid(); - Andrew Selivanov

在我的情况下，客户端会很快重新连接，因此将这个语句放在 ; drop database TARGET_DB; 之前可以很好地确保数据库在重试开始时已经被删除了。 - Mat Schaffer

我甚至愿意为dropdb --force支付费用。 - Torsten Bronger

@TorstenBronger 认可了！https://github.com/postgres/postgres/commit/1379fd537f9fc7941c8acff8c879ce3636dbdb77 - SanD

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权限，否则用户将继续重新连接，而您将永远没有机会删除该数据库。请参见此评论和相关问题，如何从数据库中分离所有其他用户。

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

- Craig Ringer

---

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

Answer 3

PostgreSQL 13引入了FORCE选项。

DROP DATABASE

DROP DATABASE删除一个数据库......此外，如果有其他人连接到目标数据库，则除非使用下面描述的FORCE选项，否则该命令将失败。

FORCE

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

DROP DATABASE db_name WITH (FORCE);

- Lukasz Szozda

---

Postgresql 11有什么解决方案吗？ - Hackeron

很遗憾这个功能无法以某种方式添加到pg_dump中，这将使恢复变得更加容易。 - aseques

Answer 4

易如反掌。

我只需在Ubuntu中重新启动服务即可断开已连接的客户端。

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;

- devdrc

---

这太过复杂了。只需按下重置按钮即可。 - Bob

dropdb DB_NAME 无需进入postgres。 - Bicameral Mind

@BicameralMind 当有活跃用户时，您需要使用 dropdb mydb --force。 - devdrc

/usr/lib/postgresql/11/bin/dropdb: 未识别选项 '--force' - Hackeron

@Hackeron --force 在 PostgreSQL 13 及以上版本中可用。 - devdrc

Answer 5

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

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

我不是完全确定，但以下操作可能会终止所有会话：

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

当然，您可能没有连接到该数据库。

- user330315

Answer 6

根据你所使用的postgresql版本，你可能会遇到一个bug，使得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。

- jb.

---

这正是我所寻找的，但对于（假设9.2及以上版本），您必须删除对pg_stat_activity的引用，并将procpid更改为pid。 - MDR

将“procpid”更改为“pid”后，此代码片段可在9.3上运行。 - jb.

即使不删除pg_stat_activity，是否也能运行？我在9.2上遇到了错误。 - MDR

好的。现在我明白了，那是一个打字错误。谢谢！ - jb.

从9.3版本开始，执行以下语句： SELECT pg_terminate_backend(pid) FROM pg_stat_get_activity(NULL::integer) WHERE datid=(SELECT oid from pg_database where datname = 'your_database'); - Shawn Vader

Answer 7

我注意到Postgres 9.2现在将列名从procpid改为pid。

我倾向于从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。

- kbrock

Answer 8

PostgreSQL 9.2及以上版本：

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

该命令用于终止在指定数据库上运行的所有进程。请将 'YOUR_DATABASE_NAME_HERE' 替换为您要终止进程的数据库名称。

- Marcelo C.

---

那不会终止活动连接吗？ - Cocowalla

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://dev59.com/fnA75IYBdhLWcg3wo6vx#3185413

- Eduardo Lucio

Answer 10

在Linux命令提示符中，我会首先通过输入以下命令停止所有正在运行的postgresql进程: sudo /etc/init.d/postgresql restart 输入命令 bg 检查是否仍有其他postgresql进程在运行

然后跟着 dropdb dbname 删除数据库

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

这对我在Linux命令提示符上有效。

- Maurice Elagu

---

如果你有多个数据库并且只想断开单个数据库的连接，那么这种做法就不好了。这将终止所有连接，有些过于“粗暴”。 - Nick

@Nick 对，但请记住我们正在重新启动所有连接并完全停止它们。 - Maurice Elagu