Postgres ERROR: database “test” is being accessed by other users

Question

Getting the following error when trying to drop a PostgreSQL DB say "test"

postgres=# DROP DATABASE test;
ERROR:  database "test" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Answer 1

You can use pg_terminate_backend to kill open connections with a query:

PostgresVersion >=9.2

SELECT 
   pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE
   pg_stat_activity.datname = 'test'
AND pid <> pg_backend_pid()

PostgresVersion <9.2

SELECT 
   pg_terminate_backend(pg_stat_activity.procpid)
FROM 
   pg_stat_activity
WHERE 
   pg_stat_activity.datname = 'test'
AND procpid <> pg_backend_pid();

where 'test' is your databasename

For PostgreSQL Version >= 13

One can use:

DROP DATABASE mydb WITH (FORCE);

Ref: https://www.postgresql.org/docs/current/sql-dropdatabase.html

Answer 2

1) Run the following command and findout the pid postgres=# select * from pg_stat_activity where datname='test';

datid | datname | pid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port -------+---------+---------+----------+----------+---------------+---------+------------+-------------------------------+-------------------------------+-------------+------------- 28091 | test | 8481 | 10 | postgres | | f | | 2008-11-12 09:12:50.277096+00 | 2008-11-12 09:11:10.328231+00 | 127.0.0.1 | 43152

2) kill -9 8481 (Here the pid is 8481)

3) Now run

postgres=# drop database test;

DROP DATABASE