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

Hi all,

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

This means I need to restart the postmaster, but the server contains
many DBs, of which some may actually be in use. How can I tell postgres
that the database 'foo' should be marked as not in use / clear or drop
any open connections / etc?

Thanks all!

Madi

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

Sysadmin wrote:
> Hi all,
>
> 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
>
> This means I need to restart the postmaster, but the server contains
> many DBs, of which some may actually be in use. How can I tell postgres
> that the database 'foo' should be marked as not in use / clear or drop
> any open connections / etc?

If you connect to "foo" and run "select * from pg_stat_activity;" what
does it show?

Have you tried changing pg_hba.conf (and reloading PG and waiting for
current connections to terminate of course) to deny access to foo before
running your commands?

Is there a pg_dumpall (or anything else that might access that db at the
PG superuser level) running at the time?

Cheers,
Steve

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

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:"

Steve Crawford wrote:
> Sysadmin wrote:
>> Hi all,
>>
>> 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
>>
>> This means I need to restart the postmaster, but the server contains
>> many DBs, of which some may actually be in use. How can I tell postgres
>> that the database 'foo' should be marked as not in use / clear or drop
>> any open connections / etc?
>
> If you connect to "foo" and run "select * from pg_stat_activity;" what
> does it show?
>
> Have you tried changing pg_hba.conf (and reloading PG and waiting for
> current connections to terminate of course) to deny access to foo before
> running your commands?
>
> Is there a pg_dumpall (or anything else that might access that db at the
> PG superuser level) running at the time?
>
> Cheers,
> Steve

Thanks, Steve!

Turns out a daemon was indeed still connected to the database... that
command pointed that out, and I assure you I gave myself a decent smack
in the forehead for it. :)

Madi