X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5C31011F6C83 for ; Fri, 31 Mar 2006 18:47:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51845-03 for ; Fri, 31 Mar 2006 18:47:36 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 1A59011F6C82 for ; Fri, 31 Mar 2006 18:47:34 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id B751956423; Fri, 31 Mar 2006 16:47:34 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Fri, 31 Mar 2006 16:47:33 -0600 Date: Fri, 31 Mar 2006 16:47:33 -0600 From: "Jim C. Nasby" To: "Matthew T. O'Connor" Cc: Gourish Singbal , "pgsql-admin@postgresql.org" Subject: Re: auto vacuuming Message-ID: <20060331224733.GX49405@pervasive.com> References: <674d1f8a0603290206u55f114e1y7bfc2f83696fbada@mail.gmail.com> <20060331174802.GS49405@pervasive.com> <20060331185037.GF14204@surnet.cl> <442DA0CA.1050901@zeut.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <442DA0CA.1050901@zeut.net> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060331:matthew@zeut.net::hhm4/fI1wEq6yMot:06i3s X-Hashcash: 1:20:060331:gourish@gmail.com::aw2xgBMoOVFF2m0I:00000000000000000000 0000000000000000000000001CsV X-Hashcash: 1:20:060331:pgsql-admin@postgresql.org::OSXPLj7skhdFPqTF:00000000000 0000000000000000000000002N5N X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200603/408 X-Sequence-Number: 21165 On Fri, Mar 31, 2006 at 04:36:10PM -0500, Matthew T. O'Connor wrote: > Alvaro Herrera wrote: > >Jim C. Nasby wrote: > >>On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote: > >>>we are using postgresql 8.1.3 and wanted to enable autovacuuming for only > >>>one of the many databases on the same postgresql cluster. > >>>pg_autovacuum seems to allow to ignore only a particular table and not > >>>the > >>>database since column vacrelid is OID from pg_class table Are there any > >>>suggestions to accomplish this tasks?. > >>Currently there is no way to exclude entire databases from autovacuum. > > > >IIRC in one of the original patches I had added a way to do it. It was > >rejected however; it was argued that there was another mechanism to > >disable it. I don't remember what it was though. The only possibility > >that comes to mind is "ALTER DATABASE SET autovacuum TO off" but I don't > >see why would it work. > > > I think the closest approximation of disabling autovacuum on a per > database basis is to connect to the database in question and perform: > > update pg_autovacuum set enabled = 'false'; > > This will prevent autovacuum from vacuuming or analyzing any of the > tables in the database, but will still check for XID wraparound. Problem with that is any time you add a table you'd need to re-run that command. Wouldn't be so bad if we had DDL triggers, but... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461