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