Обсуждение: Cannot kill autovacuum

When I set up a database with tons of data I forgot to check in the
.conf file that autovacuum was on.

Now it seems that the stat collection has prepared some tasks to be
done for autovacuum - from my experience I can tell that this will
take weeks or even months.

I disabled autovacuum in the conf file, shut down the database,
restarted it but the autovacuum process is comming up again and
again. If I kill the process it takes just some minutes to start
again to do it's jobs it has in memory.

So, my question should be quite simple - how do I shut down
autovacuum finally to not do anything more on this tables - I hope
the answer is also as simple, but until right now I didn't find any
solution for that.

I'm using 8.2.5 version.

Temporary(!) solution:

insert into pg_autovacuum (
    vacrelid,
    enabled,
    vac_base_thresh,
    vac_scale_factor,
    anl_base_thresh,
    anl_scale_factor,
    vac_cost_delay,
    vac_cost_limit,
    freeze_min_age,
    freeze_max_age
)
select    oid as vacrelid,
    false as enabled,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0
from    pg_class
where    relnamespace = 38048977
and    relkind = 'r';

I'm not 100% sure if this is a temporary solution, because I
definitly don't know the time when the autovacuum worker is going to
run again, but after 3-4 minutes there is no new autovacuum worker
running. It seems that autovacuum checks if the table is disabled
for autovacuum but it's not checking if the configuration variable
autovacuum is off?! That's really strange!

Well, one more question:
Is it possible to link vacrelid with a database in pg_database
instead of a table?


Aldor wrote:
> When I set up a database with tons of data I forgot to check in the
> .conf file that autovacuum was on.
>
> Now it seems that the stat collection has prepared some tasks to be
> done for autovacuum - from my experience I can tell that this will
> take weeks or even months.
>
> I disabled autovacuum in the conf file, shut down the database,
> restarted it but the autovacuum process is comming up again and
> again. If I kill the process it takes just some minutes to start
> again to do it's jobs it has in memory.
>
> So, my question should be quite simple - how do I shut down
> autovacuum finally to not do anything more on this tables - I hope
> the answer is also as simple, but until right now I didn't find any
> solution for that.
>
> I'm using 8.2.5 version.

Hint: When turning configuration for autovacuum to minimal settings
  they are recognized by the autovacuum process started from
postmaster. The setting "autovacuum = off" - which I tried to put
also into other areas of the conf file (to avoid that it is maybe
not parsed corretly) - is not recognized. I also checked pg_settings
and autovacuum is "off" - I also tried it with "false" - no change.

The autovacuum process is always started some seconds after
postmaster is started.

I can see in the pg_stat_activity that autovacuum is doing some
VACUUM on some of this tables.

Any hint what I can try to disable autovacuum?

Aldor wrote:
> Hint: When turning configuration for autovacuum to minimal settings
>   they are recognized by the autovacuum process started from
> postmaster. The setting "autovacuum = off" - which I tried to put
> also into other areas of the conf file (to avoid that it is maybe
> not parsed corretly) - is not recognized. I also checked pg_settings
> and autovacuum is "off" - I also tried it with "false" - no change.
>
> The autovacuum process is always started some seconds after
> postmaster is started.
>
> I can see in the pg_stat_activity that autovacuum is doing some
> VACUUM on some of this tables.
>
> Any hint what I can try to disable autovacuum?

If your database is in danger of Xid wraparound, you cannot prevent
autovacuum from starting.  When the autovacuum setting is off in
postgresql.conf, this is what's happening.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
Este mail se entrega garantizadamente 100% libre de sarcasmo.