Re: Manually clearing "database "foo" is being accessed by other users"

Two vaguely helpful comments:

1. Sometimes this has happened to me when I unknowingly have two
terminal psql processes running on the same machine that I'm using to
connect to the server. Everyone but yourself may be off the database,
but there may be two instances of yourself. :)

2. I've dealt with the situation of how to kick all users off the
system by making the client software I write listen for a "disconnect
from the database now, dammit" NOTIFY (e.g. 'NOTIFY shutdown_foo_db')
and I have the client quit immediately upon receiving this
notification the next time they make a query. It's a crude kludge,
but usually after 5-10 minutes, everyone's off and I don't have to
restart/reload anything.

-- Ilan

On Sep 20, 2007, at 4:03 PM, Sysadmin wrote:

> Tom Lane wrote:
>> Sysadmin <linux(at)alteeve(dot)com> writes:
>>> I'm finding that routinely when I try to reload a database on a
>>> server
>>> where I know there are no connections to a given DB I get the error:
>>
>>> $ dropdb foo && createdb foo -O bar && psql foo -f /path/to/db.out
>>> dropdb: database removal failed: ERROR: database "foo" is being
>>> accessed by other users
>>
>> If it says there are other connections, then there really are other
>> connections --- there are not any known bugs in that logic.
>>
>> What I am wondering though is whether you are allowing for the
>> nonzero
>> exit time of a backend process. If the above is part of a script
>> that
>> was just doing something in database foo, then the drop could fail
>> because the backend that was serving that session is still
>> cleaning up.
>> The quickest solution is a 'sleep 1' (or so) before the dropdb.
>>
>> (FWIW, 8.3 will have some delay built in here to help mask this
>> issue.)
>>
>> regards, tom lane
>
> Thanks Tom,
>
> In this case no, it's not part of a script. I was doing this
> manually
> at the OS shell. This error has happened many times, and restarting
> the
> postgres server always clears it and lets me continue.
>
> Is there a way to tell Postgres to terminate any existing
> connections
> to a given DB to avoid the need to restart the entire server?
>
> Madi
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

Ilan Volow
"Implicit code is inherently evil, and here's the reason why:"