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

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/