database 通过命令行删除 PostgreSQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7073773/
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
Drop PostgreSQL database through command line
提问by iman453
I'm trying to drop my database and create a new one through the command line.
我正在尝试删除我的数据库并通过命令行创建一个新数据库。
I log in using psql -U usernameand then do a \connect template1, followed by a DROP DATABASE databasename;.
我使用登录，psql -U username然后执行 a \connect template1，然后执行DROP DATABASE databasename;.
I get the error
我收到错误
database databasename is being accessed by other users
数据库 databasename 正在被其他用户访问
I shut down Apache and tried this again but I'm still getting this error. Am I doing something wrong?
我关闭了 Apache 并再次尝试，但我仍然收到此错误。难道我做错了什么？
回答by csano
You can run the dropdbcommand from the command line:
您可以从命令行运行dropdb命令：
dropdb 'database name'
Note that you have to be a superuser or the database owner to be able to drop it.
请注意，您必须是超级用户或数据库所有者才能删除它。
You can also check the pg_stat_activity view to see what type of activity is currently taking place against your database, including all idle processes.
您还可以检查 pg_stat_activity 视图以查看当前针对您的数据库进行的活动类型，包括所有空闲进程。
SELECT * FROM pg_stat_activity WHERE datname='database name';
回答by Eugene
This worked for me:
这对我有用：
select pg_terminate_backend(pid) from pg_stat_activity where datname='YourDatabase';
for postgresql earlier than 9.2 replace pidwith procpid
对于 9.2 之前的 postgresql 替换pid为procpid
DROP DATABASE "YourDatabase";
回答by Bohemian
Try this. Note there's no database specified - it just runs "on the server"
尝试这个。请注意，没有指定数据库 - 它只是“在服务器上”运行
psql -U postgres -c "drop database databasename"
If that doesn't work, I have seen a problem with postgres holding onto orphaned prepared statements.
To clean them up, do this:
如果这不起作用，我已经看到 postgres 保留孤立的准备好的语句的问题。
要清理它们，请执行以下操作：
SELECT * FROM pg_prepared_xacts;
then for every id you see, run this:
然后对于你看到的每个 id，运行这个：
ROLLBACK PREPARED '<id>';
回答by Scott Marlowe
When it says users are connected, what does the query "select * from pg_stat_activity;" say? Are the other users besides yourself now connected? If so, you might have to edit your pg_hba.conf file to reject connections from other users, or shut down whatever app is accessing the pg database to be able to drop it. I have this problem on occasion in production. Set pg_hba.conf to have a two lines like this:
当它说用户已连接时，查询“select * from pg_stat_activity;”是什么意思？说？除了您自己之外的其他用户现在是否已连接？如果是这样，您可能必须编辑 pg_hba.conf 文件以拒绝来自其他用户的连接，或者关闭任何正在访问 pg 数据库的应用程序才能删除它。我在生产中偶尔会遇到这个问题。将 pg_hba.conf 设置为如下两行：
local all all ident
host all all 127.0.0.1/32 reject
and tell pgsql to reload or restart (i.e. either sudo /etc/init.d/postgresql reload or pg_ctl reload) and now the only way to connect to your machine is via local sockets. I'm assuming you're on linux. If not this may need to be tweaked to something other than local / ident on that first line, to something like host ... yourusername.
并告诉 pgsql 重新加载或重新启动（即 sudo /etc/init.d/postgresql reload 或 pg_ctl reload），现在连接到您的机器的唯一方法是通过本地套接字。我假设你在 linux 上。如果不是，这可能需要在第一行调整为本地 / ident 以外的其他内容，例如主机 ...您的用户名。
Now you should be able to do:
现在你应该能够做到：
psql postgres
drop database mydatabase;
