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