pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Get a list of ALL tables
6+ messages / 5 participants
[nested] [flat]

* Get a list of ALL tables
@ 2007-11-19 22:24  Kevin Kempter <kevin@kevinkempterllc.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Kevin Kempter @ 2007-11-19 22:24 UTC (permalink / raw)
  To: pgsql-admin

Hi List;

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 ?


Thanks in advance...

/Kevin



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Get a list of ALL tables
@ 2007-11-19 23:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Kevin Kempter <kevin@kevinkempterllc.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2007-11-19 23:29 UTC (permalink / raw)
  To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Get a list of ALL tables
@ 2007-11-19 23:41  Kevin Kempter <kevin@kevinkempterllc.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 6+ messages in thread

From: Kevin Kempter @ 2007-11-19 23:41 UTC (permalink / raw)
  To: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Get a list of ALL tables
@ 2007-11-20 00:26  adey <adey11@gmail.com>
  parent: Kevin Kempter <kevin@kevinkempterllc.com>
  2 siblings, 0 replies; 6+ messages in thread

From: adey @ 2007-11-20 00:26 UTC (permalink / raw)
  To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin

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
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Get a list of ALL tables
@ 2007-11-20 01:55  Joshua D. Drake <jd@commandprompt.com>
  parent: Kevin Kempter <kevin@kevinkempterllc.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Joshua D. Drake @ 2007-11-20 01:55 UTC (permalink / raw)
  To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Get a list of ALL tables
@ 2007-11-20 10:30  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Kevin Kempter <kevin@kevinkempterllc.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Alvaro Herrera @ 2007-11-20 10:30 UTC (permalink / raw)
  To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin

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.

You can't do this in 8.1 -- in that release, the only way to advance the
wraparound safety point is to execute a database-wide vacuum.  You can't
exclude tables.  If you want to be able to do that you'll need to jump
to 8.2.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2007-11-20 10:30 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-19 22:24 Get a list of ALL tables Kevin Kempter <kevin@kevinkempterllc.com>
2007-11-19 23:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-19 23:41   ` Kevin Kempter <kevin@kevinkempterllc.com>
2007-11-20 00:26     ` adey <adey11@gmail.com>
2007-11-20 01:55     ` Joshua D. Drake <jd@commandprompt.com>
2007-11-20 10:30     ` Alvaro Herrera <alvherre@alvh.no-ip.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox