On Mar 11, 2006, at 2:44 PM, Matthew T. O'Connor wrote:
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.
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.
--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC
http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)
