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
