Drop PostgreSQL database if there are active connections to it

Scenario: We want to drop database(assume database name as company_new) in PostgreSQL. But, the database contains more active sessions(idle). When we tried to drop database, we will receive following error message.

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

Solution:

Step1: Kill(terminate) connections from that particular database using query.

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

Output:

pg_terminate_backend ---------------------- t (1 row)

Step2: Now drop database:

drop database company_new; DROP DATABASE

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: