Thread: Autovacuum Daemon Disrupting dropdb?
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. -- 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)
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
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)
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I suppose you could instead:
> connect to local postmaster
> disable autovacuum
> pg_dump remotedb
> dropdb localdb
> pg_restore remotedb.pgd
> enable autovacuum
For a "real" solution, perhaps DROP 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.
regards, tom lane
On Mar 11, 2006, at 4:13 PM, Tom Lane wrote: > For a "real" solution, perhaps DROP 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 --force 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. -- 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)
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> On Mar 11, 2006, at 4:13 PM, Tom Lane wrote:
>> For a "real" solution, perhaps DROP 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 --force 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.
regards, tom lane
Tom Lane wrote:
> "Thomas F. O'Connell" <tfo@sitening.com> writes:
>> On Mar 11, 2006, at 4:13 PM, Tom Lane wrote:
>>> For a "real" solution, perhaps DROP 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 --force 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.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
Attachment
In an interesting epilogue to this thread, I just encountered something unusual. Since having disabled autovacuum in the cluster in which it was preventing dropdb from working (because autovacuum the autovacuum daemon was behaving as a user accessing the database), dropdb has been working fine. Yesterday, though, I created a new cluster to house an on-line backup from a production server. The new cluster includes a postgresql.conf from the production server in which autovacuum was enabled. From the logs of the new cluster, autovacuum began processing as soon as the on-line backup recovery process was complete. Then, last night, my dropdb failed for the first time since having disabled it in my original postgres cluster on this server with a warning that users were accessing the database. Is it possible for an autovacuum daemon in a given cluster to have an impact on all postgres clusters running on a server? -- 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)