我需要编写一个脚本来删除 PostgreSQL 数据库。可能有很多联系,但脚本应该忽略它。
当有打开的连接时,标准的 DROP DATABASE db_name
查询不起作用。
我该如何解决这个问题?
这将删除除您之外的现有连接:
查询 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
。请参阅this mailing list thread。
在 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
权限,否则用户将继续重新连接,您将永远没有机会删除数据库。请参阅 this comment 及其相关的问题 How do I detach all other users from the database。
如果您只想断开空闲用户的连接,请参阅 this question。
PostgreSQL 13 引入了 FORCE
选项。
DROP DATABASE DROP DATABASE 删除数据库...此外,如果其他人连接到目标数据库,则此命令将失败,除非您使用下面描述的 FORCE 选项。 FORCE 尝试终止与目标数据库的所有现有连接。如果目标数据库中存在准备好的事务、活动的逻辑复制槽或订阅,它不会终止。
DROP DATABASE db_name WITH (FORCE);
您可以使用 pg_terminate_backend(int)
函数在删除数据库之前终止所有连接。
您可以使用系统视图 pg_stat_activity
获取所有正在运行的后端
我不完全确定,但以下可能会杀死所有会话:
select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'
当然你自己可能没有连接到那个数据库
十分简单。
我只是在 Ubuntu 中重新启动服务以断开连接的客户端。
sudo service postgresql stop
sudo service postgresql start
psql
DROP DATABASE DB_NAME;
dropdb DB_NAME
无需进入 postgres。
dropdb mydb --force
。
根据您的 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
。
procpid
更改为 pid
后,此代码段适用于 9.3。
我注意到 postgres 9.2 现在调用列 pid 而不是 procpid。
我倾向于从外壳调用它:
#!/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。
PostgreSQL 9.2 及更高版本:
SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'
这是我的技巧... =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
在 Linux 命令提示符中,我将首先通过绑定此命令 sudo /etc/init.d/postgresql restart 来停止所有正在运行的 postgresql 进程
输入命令 bg 检查其他 postgresql 进程是否仍在运行
然后是 dropdb dbname 删除数据库
sudo /etc/init.d/postgresql restart
bg
dropdb dbname
这适用于我在 linux 命令提示符下
在我的情况下,我必须执行一个命令来删除所有连接,包括我的活动管理员连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
它终止了所有连接并向我显示致命的“错误”消息:
FATAL: terminating connection due to administrator command SQL state: 57P01
之后可以删除数据库
除了我使用 pgAdmin4 登录并在仪表板上我断开了除 pgAdmin4 之外的所有连接,然后能够通过右键单击数据库和属性并键入新名称来重命名之外,没有什么对我有用。
; drop database TARGET_DB;
之前对我来说效果很好,以确保在事情开始重试时数据库已经消失。dropdb --force
付钱。