Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 04CF52E0032 for ; Mon, 19 Nov 2007 20:26:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 18189-02 for ; Mon, 19 Nov 2007 20:26:20 -0400 (AST) Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.246]) by postgresql.org (Postfix) with ESMTP id 337512E003C for ; Mon, 19 Nov 2007 20:26:25 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c36so345215ana for ; Mon, 19 Nov 2007 16:26:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=8ZCGQlaPtajKBGAC605fYJfCa0wBc1h7SLXUd+dmqNw=; b=fubwMF9lsTflmq6Ty5/c9TgiiKqHHmp2cN9+AMv8H5uWs1DxuoUJ/JCujgmUvAH8leFYT8aO/oYRWtFzH1EmjvaOWR67JIjxc3uVDw2flk97AloznqCchjp7cPB/tuiWYcnxvGCGAcjifX9+M/wWXakqKSMNHmu6HltEnZIbkMw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=lAV8zG1QQFevQwgFC42MZVutcWiSJPkyiH6uloeChwPq1veF6uJhLKuFancMKu4huLdI9aARIIneWAcMgjBmHlxHQCutIxRjJ/KpLxVCsQgYwkjvhXOcw39/WxRdrMLRJnIVX3neSgO1gvaALsImLrI2FtmNMNrSFkh5p4c833c= Received: by 10.142.212.19 with SMTP id k19mr1368843wfg.1195518383118; Mon, 19 Nov 2007 16:26:23 -0800 (PST) Received: by 10.142.148.18 with HTTP; Mon, 19 Nov 2007 16:26:23 -0800 (PST) Message-ID: <1c66bda80711191626w581ce731wbf862c92d92648cc@mail.gmail.com> Date: Tue, 20 Nov 2007 13:26:23 +1300 From: adey To: "Kevin Kempter" Subject: Re: Get a list of ALL tables Cc: pgsql-admin@postgresql.org In-Reply-To: <200711191641.10792.kevin@kevinkempterllc.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10166_30475406.1195518383162" References: <200711191524.47852.kevin@kevinkempterllc.com> <8053.1195514955@sss.pgh.pa.us> <200711191641.10792.kevin@kevinkempterllc.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/182 X-Sequence-Number: 27425 ------=_Part_10166_30475406.1195518383162 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline This usually does it for me:- select * from -- information_schema.tables pg_catalog.pg_tables --WHERE -- table_schema = 'public' --ORDER BY -- table_name On 11/20/07, 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. > > > /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 > ------=_Part_10166_30475406.1195518383162 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
This usually does it for me:-
 

select

*

from

-- information_schema.tables

pg_catalog

.pg_tables

--WHERE

-- table_schema = 'public'

--ORDER BY

-- table_name



 
On 11/20/07, Kevin Kempter <kevin@kevinkempterllc.com> 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.


/Kevin




> Kevin Kempter <kevin@kevinkempterllc.com> 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 <kevin@kevinkempterllc.com> 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

------=_Part_10166_30475406.1195518383162--