How to Drop a Postgres Database You're Currently Connected To?

Dropping a Connected Database in Postgres

You encountered an error while attempting to drop a database to which you are currently connected: "pq: cannot drop the currently open database." This error stems from the expected behavior in Postgres, which prohibits dropping a database with an active connection to it.

This behavior makes sense because dropping a database invalidates all open connections referencing it. To successfully drop a database, you must ensure no active connections exist.

Recommended Approach

The recommended approach is to connect to a different database and execute the DROP DATABASE command from that connection. This ensures that you don't have any open connections to the database you want to drop.

Forcibly Disconnecting Clients

In situations where other clients are connected to the database you need to drop, you can forcibly disconnect them. Note that this requires superuser privileges.

For PostgreSQL versions prior to 9.2:

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

For PostgreSQL versions 9.2 and above:

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

Once all clients have been forcibly disconnected, you can connect to a different database and execute the DROP DATABASE command again.

Additional Note

In your specific code example, you attempt to close the connection after executing the DROP DATABASE command. This step is unnecessary and can lead to unexpected behavior. Simply connect to a different database before executing the DROP DATABASE command and you will successfully close the connection to the database you have dropped.

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