Thread: 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.