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
-------------------------------------------
