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:
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?
Ilan Volow
"Implicit code is inherently evil, and here's the reason why:"