pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum all but system tables
8+ messages / 4 participants
[nested] [flat]
* vacuum all but system tables
@ 2003-03-19 08:38 Victor Yegorov <viy@pirmabanka.lv>
0 siblings, 2 replies; 8+ messages in thread
From: Victor Yegorov @ 2003-03-19 08:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-19 13:32 A.Bhuvaneswaran <bhuvansql@myrealbox.com>
parent: Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 0 replies; 8+ messages in thread
From: A.Bhuvaneswaran @ 2003-03-19 13:32 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-19 14:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-03-19 14:29 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-19 14:39 Victor Yegorov <viy@pirmabanka.lv>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Victor Yegorov @ 2003-03-19 14:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-19 14:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2003-03-19 14:47 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-19 14:53 Tomasz Myrta <jasiek@klaster.net>
parent: Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-03-19 14:53 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-19 15:02 Victor Yegorov <viy@pirmabanka.lv>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Victor Yegorov @ 2003-03-19 15:02 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum all but system tables
@ 2003-03-20 08:33 Tomasz Myrta <jasiek@klaster.net>
parent: Victor Yegorov <viy@pirmabanka.lv>
0 siblings, 0 replies; 8+ messages in thread
From: Tomasz Myrta @ 2003-03-20 08:33 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-03-20 08:33 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-19 08:38 vacuum all but system tables Victor Yegorov <viy@pirmabanka.lv>
2003-03-19 13:32 ` A.Bhuvaneswaran <bhuvansql@myrealbox.com>
2003-03-19 14:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-19 14:39 ` Victor Yegorov <viy@pirmabanka.lv>
2003-03-19 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-19 14:53 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-19 15:02 ` Victor Yegorov <viy@pirmabanka.lv>
2003-03-20 08:33 ` Tomasz Myrta <jasiek@klaster.net>
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