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

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

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

I'm using:

select count(*) from pg_stat_activity where query like 'autovacuum:%';

in collectd to know how many autovacuum are running concurrently.

You may need to create a security function like this:

CREATE OR REPLACE FUNCTION public.pg_autovacuum_count() RETURNS bigint
AS 'select count(*) from pg_stat_activity where query like ''autovacuum:%'';'
LANGUAGE SQL
STABLE
SECURITY DEFINER;

and call that from collectd.

In earlier Postgres, "query" was "current_query" so change it according to what works.

You can also run pg_activity to see the currently running queries on your database. I generally leave a terminal open with this running most of the time anyway as it's very useful.