Re: Autovacuum Daemon Disrupting dropdb?

Thomas F. O'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.


Matt