Re: Autovacuum Daemon Disrupting dropdb?

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)