Thread: calling vacuum from a script
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.
Thanks,
Janet
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.
Thanks,
Janet
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.
Attachment
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
Attachment
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