Why Can't I Drop an Open Database in Postgres?

Trouble Dropping Open Database in Postgres

Error Encountered

When attempting to drop a database, you may encounter the error:

pq: cannot drop the currently open database

Understanding the Issue

This error occurs because you're attempting to drop the database you're currently connected to. Dropping a database invalidates all open connections to it, including the one you're using.

Recommended Solution

To resolve this issue, it's recommended to connect to a different database and execute the DROP DATABASE command from there. This ensures that you have an active connection to execute the command while avoiding the issue with dropping the current database.

Example Code:

// Connect to a secondary database
otherDbConn, err := sql.Open("postgres", "host=localhost port=5432 user=username dbname=otherdb")
if err != nil {
  return err
}

// Execute DROP DATABASE command on other database
_, err = otherDbConn.Exec(fmt.Sprintf(`DROP DATABASE %s;`, dbName))
if err != nil {
  return err
}

// Close the connection to the other database
otherDbConn.Close()

Alternative Solution: Forceful Client Disconnection

If you cannot connect to another database or prefer not to, you can forcibly disconnect all clients from the database you wish to drop. This requires superuser privileges and should be used with caution.

For Postgres versions below 9.2:

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

For Postgres versions 9.2 and above:

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

After forcibly disconnecting all clients, you can connect to a different database and execute the DROP DATABASE command.

The above is the detailed content of Why Can't I Drop an Open Database in Postgres?. For more information, please follow other related articles on the PHP Chinese website!