Home > Backend Development > Golang > How to Drop a Currently Connected PostgreSQL Database?

How to Drop a Currently Connected PostgreSQL Database?

Mary-Kate Olsen
Release: 2024-11-19 12:15:03
Original
733 people have browsed it

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
Copy after login

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
}
Copy after login

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';
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template