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

Question

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

Answer 1

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

Answer 2

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.