Thread: 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.