Обсуждение: 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@alteeve.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@alteeve.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

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