Обсуждение: 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

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