Automatic target discovery for multiple databases?

[ringerc]

I've had a look around the code, README, etc and I didn't find any means of auto-discovering target databases to scrape.

Most scrapers like postgres_exporter and pg_exporter support querying pg_catalog.pg_database to enumerate databases available on the postgres instance and run metrics queries on each one during a scrape.

Is there a means to do any similar sort of discovery with sql_exporter or does that have to be handled by some external co-ordinating tool that watches for DB creations/drops and updates the sql_exporter configured targets?

[burningalchemist]

Hey @ringerc, sql_exporter is quite simple, db-agnostic and SQL-focused (and not database) exporter in the first place, so there's no such functionality built-in. It is possible to update config and reload the exporter (there's an endpoint for that) on the fly, so effectively such functionality would be a part of some external tooling as you pointed it out.

For instance, I have my custom solutions around sql_exporter (say, the core), going beyond standard available operations, interacting with the config and adjusting the scraping behaviour as per my needs.

I plan to start working on the operator for Kubernetes-based infrastructure, so the problem in that scope would be reduced to managing Metric resources which is a user responsibility anyway (they will be merged and sync with the exporter). For custom environments (e.g. on-prem or plain VM hosts in the cloud) I guess it needs to be solved in a custom way.

[ringerc]

Thanks for the info.

That's unfortunate, as I would've thought this would be a commonplace requirement across many DB engines. I guess most others permit a single connection to query resources across the whole database instance, without postgres's per-database compartmentalization.

The postgres_exporter project is recommending sql_exporter for custom SQL-query-based metrics generation as they restructure their project around custom golang based collectors for specific postgres system views and tables. But it's a non-starter for most workloads if it can't do database discovery, it'll have to be coupled with some kind of operator/supervisor to dynamically manage its configuration.

If you land up looking into a plugin architecture in future, discovery plugins would be a good candidate, and postgres database discovery would be a no-brainer. For now I'll need to look elsewhere.

Thanks for taking the time to respond.

[burningalchemist]

Hey @ringerc, thanks for your suggestion! I'll add it to the list of ideas.

It's tricky as each database engine keeps the logical database info in a specific place, so to support 30+ database engines, we would need to hardcode these locations and maintain them. Some of them (e.g. Snowflake) keep this information outside of the instance or it's not easily obtainable. In this case, it's easier and more consistent to let users organise/generate their configuration with the desired information obtained from the configuration management systems or available APIs.

Yeah, I'm aware of their suggestion to replace postgres_exporter with sql_exporter for custom queries. But it's not fully compatible, so people often get confused.

[ringerc]

I'm not surprised they get confused, as sql_exporter has very different functionality and is far from a drop-in replacement.

It sounds like the only viable option for in-component auto discovery would be a discovery plugin interface, as you wouldn't want to maintain all that yourself. It's not something I'd personally want to delegate to an external config manager and coordination tool, but I'm not the one doing the hard work here.

I'll be using https://github.com/Vonng/pg_exporter since database discovery is not something I can live without, and I also need conditional query execution to cope with DB version differences, differences in installed extensions etc. pg_exporter doesn't support conditional queries yet, but I've written a patch for it so hopefully it will soon. If that doesn't work out I'll extract the https://github.com/cloudnative-pg/cloudnative-pg/ metrics exporter into a standalone plugin. I'll try to remember to update here if I land up doing that. Hopefully this info may help other people who've come here after seeing postgres_exporter has deprecated their query config file based scraper.