如果存在活动连接，如何删除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屋！