Обсуждение: vacuum all but system tables

Hello.

I'd like to make a script to automatically vacuum all my DBs nightly. And
I'd like to skip system tables, starting with pg_*.

I've seen in the list archives somebody's solution using regexps,
something like:

$> vacuum analyze !~ ^pg_;


It doesn't work for me, and I cannot find the original post I've seen some
time ago.

I'm running:
`PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3'.


Maybe someone came across with this and could help?


--

Victor Yegorov

> I'd like to make a script to automatically vacuum all my DBs nightly. And
> I'd like to skip system tables, starting with pg_*.

If you run the process as non-super user, it must skip your system tables.  
On the other hand, if you run it as a super user, it must be done table 
wise from your script. 

regards,
bhuvaneswaran

"Victor Yegorov" <viy@pirmabanka.lv> writes:
> I'd like to make a script to automatically vacuum all my DBs nightly. And
> I'd like to skip system tables, starting with pg_*.

Um ... what in the world makes you think that's a good idea?  System
tables need maintenance too.
        regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [19.03.2003 16:32]:
> "Victor Yegorov" <viy@pirmabanka.lv> writes:
> > I'd like to make a script to automatically vacuum all my DBs nightly. And
> > I'd like to skip system tables, starting with pg_*.
>
> Um ... what in the world makes you think that's a good idea?  System
> tables need maintenance too.
>
>             regards, tom lane

Yes, of course they need.

May be I'll put my question in a different manner:

System tables are location-wide (I mean one set of tables for PostgreSQL
location) or each database has it's own set of system tables?

If second, I apologies for noising. If first, I'd like to have a separate
script for them.

--

Victor Yegorov

"Victor Yegorov" <viy@pirmabanka.lv> writes:
> May be I'll put my question in a different manner:

> System tables are location-wide (I mean one set of tables for PostgreSQL
> location) or each database has it's own set of system tables?

pg_database, pg_shadow, pg_group are shared, the rest are per-database.

These three are generally not so large that it's worth worrying about
vacuuming them extra times ...
        regards, tom lane

Użytkownik Victor Yegorov napisał:
> * Tom Lane <tgl@sss.pgh.pa.us> [19.03.2003 16:32]:
> 
>>"Victor Yegorov" <viy@pirmabanka.lv> writes:
>>
>>>I'd like to make a script to automatically vacuum all my DBs nightly. And
>>>I'd like to skip system tables, starting with pg_*.
>>
>>Um ... what in the world makes you think that's a good idea?  System
>>tables need maintenance too.
>>
>>            regards, tom lane
> 
> 
> Yes, of course they need.
> 
> May be I'll put my question in a different manner:
> 
> System tables are location-wide (I mean one set of tables for PostgreSQL
> location) or each database has it's own set of system tables?
> 
> If second, I apologies for noising. If first, I'd like to have a separate
> script for them.

They are location-wide. What's wrong with default vacuuming all 
databases at once nightly? Or maybe you better need to vacuume only 
specific tables?

Regards,
Tomasz Myrta

* Tomasz Myrta <jasiek@klaster.net> [19.03.2003 16:57]:
> U?ytkownik Victor Yegorov napisa?:
> >* Tom Lane <tgl@sss.pgh.pa.us> [19.03.2003 16:32]:
> >
> >>"Victor Yegorov" <viy@pirmabanka.lv> writes:
> >>
> >>>I'd like to make a script to automatically vacuum all my DBs nightly. And
> >>>I'd like to skip system tables, starting with pg_*.
> >>
> >>Um ... what in the world makes you think that's a good idea?  System
> >>tables need maintenance too.
> >>
> >>            regards, tom lane
> >
> >
> >Yes, of course they need.
> >
> >May be I'll put my question in a different manner:
> >
> >System tables are location-wide (I mean one set of tables for PostgreSQL
> >location) or each database has it's own set of system tables?
> >
> >If second, I apologies for noising. If first, I'd like to have a separate
> >script for them.
>
> They are location-wide. What's wrong with default vacuuming all
> databases at once nightly? Or maybe you better need to vacuume only
> specific tables?

Tom Lane pointed, that only 3 tables are location-wide:   pg_database, pg_shadow, pg_group.

Anyway, I understood, that there is more 'good' than 'bad' in vacuuming system
tables ;)

Thanks everyone.

--

Victor Yegorov

Użytkownik Victor Yegorov napisał:
> 
> Tom Lane pointed, that only 3 tables are location-wide:
>     pg_database, pg_shadow, pg_group.
It's true. I've never checked this before, but it is described in 
postgres documentation.

Tomasz