CODEWITHSUNDEEP
postgresql
codec
codec

Reputation: 8796

How to drop database which has some sessions open

I am trying to drop a database using the following command and I get an error

DROP DATABASE IF EXISTS mydb;
There are 5 other sessions using the database.

Is there any sql statement/sequence of queries which which drops database killing all the open sessions? I can only use commnand line.

I tried SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'mydb'; and also tried restarting postgres service which ideally should have worked but it did not.

Upvotes: 1

Views: 817

Answers (1)

codec
codec

Reputation: 8796

OK the followoing command worked for me great

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

Upvotes: 1

Related Questions