prometheus-community / prometheus-community/postgres_exporter

exclude-databases does not properly work

Open
#588 16 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
Go
Stars
3.6k
Forks
835
Avg merge
2d 4h
Merged PRs (30d)
10

Description

What did you do?
I ran an exporter process including the auto-discover-databases and exclude-databases flags. Also pulling from a custom query files:

exec postgres_exporter --web.listen-address :9189 --disable-default-metrics --auto-discover-databases --exclude-databases postgres,template0,template1 --extend.query-path /tmp/queries.yaml

/tmp/queries.yaml file includes two different queries:

    pg_database:
      query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database"
      master: true
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - size_bytes:
            usage: "GAUGE"
            description: "Disk space used by the database"

    pg_database_2:
      query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database WHERE datname = current_database()"
      master: true
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - size_bytes:
            usage: "GAUGE"
            description: "Disk space used by the database"

DBs on my instance:

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 nsoengas  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
           |          |          |            |            | postgres=CTc/postgres+
           |          |          |            |            | nsoengas=C/postgres
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=#

What did you expect to see?
Since I am using exclude-databases flag and then excluding postgres,template0,template1 DBs, then my expectation is postgres exporter connecting to nsoengas DB and performing the queries on my custom file (/tmp/queries.yaml).
According to those queries, pg_database should retrieve datname and size for every single database on given instance. pg_database_2 should ONLY show datname and size for the database I am connected to. I've included the proper query filter to do that.
So, pg_database_2 should connect to "nsoengas" DB and report its datname and size.

What did you see instead? Under which circumstances?
pg_database seems to be "showing" what it suppose to show. However, pg_database_2 is showing "postgres" as datname. Since I explicitly included the filter WHERE datname = current_database() AFAIK it is connecting to "postgres" database and retrieving its size
It could be seen while scrapping on the exporter port:

[root@nsoengas-db-0 /]# curl http://localhost:9189/metrics | grep pg_database
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0# HELP pg_database_2_size_bytes Disk space used by the database
# TYPE pg_database_2_size_bytes gauge
pg_database_2_size_bytes{datname="postgres",server="localhost:5432"} 7.526936e+06
# HELP pg_database_size_bytes Disk space used by the database
# TYPE pg_database_size_bytes gauge
pg_database_size_bytes{datname="nsoengas",server="localhost:5432"} 7.58428e+06
pg_database_size_bytes{datname="postgres",server="localhost:5432"} 7.526936e+06
pg_database_size_bytes{datname="template0",server="localhost:5432"} 7.414276e+06
pg_database_size_bytes{datname="template1",server="localhost:5432"} 7.414276e+06
100 51180    0 51180    0     0  4543k      0 --:--:-- --:--:-- --:--:-- 4543k
[root@nsoengas-db-0 /]#

Environment

  • System information:
[root@nsoengas-db-0 ~]$ uname -srm
Linux 5.4.17-2102.202.5.el7uek.x86_64 x86_64
[root@nsoengas-db-0 ~]$
  • postgres_exporter version:
[root@nsoengas-db-0 /]# postgres_exporter --version
postgres_exporter, version 0.10.0 (branch: HEAD, revision: 57719ba53cac428769aaf3c4c0bb742df3cfca98)
  build user:       root@4dcb2c7f1315
  build date:       20210709-11:49:20
  go version:       go1.16.5
  platform:         linux/amd64
[root@nsoengas-db-0 /]#
  • postgres_exporter flags:
exec postgres_exporter --web.listen-address :9189 --disable-default-metrics --auto-discover-databases --exclude-databases postgres,template0,template1 --extend.query-path /tmp/queries.yaml
  • PostgresSQL version:
postgres=# SELECT version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=#

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the command with auto-discovery, excluded databases, and the two queries in /tmp/queries.yaml. Trace how discovered database connections execute custom queries and verify that excluded databases are skipped and current_database() reports the selected database rather than postgres.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases, observability
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.