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

postgresql unix autovacuum

Clint Pachl · Nov 7, 2013 · Viewed 46.6k times · Source

How can one tell if the autovacuum daemon in Postgres 9.x is running and maintaining the database cluster?

Answer

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.