This blog post is about how you can gain detailed information in Percona Monitoring and Management (PMM2) of PostgreSQL disk usage. We take a look at using a catalog called pg_class, which catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes, sequences, views, materialized views, composite types, and toast tables.
It will use some custom queries in order to fetch such information, so we need to create a file with a custom query. Postgresql exporter is processing such files by default, so you should place your file into one of the paths referred to below, to the desired scraping interval and restart the exporter.
# /etc/init.d/pmm-agent restart
Stopping pmm-agent: [ OK ]
Starting pmm-agent: [ OK ]
FYI, the metrics scraping interval can be set on the tab “PMM Settings”:
Setting up Query Monitoring in PMM for Postgres
First of all, we have to enable the extension dblink. It’s a PostgreSQL contrib extension that allows users to connect to other databases and run arbitrary queries in them.
postgres=# CREATE EXTENSION dblink;
List of installed extensions
Name | Version | Schema | Description
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
The next step is to create a query for collecting data from tables. We propose using our bash script that generates a query and forms other fields for a custom query file. You have to specify the database name, or names, that will be monitored. Pg_class catalog is generated for each database so it’s not possible to create a general query. You can store the result of the script in a separate file or can extend an existing file with queries.
$ ./postgres_query_pg_class_generator.sh sbtest sbtest2 >> queries-postgres-2.yml
Now it’s time to restart exporter and check the newly collected metrics.
Also, you can upload our dashboard in order to checks queries through the Grafana interface. It has got all PMM2 elements such as variables, menu, annotations.
Here is a screenshot from our test installation.