Kill sessions/connections in PostgreSQL

PostgreSQL

Wanna drop database in postgresql you may saw the following message if some connections is still connect to database

ERROR: database "my_db" is being accessed by other users DETAIL: There is 1 other session using the database.

 

How to do do ?

You can use pg_terminate_backend() to kill a connection. You have to be superuser to use this function. This works on all operating systems the same.

/* Step 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'my_db';

/* Step 2: use ALTER DATABASE. Superusers still can connect! */
ALTER DATABASE my_db CONNECTION LIMIT 0;

/* Step 3: pg_terminate */
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_db';

/* Step 4: terminate */
DROP DATABASE my_db;


Leave a Reply

Your email address will not be published. Required fields are marked *