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.

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;