by

How to force-drop a postgresql database by killing off connection processes

Ever need to drop a postgresql database, but it would not let you because there are open connections to it (from a webapp or whatever)?

Quite annoying.  If on a production server, and other databases are being used, restarting postgresql is a last resort, because it generates downtime for your site (even if small).

I finally took the time to scratch around and find the answer.

As a super user, to list all of the open connections to a given database:

select * from pg_stat_activity where datname='YourDatabase';

As a superuser, to drop all of the open connections to a given database:

select pg_terminate_backend(procpid) from pg_stat_activity where datname=’YourDatabase’;

Or for 9.x, change `procpid` to `pid`

select pg_terminate_backend(pid) from pg_stat_activity where datname='YourDatabase';

—

Here are some references to the functions:

http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE

http://www.postgresql.org/docs/8.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE

7 thoughts on “How to force-drop a postgresql database by killing off connection processes”

  1. Your example is truncated on the right. what comes after the where clause?

    As part of my testing I need to force a connection to die….

    Thanks for the post and the reply. Very useful.

  3. Thanks for the query. If anyone took out the WHERE clause hoping to drop all connections, this only sometimes work. The reason is this will actually kill your current query. You could make a sub query to first sort by query_start time or perhaps a better way would just be to filter out anything that has “pg_stat_activity” in the curreny_query problem.

  6. Thank you for findings!

    If you need to kill all connections except the current, then query may be like:

    SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname=’YourDatabase’ AND procpid!=pg_backend_pid();

  7. Pingback: How to: Drop postgresql database through command line | SevenNet

Leave a comment