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
--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
