Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EB90B2E0046 for ; Mon, 19 Nov 2007 19:41:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03114-07 for ; Mon, 19 Nov 2007 19:41:27 -0400 (AST) Received: from outbound-mail-32.bluehost.com (outbound-mail-32.bluehost.com [69.89.18.152]) by postgresql.org (Postfix) with SMTP id C4D732E0044 for ; Mon, 19 Nov 2007 19:41:26 -0400 (AST) Received: (qmail 25821 invoked by uid 0); 19 Nov 2007 23:41:24 -0000 Received: from unknown (HELO box54.bluehost.com) (69.89.20.54) by mailproxy2.bluehost.com with SMTP; 19 Nov 2007 23:41:24 -0000 Received: from vdsl-151-118-146-205.dnvr.qwest.net ([151.118.146.205] helo=new-host.home) by box54.bluehost.com with esmtp (Exim 4.68) (envelope-from ) id 1IuGF2-0006qV-OC for pgsql-admin@postgresql.org; Mon, 19 Nov 2007 16:41:24 -0700 From: Kevin Kempter To: pgsql-admin@postgresql.org Subject: Re: Get a list of ALL tables Date: Mon, 19 Nov 2007 16:41:10 -0700 User-Agent: KMail/1.9.6 (enterprise 0.20070907.709405) References: <200711191524.47852.kevin@kevinkempterllc.com> <8053.1195514955@sss.pgh.pa.us> In-Reply-To: <8053.1195514955@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200711191641.10792.kevin@kevinkempterllc.com> X-Identified-User: {840:box54.bluehost.com:kevinkem:kevinkempterllc.com} {sentby:bopbeforesmtp 151.118.146.205 authed with kevinkempterllc.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.495 tagged_above=0 required=5 tests=AWL=-0.636, BAYES_50=0.001, DNS_FROM_OPENWHOIS=1.13 X-Spam-Level: X-Archive-Number: 200711/181 X-Sequence-Number: 27424 Sorry, I meant to send this to the list: We have a handful of tables that are quite large and take several hours to= =20 vacuum. We're managing these large tables by using cron via a schedule that= =20 accounts for system load. I want to pull the list of all tables and exclude= =20 these large tables from the list, then run the vacuum on the remaining tabl= es=20 once a week to avoid transaction ID wraparound failures. /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? > > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0regards, tom la= ne 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