query to custom database.

[kilin-s]

Hello, I want to use queries.yaml for request to my custom postgres databases, but i don't understand how. Shold i use database name in DSN like this : DATA_SOURCE_NAME=postgresql://postgres_exporter:password@localhost:5432/my_custom_db?sslmode=disable or i can set database in queries.yaml?

[kilin-s]

And how often query is execute?

[uspen]

That is unnecessary. But if you need to request for example pg_stat_user_tables you need connect to custom db

[wrouesnel]

This is something I've not actually tried directly myself. A documentation improvement would be welcome so we can give a canonical example.

[wrouesnel]

Noted that we want to support some syntax in queries.yaml for multi-database queries.

[imatpro]

Hi,

any news on this features?

i would like use query.yaml for execute custom query in MYDB1, or MYDB2 or MYDB3.

thanks

[wrouesnel]

Probably pending the librarification of the collector, since functionally it's a bunch of collectors being aggregated, with only one including the default metrics.

[imatpro]

I resolve my problem with this exporter : https://github.com/justwatchcom/sql_exporter

[ipolishchuk]

After using postgres_exporter for a while on single database instances and loving it, it was a rude awakening to discover that I cannot collect custom metrics on multi-database instances. Is there any rough time estimate when it might become available?

[mubn]

Is there any news about this feature?

[wrouesnel]

Probably needs some ability to map multiple queries.yml files to individual DSNs.

[isaiasanchez]

Expanding the Auto-discover Databases you can add a value in queries.yaml like: database: for example, and the query will only be executed in those databases matching that list, something like:

pg_custom_query:
   query: "SELECT somelabel, somemetric FROM  sometable"
   database: "mydb1,mydb2"
   metrics:
     - somelabel:
           usage: "LABEL"
     - somemetric:
           usage: "GAUGE"

This query will only be executed when mydb1 and/or mydb2 are discovered in the instance.

In that way custom queries can be executed together with other queries using master: "true"

[Virkuz]

Expanding the Auto-discover Databases you can add a value in queries.yaml like: database: for example, and the query will only be executed in those databases matching that list, something like:

pg_custom_query:
   query: "SELECT somelabel, somemetric FROM  sometable"
   database: "mydb1,mydb2"
   metrics:
     - somelabel:
           usage: "LABEL"
     - somemetric:
           usage: "GAUGE"

This query will only be executed when mydb1 and/or mydb2 are discovered in the instance.

In that way custom queries can be executed together with other queries using master: "true"

It is don't work. I do

pg_statistic_custome:
  query: "select count(*) from msg_users_online muo where network_status = 1"
  database: "usersDB"
  metrics:
    - users_online:
        usage: "GAUGE"

And I have discovery

postgres_exporter --extend.query-path /etc/prometheus/postgres-queries.yml --auto-discover-databases --web.listen-address :9186

But in logs I have error

time="2020-07-15T11:17:24+03:00" level=info msg="Error running query on database \"127.0.0.1:5432\": pg_statistic_custome pq: relation \"msg_users_online\" does not exist" source="postgres_exporter.go:1356"

What I do wrong ?

[isaiasanchez]

Well I was suggesting these configuration for developers to implement it in future version of the exporter, right now it doesn't work.

Now what you can do is something like:

pg_statistic_custome:
  query: "SELECT CASE WHEN current_database() = 'usersDB' THEN (select count(*) from msg_users_online muo where network_status = 1) ELSE 0 END"
  metrics:
    - users_online:
        usage: "GAUGE"

Or also you can configure your postgres_exporter database connection to use your database instead of default postgres and disable autodiscover.

[asbjornenge]

Just going to leave a comment here since it was super frustrating 😅

Trying to perform a query on a custom database I had to specify DATA_SOURCE_NAME using the following params:

DATA_SOURCE_NAME: "host=<host> port=<port> sslmode=disable user=<user> password=<pass> dbname=<dbname>"

You can find the available params here.

Using DATA_SOURCE_URI I only got "database does not exist" - seems a parsing issue. Using CASE WHEN current_database() postgres was evaluating the query on all databases so I got lots of errors on databases where my custom relations (table) did not exist.

But using the DATA_SOURCE_NAME formatted as above and in combination with PG_EXPORTER_EXTEND_QUERY_PATH and PG_EXPORTER_DISABLE_DEFAULT_METRICS=true I was able to make it work as I wanted 😅👍

Thanks!