Vacuum PostgreSQL DB from PHP

Question

I want to vacuum a PostgreSQL database from PHP.

I have tried this:

pg_query($conn,"vacuum analyse;");

How can I tell if this works?

Answer 1

I believe it doesn't return an error, it is probably working. Here are the docs on vacuum.

http://www.postgresql.org/docs/current/interactive/sql-vacuum.html

Answer 2

First question: why did you do VACUUM FULL? It doesn't make sense. It should be practically never called.

Second: Running vacuum from php (presumably webpage) can be problematic. Vacuum can easily take over 3 minutes, which is (if I recall correctly) standard timeout for web requests.

The best solution is to use autovacuum. If you can't use autovacuum - schedule vacuumdb calls using cron.

Answer 3

Assuming your vacuum runs for more than a few seconds, you can run "SELECT * FROM pg_stat_activity" in a postgres client to get a list of currently running queries. Your VACUUM query should show up there.

Answer 4

Run this query before and after running the vacuum query. If 'age' is less after the vacuum than it was before, then the vacuum has run successfully.

SELECT age(datfrozenxid) as age FROM pg_database where datname='your_db';