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

Question

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.

Parth Desai · Accepted Answer · 2019-04-03 05:23:23Z

96

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.

edited Apr 3, 2019 at 5:23

answered Apr 8, 2016 at 15:04

Parth Desai

1,85914 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

t_io Over a year ago

Since 9.2 procpid needs to be replaced with pid SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

Lord Elrond Over a year ago

Since postgresql 13 you can use DROP DATABASE mydb WITH (FORCE); instead

Eugene Gr. Philippov Over a year ago

@LordElrond psql (13.3 (Debian 13.3-1.pgdg100+1)) Type "help" for help. dbname=# drop database dbname with (force); ERROR: cannot drop the currently open database dbname=#

Lord Elrond Over a year ago

@EugeneGr.Philippov you will still need to switch your connection to another database.

Thomas Over a year ago

Can't drop the database if you are not the owner.

|

simibac · Accepted Answer · 2020-06-10 06:17:50Z

57

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.

answered Jun 10, 2020 at 6:17

simibac

8,8345 gold badges43 silver badges60 bronze badges

Comments

bfontaine · Accepted Answer · 2023-01-19 11:37:30Z

46

To drop the database:

\c postgres

Then DROP DATABASE your_database works

edited Jan 19, 2023 at 11:37

bfontaine

21.4k16 gold badges88 silver badges128 bronze badges

answered Aug 2, 2021 at 1:39

Susobhan Das

1,31013 silver badges25 bronze badges

2 Comments

raphael.oester Over a year ago

straightforward answer that simply resolves the problem without talking too much i approve

MrIzzat Over a year ago

If an error is raised saying there are other sessions using the database, add the keyword WITH (force) to the drop database sql call.

GCru · Accepted Answer · 2020-09-15 20:47:42Z

8

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.

edited Sep 15, 2020 at 20:47

answered Sep 5, 2020 at 21:11

GCru

5166 silver badges8 bronze badges

Comments

kandarp sharma · Accepted Answer · 2022-06-02 21:12:53Z

7

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

answered Jun 2, 2022 at 21:12

kandarp sharma

711 silver badge1 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

cigien · Accepted Answer · 2022-03-08 16:21:41Z

4

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

edited Mar 8, 2022 at 16:21

cigien

61.2k11 gold badges86 silver badges124 bronze badges

answered Mar 8, 2022 at 13:56

Kevin Kyei

5857 silver badges13 bronze badges

Comments

DanteArceneaux · Accepted Answer · 2022-07-02 08:59:10Z

3

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.

answered Jul 2, 2022 at 8:59

DanteArceneaux

1091 silver badge8 bronze badges

Comments

Hrvoje · Accepted Answer · 2019-11-13 16:06:11Z

1

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.

answered Nov 13, 2019 at 16:06

Hrvoje

15.4k11 gold badges104 silver badges121 bronze badges

Comments

asmigliero · Accepted Answer · 2025-07-21 09:39:29Z

0

According to the documentation, connect to the standard system database, template1, and then drop your db.

DROP DATABASE: cannot be executed on the currently open database

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.

A complete example with a docker container:

docker compose exec <...> sh

Enter PostgresSQL terminal on template1 db

psql -U postgres -d template1

drop database <...> still returned an error but following worked:

drop database <...> WITH (FORCE);

edited Jul 21 at 9:39

answered Jul 21 at 9:33

asmigliero

12 bronze badges

Comments

Hamed Damirchi · Accepted Answer · 2022-01-06 17:45:46Z

-1

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

answered Jan 6, 2022 at 17:45

Hamed Damirchi

1031 silver badge7 bronze badges

2 Comments

Mark Over a year ago

This still gives me ERROR: cannot drop the currently open database

Baris Senyerli Over a year ago

my database still exist.

Collectives™ on Stack Overflow

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

10 Answers 10

7 Comments

Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

7 Comments

Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related