PostgresSQL Database/Table size monitoring

Hello All!

I’m trying to monitor PostgreSQL tables/database sizes. I run >>>this<<< tutorial from Percona. It looks like it’s working but PMM Agent is spamming errors in syslog.

Here’s my setup:

PMM Server:

  • OS/kernel: Linux pmm-test 4.19.0-17-amd64 #1 SMP Debian 4.19.194-3 (2021-07-18) x86_64 GNU/Linux
  • Docker: Docker version 18.09.1, build 4c52b90 (installed from Debian repository)
  • Docker compose version: docker-compose version 1.21.0, build unknown (also from Debian repository)
  • PMM server version: 2.22.0 (September 21, 2021)

PostgreSQL server:

  • OS/kernel: Linux ntms-test 4.19.0-17-amd64 #1 SMP Debian 4.19.194-3 (2021-07-18) x86_64 GNU/Linux
  • PostgreSQL version: psql (11.12 (Debian 11.12-0+deb10u1))
  • PMM Agent: 2.21.0 (FullCommit: 22d3dec79e93b92de77a9fb8ff2b74b3d8d5d47d)
  • Installed extensions on PostgreSQL database:
postgres=# \dx
                                       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
  • Full YAML generated by Percona script to monitor table size
## Custom query
pg_class:
  query: "SELECT datname, COALESCE(ntms.relname) as relname, COALESCE(ntms.table_rows) as table_rows, COALESCE(ntms.disk_usage_table_bytes) as disk_usage_table_bytes, COALESCE(ntms.disk_usage_index_bytes) as disk_usage_index_bytes, COALESCE(ntms.disk_usage_toast_bytes,0) as disk_usage_toast_bytes FROM pg_database LEFT OUTER JOIN dblink('dbname=ntms','SELECT current_database(), relname,  CAST(reltuples as BIGINT), pg_total_relation_size(oid), pg_indexes_size(oid), pg_total_relation_size(reltoastrelid) FROM pg_class') as ntms(datname name, relname name, table_rows BIGINT, disk_usage_table_bytes BIGINT, disk_usage_index_bytes BIGINT, disk_usage_toast_bytes BIGINT) USING (datname) WHERE datname NOT LIKE 'template_' AND datname NOT LIKE 'postgres'"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of the database that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of the table, index, view, etc."
    - table_rows:
        usage: "GAUGE"
        decription: "Number of rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX"
    - disk_usage_table_bytes:
        usage: "GAUGE"
        description: "Total disk space used by the specified table, including all indexes and TOAST data"
    - disk_usage_index_bytes:
        usage: "GAUGE"
        description: "Total disk space used by indexes attached to the specified table"
    - disk_usage_toast_bytes:
        usage: "GAUGE"
        description: "Total disk space used by TOAST data attached to the specified table"

I can see charts in grafana. If I run this query from pmm postgres user it’s returning all data without any errors. There is no other schemata than public.
Errors in syslogs looks like this:

Oct  4 13:08:16 ntms-test pmm-agent[5065]: #033[36mINFO#033[0m[2021-10-04T13:08:16.955+02:00] time="2021-10-04T13:08:16+02:00" level=info msg="Error running query on database \"127.0.0.1:5432\": pg_class pq: function dblink(unknown, unknown) does not exist" source="postgres_exporter.go:1388"  #033[36magentID#033[0m=/agent_id/2e6857f8-8f5c-4912-a65c-f8f7298dd641 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=postgres_exporter

This error is occuring every second/two seconds (despite it’s placed in low-resolution folder which should collect data every 60 seconds).

Regards
Przemek