Postgresql Kill Existing connection/session

• This will drop existing connections except for yours
• Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

PostgreSQL 9.2 and above:
In PostgreSQL 9.2 and above, to disconnect everything except your session from the database you are connected to:

First we need to Check what database do you want drop:

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 account    | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sales      | u2       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy2 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
(8 rows)

To destroy the database demo using the server on host eden, port 5000, with verification and a peek at the underlying command:

[postgres@r1 ~]$ dropdb -p 5432 -h r1 -i -e sales
Database "sales" will be permanently removed.
Are you sure? (y/n) Y
Please answer "y" or "n".
Are you sure? (y/n) y
Password: 
DROP DATABASE sales;
dropdb: database removal failed: ERROR:  database "sales" is being accessed by other users
DETAIL:  There is 1 other session using the database.
[postgres@r1 ~]$ 

solution:

• Above statement throws error becouse  database "sales" is being accessed by other users We cannot drop a database that has any open connections/session, including our own connection from psql or pgAdmin III. We must switch to another database or template1 if we want to delete the database we are currently connected to. Thus, it might be more convenient to use the program dropdb instead which is a wrapper around this command.
• another way is we can kill the session using following command

postgres=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'sales' AND pid <> pg_backend_pid();
 pg_terminate_backend 
----------------------
 t
(1 row)

after terminated above session you can drop the database:

[root@r1 ~]# dropdb -p 5432 -h r1 -i -e sales
bash: dropdb: command not found
[root@r1 ~]# su - postgres
[postgres@r1 ~]$ dropdb -p 5432 -h r1 -i -e sales
Database "sales" will be permanently removed.
Are you sure? (y/n) y
Password: 
DROP DATABASE sales;
[postgres@r1 ~]$ 

then check database is whether droped or not ollwing command:

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 account    | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy2 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
(7 rows)

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 you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.
Note the renaming of the procpid column to pid

Example: