CODEWITHSUNDEEP
postgresqlgo
b0xxed1n
b0xxed1n

Reputation: 2343

Postgres drop database error: pq: cannot drop the currently open database

I'm trying to drop the database I'm currently connected to like so, but I'm getting this error:

pq: cannot drop the currently open database

I don't really understand how I'm expected to drop the database if I have to close my connection, because then I don't think I will be able to use dbConn.Exec to execute my DROP DATABASE statement?

dbConn *sql.DB

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

  return dbConn.Close()
}

I guess I could connect to a different database and then execute it on that connection, but I'm not even sure if that'd work, and it seems really weird to have to connect to a new database just to drop a different database. Any ideas? Thanks.

Upvotes: 109

Views: 223463

Answers (9)

Susobhan Das
Susobhan Das

Reputation: 1164

To drop the database:

\c postgres

Then DROP DATABASE your_database works

Upvotes: 43

DanteArceneaux
DanteArceneaux

Reputation: 109

If you are using DBeaver make sure you are not connected to the database you are trying to drop. Go to edit connections and look at the database name.

Switch the connection to a different database and then drop the database you wish.

Upvotes: 3

kandarp sharma
kandarp sharma

Reputation: 71

Just Connect to a different database using \c db_name; and then drop the required database using drop database db_name;

Upvotes: 7

Kevin Kyei
Kevin Kyei

Reputation: 585

It's simple, just connect to another database \c database2. Once connected execute the drop database command while connected to the other database.

Upvotes: 4

Hamed Damirchi
Hamed Damirchi

Reputation: 103

you can force to drop database with: DROP DATABASE mydb WITH (FORCE)

Upvotes: -1

GCru
GCru

Reputation: 516

I am using PostgreSQL 12 and pgAdmin-4 in Windows 10. I had to use a combination of the above answers to drop a database, which I could not drop in pgAdmin because I was unable to close all open connections in pgAdmin.

Close pgAdmin-4.

In Windows command line, assuming my server's name is postgres and my database is mydb:

C:\> psql -U postgres

I logged in with my server password.

I then closed all open connections to mydb:

postgres-# SELECT * FROM pg_stat_activity WHERE pg_stat_activity.datname='mydb';
postgres-# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

Finally, I successfully dropped mydb:

postgres-# DROP DATABASE mydb;

Now if I go back into pgAdmin-4 it is gone.

Upvotes: 8

simibac
simibac

Reputation: 8610

If you encounter this problem in IntelliJ, change the schema with the following dropdown to postgres.

After that, I was able to drop a db.

enter image description here

Upvotes: 53

Hrvoje
Hrvoje

Reputation: 15212

None of this worked for me since I tried to do it through pgAdmin which kept database connections open as soon as I delete them.

Solution:

C:\Program Files\PostgreSQL\11\scripts\runpsql.bat

after you supply correct information you will be able to get pg command prompt, here you can just type:

dbdrop yourdatabase

After that you might still see database in pgAdmin but now you can just simply delete it with right click and DELETE/DROP option.

Upvotes: 1

Parth Desai
Parth Desai

Reputation: 1839

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.

Upvotes: 95

Related Questions