Add auto-discover-database for deeper server metrics

Context

We have some additional queries that used to work well on older versions of postgres_exporter. However, due to the removal of custom queries and auto-discover, it appears that we are losing this level of detail. It would greatly benefit us if the auto-discover feature could be reinstated as it was crucial for our needs (as we have a large number of databases).

Example of query:

SELECT current_database() datname, us.schemaname, us.relname, us.indexrelname, us.idx_scan, us.idx_tup_read, us.idx_tup_fetch, io.idx_blks_read, io.idx_blks_hit, ind.indisvalid
    FROM pg_index as ind, pg_stat_user_indexes as us, pg_statio_user_indexes as io
    WHERE ind.indexrelid = us.indexrelid
    AND ind.indexrelid = io.indexrelid;
# This query need to be executed per database to resolve relname (relid::regclass)

Proposal

Revert auto-discover-database feature to allow deeper queries when monitoring postgresql instances.

The release notes states:

This exporter is meant to monitor PostgresSQL servers, not the user data/databases. If
you need a generic SQL report exporter https://github.com/burningalchemist/sql_exporter
is recommended.

As you can see in the provided example, it's important to have a more detailed monitoring of the server, especially at the level of individual databases. Currently, the tables we need to access are only visible within each database, which can be quite tedious if we have a large number of databases.

We are interested in enhancing the current collectors and introducing new ones to improve monitoring capabilities. However, the recent changes have been disadvantageous for us and are causing some setbacks.

TL;DR

Auto-discover-database and associated features could benefit to more complex collectors. We don't want the reversal of custom queries, as we understand the vision behind its removal.

See also

These needs are related to our opensource database monitoring framework and the PostgreSQL part relies on this project.

No files, tests, or entry points are named. Start by reviewing the current collectors and the removed auto-discover-database behavior, then determine how per-database queries should be represented without restoring custom queries. Done means auto-discovery supports deeper PostgreSQL metrics across databases and has coverage for the new behavior.