Postgres删除数据库错误：pq：无法删除当前打开的数据库

Question

我试图像这样删除当前连接到的数据库，但我得到了以下错误：

pq: cannot drop the currently open database

要关闭连接是不是就必须要删除数据库？我不太理解，因为我认为我无法使用dbConn.Exec执行我的DROPDATABASE语句。

dbConn *sql.DB

func stuff() error {
  _, err := dbConn.Exec(fmt.Sprintf(`DROP DATABASE %s;`, dbName))
  if err != nil {
    return err
  }

  return dbConn.Close()
}

我想我可以连接到其他数据库，然后在那个连接上执行它，但我不确定这是否有效，而且如果这样做就必须连接到一个新的数据库，这看起来更麻烦了。有什么想法吗？谢谢。

Answer 1

Because, you are trying to execute dropDb command on database, to which you have open connection.

According to postgres documentation:

You cannot be connected to the database you are about to remove. Instead, connect to template1 or any other database and run this command again.

This makes sense, because when you drop the entire database, all the open connection referencing to that database becomes invalid, So the recommended approach is to connect to different database, and execute this command again.

If you are facing a situation, where a different client is connected to the database, and you really want to drop the database, you can forcibly disconnect all the client from that particular database.

For example, to forcibly disconnect all clients from database mydb:

If PostgreSQL < 9.2

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb';

Else

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

Note: This command requires superuser privileges.

Then, you can connect to different database, and run dropDb command again.