Thread: Temporary disable autovacuum in pgsql 8.1.0

We are in the process of upgrading our 400GB database from PostgreSQL
7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum
process was slowing things down significantly. I should have disabled it
in postgresql.conf before starting the restore (duh).
Not wanting to restart the restore, I searched for a method of disabling
autovacuum on the fly. I found the solution based on the hints I found
at http://archives.postgresql.org/pgsql-patches/2004-06/msg00414.php

This works because pg_restore will create all tables before it starts
filling them. Resetting the pg_autovacuum table is a simple truncate.
Perhaps it will help someone with the same problem.

insert into pg_autovacuum (
            vacrelid,
            enabled,
            vac_base_thresh,
            vac_scale_factor,
            anl_base_thresh,
            anl_scale_factor,
            vac_cost_delay,
            vac_cost_limit)
select a.oid,
       false,
       current_setting('autovacuum_vacuum_threshold')::int,
       current_setting('autovacuum_vacuum_scale_factor')::real,
       current_setting('autovacuum_analyze_threshold')::int,
       current_setting('autovacuum_analyze_scale_factor')::real,
       current_setting('autovacuum_vacuum_cost_delay')::int,
       current_setting('autovacuum_vacuum_cost_limit')::int
from            pg_class a
inner join      pg_stat_all_tables b
on              a.oid=b.relid
left outer join pg_autovacuum c
on              a.oid = c.vacrelid
where           a.relkind = 'r'
and             schemaname not like 'pg_temp_%%'
and             a.oid not in (select distinct vacrelid from
                              pg_autovacuum);

update pg_autovacuum
set    enabled=false
where  enabled;

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. |     www.dna-portal.net
Bazarstraat 44a    |  www.trust-factory.com
2518AK The Hague   |  Phone: +31 70 3620684
The Netherlands    |  Fax  : +31 70 3603009
-------------------------------------------

Richard van den Berg <richard.vandenberg@trust-factory.com> writes:
> We are in the process of upgrading our 400GB database from PostgreSQL
> 7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum
> process was slowing things down significantly. I should have disabled it
> in postgresql.conf before starting the restore (duh).
> Not wanting to restart the restore, I searched for a method of disabling
> autovacuum on the fly. I found the solution based on the hints I found
> at http://archives.postgresql.org/pgsql-patches/2004-06/msg00414.php

Couldn't you have just set autovacuum = off in postgresql.conf?  (Unlike
some other things, this setting can be changed after postmaster start.)

            regards, tom lane

Richard van den Berg <richard.vandenberg@trust-factory.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote on 06-12-2005 15:49:28:
>> Couldn't you have just set autovacuum = off in postgresql.conf?  (Unlike
>> some other things, this setting can be changed after postmaster start.)

> If that is true, the documentation at
> http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
> should be updated:

> autovacuum (boolean)
> Controls whether the server should start the autovacuum subprocess. This
> is off by default. stats_start_collector and stats_row_level must also be
> on for this to start. This option can only be set at server start or in
> the postgresql.conf file.

That documentation is exactly correct.  Note that it doesn't say "can
only be set at server start" full stop.

            regards, tom lane