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?

score 98 · Accepted Answer · edited May 23 '17 at 11:46

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:

score 19 · Answer 2 · edited Aug 12 '16 at 19:53

19

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.

edited Aug 12 '16 at 19:53

Jeff Atwood

63,320
48
150
153

answered Jul 29 '14 at 09:57

Cactusbone

1,056
8
20

This works if you run the query as the same user that is running autovacuum, otherwise the "query" field may just show . Especially on AWS/GCP. – xref Aug 05 '18 at 02:21

score 0 · Answer 3 · answered Mar 02 '16 at 19:35

0

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.

answered Mar 02 '16 at 19:35

Philip Snyder

54
3

score 0 · Answer 4 · answered Jan 18 '23 at 02:56

0

set log_autovacuum_min_duration to the time length that you desire and the autovacuum execution exceeds the time length will be logged.

answered Jan 18 '23 at 02:56

CJ Chang

325
4
12