From: Usama Dar <munir.usama@gmail.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: posgres support <pgsql-admin@postgresql.org>
Subject: Re: Enhancement request
Date: Sat, 1 Dec 2007 02:42:13 +0500
Message-ID: <ff0e67090711301342q62f99a12y188fddce920d2449@mail.gmail.com> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760197E2C@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760197E2C@SAB-FENWICK.sab.uiuc.edu>
On Dec 1, 2007 12:46 AM, Campbell, Lance <lance@uiuc.edu> wrote:
> Could you please add to your to do list a schema parameter for vacuum?
>
>
>
> Example:
>
>
>
> VACUUM SCHEMA xyz;
>
>
>
> PostgreSQL would get a list of all of the tables found in the schema. It
> would then loop through vacuuming each table in the schema.
>
>
>
> I found today that I did not have max_fsm_pages set high enough in order
> to vacuum the entire database. I vacuum daily to avoid problems. After
> realizing that I cannot vacuum the entire database now, I then had to do it
> at the table level. I had to go through all 13 of my schemas and list each
> table I found into an SQL vacuum script. This was a real waist of time. I
> will eventually restart PostgreSQL with an increased value for max_fsm_pages
> so this will not be an issue.
>
>
>
> But until then I will have to run a new script. I created a script with
> 420 SQL vacuum statements at the table level. I would have preferred to
> create 13 vacuum SQL statements at the schema level.
>
i generally use an sql to generate vaccum statement for all tables in a
schema and then simple copy paste the output in an sql file and then just
run it. something like following
postgres=# select 'vacuum ' || relname || ';'
from pg_class c , pg_namespace n
where c.relnamespace = n.oid
and n.nspname='public' and relkind='r';
?column?
--------------
vacuum b;
vacuum a;
vacuum test;
vacuum xyz;
vacuum foo;
(5 rows)
You can redirect this output to a file and then strip off the heading and
the feedback of (5 rows) and just run that file
>
> Thanks for considering this enhancement,
>
>
>
> Lance Campbell
>
> Project Manager/Software Architect
>
> Web Services at Public Affairs
>
> University of Illinois
>
> 217.333.0382
>
> http://webservices.uiuc.edu
>
>
>
--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: munir.usama@gmail.com, lance@uiuc.edu
Subject: Re: Enhancement request
In-Reply-To: <ff0e67090711301342q62f99a12y188fddce920d2449@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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