Why am I not able to drop postgres database

Question

I am trying to drop a postgres database. But I am always getting a

There is 1 other session using the database

error.

I tried stopping all the sessions with this command

select pg_terminate_backend(pid) from pg_stat_activity where datname='my_database';

this was the result

pg_terminate_backend
----------------------
(0 rows)

and then I used this command

DROP DATABASE my_database;

This is the entire error message

ERROR:  database "my_database" is being accessed by other users
DETAIL:  There is 1 other session using the database.

I am using postgres 12.

Yes. I also tried deleting the db with pgadmin. facing the same error — Sashaank, Sep 23 '20 at 08:33
you need to connect to a _different_ database. You can't drop the database you are connected to — , Sep 23 '20 at 08:34
The same way you connect to that database, just provide a different database name. — , Sep 23 '20 at 08:35

score 9 · Accepted Answer · edited Sep 23 '20 at 09:16

9

Make sure no users can connect to your database (superuser privileges, superusers still can connect):
```
ALTER DATABASE mydb CONNECTION LIMIT 0; 
```
Force disconnection of all clients connected to this database (database owner privileges):
```
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb';
```
Then use your drop command to delete the Database (database owner privilege):
```
DROP DATABASE mydb;
```

edited Sep 23 '20 at 09:16

Laurenz Albe

answered Sep 23 '20 at 08:33

Ankit Das

Hi. can you explain the first part more clearly. I am new to sql – Sashaank Sep 23 '20 at 08:34
The first part makes sure that no one can connect to your database(except the SuperUsers), so you are limiting the connections for your DB, its optional, but if you users keep using the DB and you dont want them to , then you can use it and restrict users from connecting to the DB – Ankit Das Sep 23 '20 at 08:36
And afterwards, I suppose you have to unlimit with "ALTER DATABASE mydb CONNECTION LIMIT -1;" – Carlitos_30 Oct 02 '22 at 00:14

1 Answers1