首页 > 代码库 > How to drop a PostgreSQL database if there are active connections to it?
How to drop a PostgreSQL database if there are active connections to it?
1、PostgreSQL 9.1 and below:
SELECT pg_terminate_backend(pg_stat_activity.procpid)FROM pg_stat_activityWHERE pg_stat_activity.datname = ‘TARGET_DB‘ AND procpid <> pg_backend_pid();
PostgreSQL 9.2 and above:
SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activityWHERE pg_stat_activity.datname = ‘TARGET_DB‘ AND pid <> 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.
Note the renaming of the procpid
column to pid
. See this mailing list thread.
pg_backend_pid:Process ID of the server process attached to the current session
2、
#!/usr/bin/env bash# kill all connections to the postgres serverif [ -n "$1" ] ; then where="where pg_stat_activity.datname = ‘$1‘" echo "killing all connections to database ‘$1‘"else echo "killing all connections to database"ficat <<-EOF | psql -U postgres -d postgres SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity${where}EOF
参考:
http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it
http://www.postgresql.org/docs/9.3/static/functions-info.html
How to drop a PostgreSQL database if there are active connections to it?
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。