dropdb: database removal failed: active sessions

On Mon, 2011-08-29 at 15:22 -0400, JD Wong wrote:

Hi,

I cannot dropdb. Postgres throws me:
dropdb: database removal failed: ERROR: database "database1" is being
accessed by other users
DETAIL: There are 1 other session(s) using the database.

the "select datname,current_query,query_start from pg_stat_activity ;" query
gives me:

datname |
current_query |
query_start
-------------------------+--------------------------------------------------------------------------------------------------------------------------+-------------------------------
database2 | CREATE INDEX location_object_bioseg ON location
USING gist (locatedonid, bioseg_create(intermine_start, intermine_end)); |
2011-08-29 14:43:40.856594-04
database3 | <IDLE>
|
2011-08-29 15:08:17.469927-04
database4 | <IDLE>
|
2011-08-29 15:07:09.484543-04
database3 | <IDLE>
|
2011-08-29 15:07:09.901601-04
database2 | select datname,current_query,query_start from
pg_stat_activity ; |
2011-08-29 15:12:39.811168-04
database3 | <IDLE>
|
2011-08-29 15:08:17.476254-04
database3 | <IDLE>
|
2011-08-29 15:07:10.422579-04
database4 | <IDLE>
|
2011-08-29 15:07:10.515946-04
database1 | <IDLE>
|
2011-08-29 15:07:31.423596-04

Is the drop being prevented by the active query from database2?

No, by the IDLE one from database1.

If not how
can I kill the <IDLE> query that must be preventing deletion.

You have to use the pg_terminate_backend on this connection.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

On 08/29/11 12:22 PM, JD Wong wrote:

I cannot dropdb. Postgres throws me:
dropdb: database removal failed: ERROR: database "database1" is being
accessed by other users
DETAIL: There are 1 other session(s) using the database.

the "select datname,current_query,query_start from pg_stat_activity ;"
query gives me:

datname |
current_query
| query_start
-------------------------+--------------------------------------------------------------------------------------------------------------------------+-------------------------------
...
database1 | <IDLE>

| 2011-08-29 15:07:31.423596-04

Is the drop being prevented by the active query from database2? If
not how can I kill the <IDLE> query that must be preventing deletion.

any connection to the database, even idle, will prevent a drop database.

try...

select pg_terminate_backend(procpid) from pg_stat_activity where
datname = 'database1';

that will snuff those processes. then you can drop the database,
assuming the clients don't immediately reconnect.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Mon, Aug 29, 2011 at 2:39 PM, John R Pierce <pierce@hogranch.com> wrote:

any connection to the database, even idle, will prevent a drop database.

try...

   select pg_terminate_backend(procpid) from pg_stat_activity where datname
= 'database1';

that will snuff those processes.  then you can drop the database, assuming
the clients don't immediately reconnect.

If you lose your connection at this point you'll know who was
connected to that database as well.

Yes, I have done that before.