Postgresql – nulls on pg_stat_get_progress_info(‘VACUUM’) columns on pg_exporter role

autovacuummonitoringpostgresqlpostgresql-12vacuum

I'm trying to export the results of a query select * from pg_stat_progress_vacuum;. The problem is that the only column values I can see from the created user are:

  • pid
  • datid
  • datname

Other columns are constantly null. When query is run from superuser account there are values of course.

Based on pg_stat_progress_vacuum view definition I noticed that the problematic part is usage of pg_stat_get_progress_info function with VACUUM parameter (view names params returned by this function).

User has pg_monitor role assigned.

What am I missing?

Result of select * from pg_stat_progress_vacuum; from monitoring user

[
  {
    "pid": 33079,
    "datid": 16410,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  },
  {
    "pid": 38625,
    "datid": 121786502,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  }
]

Result from superuser for the same query

postgres=# select * from pg_stat_progress_vacuum;
  pid  |   datid   |     datname      |   relid   |       phase       | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
-------+-----------+------------------+-----------+-------------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 38625 | 121786502 | <database name> | 121786624 | vacuuming indexes |         7091104 |           7091104 |                  0 |                  0 |       178956970 |        73659774
(1 row)

A couple of seconds passed from one query execution to the other, so I assume that just autovacuum has finished thus the difference in row count.

Postgres version is 12.2

Best Answer

You are missing the required privileges.

To see all values in a line of pg_stat_progress_vacuum, one of the following must be satisfied:

  • you are a superuser

  • the VACUUM is run by the same user as your current connection

  • you are a member of the system role pg_read_all_stats