Thread: Autovacuum Daemon Disrupting dropdb?

Autovacuum Daemon Disrupting dropdb?

From
"Thomas F. O'Connell"
Date:
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)


Re: Autovacuum Daemon Disrupting dropdb?

From
"Matthew T. O'Connor"
Date:
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

Re: Autovacuum Daemon Disrupting dropdb?

From
"Thomas F. O'Connell"
Date:
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)

Re: Autovacuum Daemon Disrupting dropdb?

From
Tom Lane
Date:
"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

Re: Autovacuum Daemon Disrupting dropdb?

From
"Thomas F. O'Connell"
Date:
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)

Re: Autovacuum Daemon Disrupting dropdb?

From
Tom Lane
Date:
"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

Re: Autovacuum Daemon Disrupting dropdb?

From
Stuart Bishop
Date:
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

Re: Autovacuum Daemon Disrupting dropdb?

From
"Thomas F. O'Connell"
Date:
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)