Only run certain queries on some databases

[pvanderlinden]

I was wondering if it is possible to run some queries on some of the databases?
The postgres exporter auto discovers the databases, and runs all queries on all databases. Which currently logs errors in both the exporter and postgres logs. How can I restrict certain queries to only some/one of the databases (for example only run pg_stat_statements on the "main" database)? Or is that a new feature?

[uspen]

master: true

[pvanderlinden]

@uspen that only says it should only run on the master database server as far as I understand. Otherwise I don't understand how to specify what the main DB is (and would be severely limiting)

[pvanderlinden]

Is this project dead @wrouesnel ?

[wrouesnel]

I haven't had a lot of time to work on it over the last year, but more significantly I simply don't work with monitoring postgres databases all that much professionally anymore. Pull requests are merged as I can get around to it (like now).

[adriendrien]

Hi,

Is there any known workaround so we can avoid logging errors ?

[itshikanov]

any ideas?

[pvanderlinden]

I have looked at the code, the main challenge I see is that there is no database name available when looping through servers/queries:

postgres_exporter/cmd/postgres_exporter/postgres_exporter.go

Line 1460 in cbc6ae3

for namespace, mapping := range server.metricMap {

[SuperQ]

Prometheus exporters are supposed to be designed to be 1:1 with their targets. The other option is to use the multi-target exporter pattern.

The current multi-database server feature is actually a mistake and needs to be removed. As this bug has shown, it's one of those unintended consequences of trying to do things that don't follow best practices.

[pvanderlinden]

This is actually not about multiple servers, but about scraping multiple databases within in single server (or would that be seen as a separate target as well)? How would you approach this @SuperQ , because a lot of the metrics exported within this exporter will be server wide metrics, others are database specific metrics (but most will export the metrics for all databases within that server)?

[SuperQ]

Oh, sorry, I was confused by name of the feature.

Is this about filters for which databases are included in table stats?

[pvanderlinden]

It is mainly about filtering which user queries will run on which databases. There are also a few server wide tables with database stats, which this is not about. The main scenario is to run certain queries on certain database(s) to prevent a constant error stream

[pvanderlinden]

For example pg_stat_statements is an extension, which only needs to be activated on one database to get the info you need.

[SuperQ]

I see part of this problem is that the user-provided query system allows the exporter to be abused as a general use SQL exporter. IMO we should probably eliminate this feature, and integrate all the useful postgresql server metrics queries into the exporter as collectors . This is how we avoid this class of problems in the mysqld_exporter.

[pvanderlinden]

How would you handle this with extensions? Postgresql has a ton of extensions, which usually provide their own statistics. The statement statistics table is one of them (not installed/activated by default)