How to Drop a Currently Connected PostgreSQL Database?

Error in Dropping Currently Connected Postgres Database

When attempting to drop the database that you're currently connected to in Postgres, you may encounter the following error:

pq: cannot drop the currently open database

This error arises because database drops require a closed connection to the database being removed. The confusion lies in the inability to execute DROP DATABASE using dbConn.Exec if the connection is closed.

Solution

The recommended approach to dropping a currently connected database is to connect to a different database and execute the DROP DATABASE command from there. This is because the connection to the database being dropped becomes invalid upon its deletion.

For example:

import "github.com/lib/pq"

...

func dropDatabase(dbName string) error {
    // Connect to a different database (e.g., template1)
    otherConn, err := pq.Open("other_conn_string")
    if err != nil {
        return err
    }
    defer otherConn.Close()
    
    // Drop the desired database from the other connection
    _, err = otherConn.Exec(fmt.Sprintf(`DROP DATABASE %s;`, dbName))
    return err
}

Alternative Approach (Force Disconnection)

In scenarios where a different client is connected to the database being dropped and you require urgent removal, you can forcibly disconnect all clients from the target database using the following commands (requirements vary depending on PostgreSQL version):

-- PostgreSQL < 9.2
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb';

-- PostgreSQL >= 9.2
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

Note: Execute these commands with superuser privileges. Once the clients are forcibly disconnected, you can connect to a different database and execute the DROP DATABASE command.

The above is the detailed content of How to Drop a Currently Connected PostgreSQL Database?. For more information, please follow other related articles on the PHP Chinese website!