I administer a network where a postgres database on one machine is
nightly dumped to another machine where it is restored (for
verification purposes) once the dump completes. The process is roughly:
pg_dump remotedb
dropdb localdb
pg_restore remotedb.pgd
We recently upgraded the system to 8.1.x and enabled autovacuum and
the dropdb command has recently begun failing periodically. Is this
because the autovacuum daemon runs it technically runs as a user and
can thus prevent dropping a database? There is no public application
that accesses the database. I note that the autovacuum daemon
requires a superuser_reserved_connections slot.

Mar 11, 2006, at 2:44 PM, Matthew T. 'Connor wrote:

Thomas F. 'Connell wrote:
>I administer a network where a postgres database on one machine is
>nightly dumped to another machine where it is restored (for
>verification purposes) once the dump completes. The process is
>roughly:
>pg_dump remotedb
>dropdb localdb
>pg_restore remotedb.pgd
>We recently upgraded the system to 8.1.x and enabled autovacuum
>and the dropdb command has recently begun failing periodically. Is
>this because the autovacuum daemon runs it technically runs as a
>user and can thus prevent dropping a database? There is no public
>application that accesses the database. I note that the autovacuum
>daemon requires a superuser_reserved_connections slot.
>
First off, are you sure it's autovacuum that is causing the failure?

The autovacuum connects to each database to look around and decided
if any work should be done, so it's certainly possible that every
once in a while, autovacuum just happens to be connected to the
database you want to drop when you want to drop it. With the
integration of autovacuum in 8.1, you can now tell autovacuum to
ignore tables, but I don't think there is a way to tell it to avoid
a particular database, but might be a reasonable feature addition.

I suppose you could instead:

connect to local postmaster
disable autovacuum
pg_dump remotedb
dropdb localdb
pg_restore remotedb.pgd
enable autovacuum

This isn't totally bulletproof, but assuming that autovacuum never
really spends much time in the database to be dropped it should be
reaonably safe.

I'm not positive, but there aren't many other suspects. Is there an
easy way to disable autovacuum automatically? I'm sure I could
inplace edit postgresql.conf and reload or something.

For the short term, I'm just disabling it altogether on the server
that holds the dump and does the restoration because performance is
not really an issue.

Mar 11, 2006, at 4:13 PM, Tom Lane wrote:

For a "real" solution, perhaps DRP DATABASE could somehow look to
determine if there's an autovac daemon active in the target database,
and if so send it a SIGINT and wait for it to go away.

In general, it also seems like a option or something similar
would be reasonable for dropdb because the state of the database in
terms of user activity wouldn't seem to matter a whole lot if the
intent is to drop it.

Tom Lane wrote:
"Thomas F. 'Connell" <tfo (AT) sitening (DOT) comwrites:
>Mar 11, 2006, at 4:13 PM, Tom Lane wrote:
For a "real" solution, perhaps DRP DATABASE could somehow look to
determine if there's an autovac daemon active in the target database,
and if so send it a SIGINT and wait for it to go away.

>In general, it also seems like a option or something similar
>would be reasonable for dropdb because the state of the database in
>terms of user activity wouldn't seem to matter a whole lot if the
>intent is to drop it.

except to the processes connected to it.

If we trusted selective SIGTERM we could imagine sending that to
non-autovac processes connected to the target database, but we don't
really. In any case, killing a database that has active users seems
like a pretty large-caliber foot-gun to me; that condition suggests
*very* strongly that the database is not so idle as all that.

I would find this useful. We have a large test suite that drops and
recreates a test database as required to maintain test isolation. Two
problems we have are:
- If a test fails to close all of its connections, the rest of
the tests are victimized as the database cannot be dropped.
- If you close all your connections and immediately attempt to drop the
database, it will often fail as it appears that PostgreSQL is still
cleaning up the recently closed connections. I don't know if this is
a PostgreSQL issue or an issue on how our database driver closes
connections (psycopg1 for Python).

To work around the first issue, we have to examine pg_stat_activity for
process ids and kill any outstanding ones.

To work around he second issue, we attempt to drop a number of times with a
short sleep between each try. Which is rather 'icky.

I have similar issues I need to deal with on our staging server, which each
day automatically needs to have the database reset with a fresh dump of our
production database, code updates rolled out and schema and data migration
patches applied.