How can I tell if PostgreSQL's Autovacuum is running on UNIX?

autovacuum, postgresql, unix

Solution

PostgreSQL 9.3

Determine if Autovacuum is Running

This is specific to Postgres 9.3 on UNIX. For Windows, see this question.

Query Postgres System Table

SELECT
  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count  -- not available on 9.0 and earlier
FROM pg_stat_user_tables;

Grep System Process Status

$ ps -axww | grep autovacuum
24352 ??  Ss      1:05.33 postgres: autovacuum launcher process  (postgres)    

Grep Postgres Log

# grep autovacuum /var/log/postgresql
LOG:  autovacuum launcher started
LOG:  autovacuum launcher shutting down

If you want to know more about the autovacuum activity, set `log_min_messages` to `DEBUG1..DEBUG5`. The SQL command `VACUUM VERBOSE` will output information at log level `INFO`.

Regarding the Autovacuum Daemon, the Posgres docs state:

In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set.

See Also:

- http://www.postgresql.org/docs/current/static/routine-vacuuming.html

- http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

Problem

Original source