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.

Mountainside

Are you connected to the database you are trying to drop? – Seep

Yes. I also tried deleting the db with pgadmin. facing the same error – Mountainside

you need to connect to a different database. You can't drop the database you are connected to – Seep

How do I do that? – Mountainside

The same way you connect to that database, just provide a different database name. – Seep

Thanks. I did not know that. This worked – Mountainside

Answer 1

1. Make sure no users can connect to your database (superuser privileges, superusers still can connect):

   ALTER DATABASE mydb CONNECTION LIMIT 0; 
   

2. Force disconnection of all clients connected to this database (database owner privileges):

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

3. Then use your drop command to delete the Database (database owner privilege):

   DROP DATABASE mydb;
   

Dr

Hi. can you explain the first part more clearly. I am new to sql – Mountainside

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 – Dr

And afterwards, I suppose you have to unlimit with "ALTER DATABASE mydb CONNECTION LIMIT -1;" – Shellfish