1

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?

Liam
  • 19,819
  • 24
  • 83
  • 123

4 Answers4

4

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.

  • Timeout is not a problem, it is a PHP script that is called by cron and wget and carries out several other maintenance tasks. – Liam Sep 15 '09 at 09:08
  • 1
    If you have access to cron, why don't you just directly call vacuumdb? –  Sep 15 '09 at 11:15
3

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';
Liam
  • 19,819
  • 24
  • 83
  • 123
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

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Anthony Gatlin
  • 4,407
  • 5
  • 37
  • 53
1

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.

Frank Farmer
  • 38,246
  • 12
  • 71
  • 89