Find Active Session and Kill it on PostgreSQL

I happen to encounter a case when I want to drop a database and not allowed by system since there are an active session. I ended up having to restart database and the perform the drop. this is not ideal if there are more than 2 database or its already run on production, but here's how to do it


basically we can use pg_terminate_backend command to eliminate a session, and we can search the session id that is active on pg_stat_activity table, combines it and you can use the following commands

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

Change the 'TARGET_DB' with database you want to Drop.