Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CA3042E0039 for ; Mon, 19 Nov 2007 21:55:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 29654-07 for ; Mon, 19 Nov 2007 21:55:46 -0400 (AST) Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id 8CE4F2E0032 for ; Mon, 19 Nov 2007 21:55:46 -0400 (AST) Received: from [192.168.1.4] (or-69-34-217-90.sta.embarqhsd.net [69.34.217.90]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id lAK1th1M012695 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 19 Nov 2007 17:55:44 -0800 Message-ID: <47423EA3.8000100@commandprompt.com> Date: Mon, 19 Nov 2007 17:55:47 -0800 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Thunderbird 1.5.0.13 (Windows/20070809) MIME-Version: 1.0 To: Kevin Kempter CC: pgsql-admin@postgresql.org Subject: Re: Get a list of ALL tables References: <200711191524.47852.kevin@kevinkempterllc.com> <8053.1195514955@sss.pgh.pa.us> <200711191641.10792.kevin@kevinkempterllc.com> In-Reply-To: <200711191641.10792.kevin@kevinkempterllc.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Mon, 19 Nov 2007 17:55:44 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/183 X-Sequence-Number: 27426 Kevin Kempter wrote: > Sorry, I meant to send this to the list: > > > We have a handful of tables that are quite large and take several hours to > vacuum. We're managing these large tables by using cron via a schedule that > accounts for system load. I want to pull the list of all tables and exclude > these large tables from the list, then run the vacuum on the remaining tables > once a week to avoid transaction ID wraparound failures. If I were you, I would use autovacuum for all tables *except* the high velocity tables. That will take care of the wrap concerns and will also allow you to be aggressive via cron on the tables that autovacuum isn't good enough for. Oh... and get up to 8.1.10 Joshua D. Drake > > > /Kevin > > > > >> Kevin Kempter writes: >>> I'm working with a client with several highly active 8.1.4 servers. I >>> want to run a weekly cron that will vacuum ALL db tables except those >>> with entries in pg_autovacuum (where we've setup manual/cron vacuums) in >>> order to eliminate transaction ID wraparound failure warnings (which >>> comes up fairly often). >>> >>> My question is: >>> will a select from pg_tables for each database in the cluster provide me >>> with a full table listing for the purposes of ensuring that all tables in >>> the db have been vacuumed or are there others I'll need to account for as >>> well ? >> Why are you insisting on inventing your own wheel for this, when >> "vacuum" without a parameter does that just fine? >> >> regards, tom lane > > > > > On Monday 19 November 2007 16:29:15 you wrote: >> Kevin Kempter writes: >>> I'm working with a client with several highly active 8.1.4 servers. I >>> want to run a weekly cron that will vacuum ALL db tables except those >>> with entries in pg_autovacuum (where we've setup manual/cron vacuums) in >>> order to eliminate transaction ID wraparound failure warnings (which >>> comes up fairly often). >>> >>> My question is: >>> will a select from pg_tables for each database in the cluster provide me >>> with a full table listing for the purposes of ensuring that all tables in >>> the db have been vacuumed or are there others I'll need to account for as >>> well ? >> Why are you insisting on inventing your own wheel for this, when >> "vacuum" without a parameter does that just fine? >> >> regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >