如果有活动连接，如何删除 PostgreSQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5408156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop a PostgreSQL database if there are active connections to it?
提问by Roman Prykhodchenko
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.
我需要编写一个脚本来删除 PostgreSQL 数据库。可能有很多连接，但脚本应该忽略它。
The standard DROP DATABASE db_namequery doesn't work when there are open connections.
DROP DATABASE db_name当有打开的连接时，标准查询不起作用。
How can I solve the problem?
我该如何解决问题？
回答by Kuberchaun
This will drop existing connections except for yours:
这将删除除您之外的现有连接：
Query pg_stat_activityand get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int)to them.
查询pg_stat_activity并获取要杀死的 pid 值，然后SELECT pg_terminate_backend(pid int)向它们发出。
PostgreSQL 9.2 and above:
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 and below:
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();
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.
一旦您断开所有人的连接，您将必须断开连接并从另一个数据库的连接发出 DROP DATABASE 命令，也就是您试图删除的那个。
Note the renaming of the procpidcolumn to pid. See this mailing list thread.
请注意将procpid列重命名为pid. 请参阅此邮件列表线程。
回答by Craig Ringer
In PostgreSQL 9.2 and above, to disconnect everything except your session from the database you are connected to:
在 PostgreSQL 9.2 及更高版本中，要将除会话之外的所有内容与所连接的数据库断开连接：
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid();
In older versions it's the same, just change pidto procpid. To disconnect from a different database just change current_database()to the name of the database you want to disconnect users from.
在旧版本中它是相同的，只需更改pid为procpid. 要从不同的数据库断开连接，只需更改current_database()要断开用户连接的数据库的名称。
You may want to REVOKEthe CONNECTright from users of the database before disconnecting users, otherwise users will just keep on reconnecting and you'll never get the chance to drop the DB. See this commentand the question it's associated with, How do I detach all other users from the database.
您可能希望REVOKE在CONNECT从数据库的用户断开用户之前正确的，否则用户只会继续重新连接和你永远不会放弃的DB的机会。请参阅此评论以及与之相关的问题How do I detach all other users from the database。
If you just want to disconnect idle users, see this question.
如果您只想断开空闲用户的连接，请参阅此问题。
回答by a_horse_with_no_name
You could kill all connections before dropping the database using the pg_terminate_backend(int)function.
您可以在使用该pg_terminate_backend(int)函数删除数据库之前终止所有连接。
You can get all running backends using the system view pg_stat_activity
您可以使用系统视图获取所有正在运行的后端 pg_stat_activity
I'm not entirely sure, but the following would probably kill all sessions:
我不完全确定，但以下内容可能会杀死所有会话：
select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'
Of course you may not be connected yourself to that database
当然你自己可能没有连接到那个数据库
回答by jb.
Depending on your version of postgresql you might run into a bug, that makes pg_stat_activityto omit active connections from dropped users. These connections are also not shown inside pgAdminIII.
根据您的 postgresql 版本，您可能会遇到一个错误，这会pg_stat_activity导致忽略掉线用户的活动连接。这些连接也没有显示在 pgAdminIII 中。
If you are doing automatic testing (in which you also create users) this might be a probable scenario.
如果您正在进行自动测试（您还创建用户），这可能是一个可能的场景。
In this case you need to revert to queries like:
在这种情况下，您需要恢复到以下查询：
SELECT pg_terminate_backend(procpid)
FROM pg_stat_get_activity(NULL::integer)
WHERE datid=(SELECT oid from pg_database where datname = 'your_database');
NOTE: In 9.2+ you'll have change procpidto pid.
注意：在 9.2+ 中，您将更procpid改为pid.
回答by kbrock
I noticed that postgres 9.2 now calls the column pid rather than procpid.
我注意到 postgres 9.2 现在调用列 pid 而不是 procpid。
I tend to call it from the shell:
我倾向于从 shell 调用它：
#!/usr/bin/env bash
# kill all connections to the postgres server
if [ -n "" ] ; then
where="where pg_stat_activity.datname = ''"
echo "killing all connections to database ''"
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
Hope that is helpful. Thanks to @JustBob for the sql.
希望这是有帮助的。感谢@JustBob 提供的 sql。
回答by devdrc
I just restart the service in Ubuntu to disconnect connected clients.
我只是在 Ubuntu 中重新启动服务以断开连接的客户端。
sudo service postgresql stop
sudo service postgresql start
psql
DROP DATABASE DB_NAME;
回答by Maurice Elagu
In Linux command Prompt, I would first stop all postgresql processes that are running by tying this command sudo /etc/init.d/postgresql restart
在 Linux 命令提示符中，我将首先通过绑定此命令sudo /etc/init.d/postgresql restart 来停止所有正在运行的 postgresql 进程
type the command bgto check if other postgresql processes are still running
键入命令 bg以检查其他 postgresql 进程是否仍在运行
then followed by dropdb dbnameto drop the database
然后是dropdb dbname删除数据库
sudo /etc/init.d/postgresql restart
bg
dropdb dbname
This works for me on linux command prompt
这在 linux 命令提示符下对我有用
回答by Marcelo C.
PostgreSQL 9.2 and above:
PostgreSQL 9.2 及以上：
SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'
SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'
回答by Eduardo Lucio
Here's my hack... =D
这是我的黑客... =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>;"
I put this answer because include a command (above) to block new connections and because any attempt with the command...
我提出这个答案是因为包含一个命令（上面）来阻止新连接，并且因为任何使用该命令的尝试......
REVOKE CONNECT ON DATABASE <DATABASE_NAME> FROM PUBLIC, <USERS_ETC>;
... do not works to block new connections!
... 不能阻止新连接！
Thanks to @araqnid @GoatWalker ! =D
感谢@araqnid @GoatWalker！=D
回答by Lukasz Szozda
Upcoming PostgreSQL 13 will introduce FORCEoption.
即将发布的 PostgreSQL 13 将引入FORCE选项。
DROP DATABASEdrops a database ... Also, if anyone else is connected to the target database, this command will fail unless you use the FORCEoption described below.
FORCE
Attempt to terminate all existing connections to the target database. It doesn't terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.
DROP DATABASE删除数据库...此外，如果其他人连接到目标数据库，除非您使用下面描述的FORCE选项，否则此命令将失败。
力量
尝试终止与目标数据库的所有现有连接。如果目标数据库中存在准备好的事务、活动的逻辑复制槽或订阅，它不会终止。
DROP DATABASE db_name WITH (FORCE);
