通过命令行删除PostgreSQL数据库[关闭]

本文翻译自：Drop PostgreSQL database through command line [closed]

I'm trying to drop my database and create a new one through the command line. 我正在尝试删除我的数据库并通过命令行创建一个新数据库。

I log in using psql -U username and then do a \\connect template1 , followed by a DROP DATABASE databasename; 我使用psql -U username ，然后执行\\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. 我关闭Apache并再次尝试这个，但我仍然收到此错误。 Am I doing something wrong? 难道我做错了什么？

---

#1楼

参考：https://stackoom.com/question/tGd7/通过命令行删除PostgreSQL数据库-关闭

---

#2楼

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. 如果这不起作用，我看到postgres持有孤立的准备语句的问题。
To clean them up, do this: 要清理它们，请执行以下操作：

SELECT * FROM pg_prepared_xacts;

then for every id you see, run this: 那么对于你看到的每个id，运行这个：

ROLLBACK PREPARED '<id>';

---

#3楼

You can run the dropdb command 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';

---

#4楼

When it says users are connected, what does the query "select * from pg_stat_activity;" 当它说用户已连接时，查询是什么“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. 如果是这样，您可能必须编辑pg_hba.conf文件以拒绝来自其他用户的连接，或者关闭访问pg数据库的任何应用程序以便能够删除它。 I have this problem on occasion in production. 我有时会在制作中遇到这个问题。 Set pg_hba.conf to have a two lines like this: 将pg_hba.conf设置为两行，如下所示：

local   all         all                               ident
host    all         all         127.0.0.1/32          reject

and tell pgsql to reload or restart (ie 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. 并告诉pgsql重新加载或重新启动（即sudo /etc/init.d/postgresql重新加载或pg_ctl重新加载），现在连接到您的机器的唯一方法是通过本地套接字。 I'm assuming you're on linux. 我假设你在Linux上。 If not this may need to be tweaked to something other than local / ident on that first line, to something like host ... yourusername. 如果不是这可能需要调整到第一行上本地/身份以外的东西，就像主机... yourusername。

Now you should be able to do: 现在你应该能够做到：

psql postgres
drop database mydatabase;

---

#5楼

This worked for me: 这对我有用：

select pg_terminate_backend(pid) from pg_stat_activity where datname='YourDatabase';

for postgresql earlier than 9.2 replace pid with procpid 对于早于9.2的postgresql，用procpid替换pid

DROP DATABASE "YourDatabase";

http://blog.gahooa.com/2010/11/03/how-to-force-drop-a-postgresql-database-by-killing-off-connection-processes/ http://blog.gahooa.com/2010/11/03/how-to-force-drop-a-postgresql-database-by-killing-off-connection-processes/