如果有活动连接，如何删除 PostgreSQL 数据库? [英] How to drop a PostgreSQL database if there are active connections to it?

问题描述

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

I need to write a script that will drop a PostgreSQL database. There may be a lot of connections to it, but the script should ignore that.

标准 DROP DATABASE db_name 查询在有打开连接时不起作用.

The standard DROP DATABASE db_name query doesn't work when there are open connections.

我该如何解决问题?

推荐答案

这将丢弃除您之外的现有连接:

This will drop existing connections except for yours:

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

Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

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 命令，该连接也不是您试图删除的那个.

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.

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

Note the renaming of the procpid column to pid. See this mailing list thread.

这篇关于如果有活动连接，如何删除 PostgreSQL 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！