calling vacuum from a script

On 23/06/10 13:44, Janet Jacobsen wrote:

Hi. Is it possible to vacuum a table (vacuum full analyze)
from a script. Currently I run the Postgres client and then
run vacuum, but I'd like to automate the vacuum by calling
it from a (Perl) script.

see attached for example.

On Tue, 2010-06-22 at 20:44 -0700, Janet Jacobsen wrote:

Is it possible to vacuum a table (vacuum full analyze)
from a script.

Use vacuumdb command.

(Why are you using VACUUM FULL? It has been considered harmful, at least
for a regular maintenance job.)
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

On 06/22/10 8:44 PM, Janet Jacobsen wrote:

Hi. Is it possible to vacuum a table (vacuum full analyze)
from a script. Currently I run the Postgres client and then
run vacuum, but I'd like to automate the vacuum by calling
it from a (Perl) script.

postgres since 8.1 has had autovacuum, autovacuum automatically
analyzes, too. You shouldn't need to vacuum in a script.

in answer to your specific question, yes. perl can connect to postgres
quite easily, using DBI w/ DBD-PgSQL, then you can just execute a query
like "VACUUM ANALYZE tablename;"

If your tables have become badly fragmented, CLUSTER on the most used
index (usually the primary key) works much better than VACUUM FULL,
although do note, it uses a global table lock, so you can't be accessing
the table at the same time.

Devrim GÜNDÜZ wrote:

(Why are you using VACUUM FULL? It has been considered harmful, at least
for a regular maintenance job.)

We should point everyone using VACUUM FULL like Janet to read
http://wiki.postgresql.org/wiki/VACUUM_FULL

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us