Leeladharan Achar - alias - leelu ~ blogging...hola mi amigos..'s Blog

Posted July 14, 2016   23795 views

Drop a PostgreSQL database if there are active connections to it

When you are trying to drop a PostgreSQL DB say "target_db" and you find error as below

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

If you get above error then there 1 active connection opened to target_db, so it cannot delete the selected database until the active connection is closed. So to delete the selected database first we need to close all existing connections to the selected database. This can be done with query as below

PostgreSQL 9.2 and above:

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

PostgreSQL 9.1 and below:

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

Once above query is executed. We can drop the target db with out any error by executing below query

    DROP DATABASE target_db

That's all, Thanks