# PostgresSQL Database/Table size monitoring
**URL:** https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403
**Category:** Database Monitoring and Management
**Tags:** postgres
**Created:** [October 4, 2021, 11:12am UTC](https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403 "2021-10-04T11:12:04Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@winnetou](https://forums.percona.com/u/winnetou)
#### Post date: [October 4, 2021, 11:12am UTC](https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403/1 "2021-10-04T11:12:04Z")
Hello All!
I’m trying to monitor PostgreSQL tables/database sizes. I run [\>\>\>this\<\<\<](https://www.percona.com/blog/2020/03/12/adding-postgresql-databases-tables-size-dashboard-to-the-percona-monitoring-and-management-plugin/) 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:
```auto
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
```auto
## 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:
```auto
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
---
### Author:  [@Anton\_Bystrov](https://forums.percona.com/u/Anton_Bystrov)
#### Post date: [October 26, 2021, 5:27am UTC](https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403/2 "2021-10-26T05:27:57Z")
Hi, Przemek. Please try to enable extension dblink from pmm user.
---
### Author:  [@winnetou](https://forums.percona.com/u/winnetou)
#### Post date: [October 26, 2021, 1:06pm UTC](https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403/3 "2021-10-26T13:06:28Z")
Hi, Anton
Thank you for reply. Unfortunately this didn’t help (just like I suppose). I’m still getting same errors. I have even updated pmm to newest version (2.23.0 from October 21st).
```auto
Oct 26 14:50:16 ntms-debug pmm-agent[27106]: #033[36mINFO#033[0m[2021-10-26T14:50:16.552+02:00] time="2021-10-26T14:50: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:1426" #033[36magentID#033[0m=/agent_id/011189e9-a10a-4aa4-bfc4-1066a78077e4 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=postgres_exporter
Oct 26 14:50:16 ntms-debug pmm-agent[27106]: #033[36mINFO#033[0m[2021-10-26T14:50:16.552+02:00] time="2021-10-26T14:50:16+02:00" level=error msg="queryNamespaceMappings returned 1 errors" source="postgres_exporter.go:1605" #033[36magentID#033[0m=/agent_id/011189e9-a10a-4aa4-bfc4-1066a78077e4 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=postgres_exporter
```
```auto
[14:56:59]root@ntms-debug:~# psql -h127.0.0.1 -U pmm postgres
Password for user pmm:
psql (11.12 (Debian 11.12-0+deb10u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
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
(3 rows)
postgres=# 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';
datname | relname | table_rows | disk_usage_table_bytes | disk_usage_index_bytes | disk_usage_toast_bytes
---------+-----------------------------------------------+------------+------------------------+------------------------+------------------------
ntms | pg_toast_16393 | 0 | 8192 | 8192 | 0
...
(655 rows)
postgres=#
```
Query generated by script works flawlessly but for some reason pmm-client/agent exporters are throwing errors.
---
### Author:  [@Anton\_Bystrov](https://forums.percona.com/u/Anton_Bystrov)
#### Post date: [October 28, 2021, 5:19pm UTC](https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403/4 "2021-10-28T17:19:32Z")
Hi, Przemek!
I repeat this problem. It is true when you haven’t user/database “postgres” and extension dblink at postgres db. Can you check it at your environment?
---
### Author:  [@winnetou](https://forums.percona.com/u/winnetou)
#### Post date: [October 31, 2021, 6:17pm UTC](https://forums.percona.com/t/postgressql-database-table-size-monitoring/12403/5 "2021-10-31T18:17:50Z")
User and database postgres exists. Dblink extension is installed on postgres database.
I have no more ideas what’s wrong, so probably will try to make own dashboard and query. Especially that names “bloat\_1234” or “index\_3412” are annoying 😉