How to discover the databases on a server dynamically, now that `auto-discover-databases` is DEPRECARED?

[CiucurDaniel]

As the auto-discover-databases parameter is now deprecated, what is the expected way to configure the exporter to collect metrics for all databases from a server?

(DEPRECATED) Whether to discover the databases on a server dynamically. Default is false

I have searched for this for quite a while and I do not find any example on how this is supposed to be done now.
It would be great to have such an example because at the moment we only get informed that this paramater has been deprecated but have been provided not clear guide on how to adjust.

[youribonnaffe]

I understand that multi-target would be the way to move forward from other comments but for my use case, auto-discovering all the databases and trying to connect to them with a single user seems much easier than having to setup multiple targets.

Is there a plan to better support this? Would you be open to contributions on that (I saw a few tickets and MRs but they don't seem very active).

Exporting metrics at table level through database discovery would be great, I know there are alternatives that would perhaps support that, but having predefined collectors in this exporter makes configuration much easier.

[youribonnaffe]

As I was going though the code base, trying to understand how to make it work, I realized that with autodiscovery + custom queries I could still achieve what I wanted (table level metrics for all the databases on the server, without having to explicitly configure each database).

Currently my configuration to run the exporter looks something like:

    - --auto-discover-databases
    - --extend.query-path=/queries/queries.yaml
    - --exclude-databases=rdsadmin
    - --collector.database
    - --collector.stat_database
    - --collector.locks
    - --collector.stat_bgwriter
    - --collector.roles
    - --collector.long_running_transactions
    # Not used and requires extra permission on pg_ls_waldir()
    - --no-collector.wal
    # Not using replication
    - --no-collector.replication
    - --no-collector.replication_slot
    # Disable collectors that use database discovery, they are covered by the queries.yaml configuration file
    - --no-collector.stat_user_tables
    - --no-collector.statio_user_tables
    - --no-collector.statio_user_indexes

And I'm just running those custom queries to get the table level metrics:

# Those queries do not work well database discovery and the programmatic collectors of the exporter
# So we fall back on the (deprecated) custom queries mechanism
pg_stat_user_tables:
  query: |
    SELECT
      current_database() datname,
      schemaname,
      relname,
      seq_scan,
      seq_tup_read,
      idx_scan,
      idx_tup_fetch,
      n_tup_ins,
      n_tup_upd,
      n_tup_del,
      n_tup_hot_upd,
      n_live_tup,
      n_dead_tup,
      n_mod_since_analyze,
      COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum,
      COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum,
      COALESCE(last_analyze, '1970-01-01Z') as last_analyze,
      COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze,
      vacuum_count,
      autovacuum_count,
      analyze_count,
      autoanalyze_count,
      pg_total_relation_size(relid) as size_bytes
    FROM
      pg_stat_user_tables
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of current database"
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - seq_scan:
        usage: "COUNTER"
        description: "Number of sequential scans initiated on this table"
    - seq_tup_read:
        usage: "COUNTER"
        description: "Number of live rows fetched by sequential scans"
    - idx_scan:
        usage: "COUNTER"
        description: "Number of index scans initiated on this table"
    - idx_tup_fetch:
        usage: "COUNTER"
        description: "Number of live rows fetched by index scans"
    - n_tup_ins:
        usage: "COUNTER"
        description: "Number of rows inserted"
    - n_tup_upd:
        usage: "COUNTER"
        description: "Number of rows updated"
    - n_tup_del:
        usage: "COUNTER"
        description: "Number of rows deleted"
    - n_tup_hot_upd:
        usage: "COUNTER"
        description: "Number of rows HOT updated (i.e., with no separate index update required)"
    - n_live_tup:
        usage: "GAUGE"
        description: "Estimated number of live rows"
    - n_dead_tup:
        usage: "GAUGE"
        description: "Estimated number of dead rows"
    - n_mod_since_analyze:
        usage: "GAUGE"
        description: "Estimated number of rows changed since last analyze"
    - last_vacuum:
        usage: "GAUGE"
        description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
    - last_autovacuum:
        usage: "GAUGE"
        description: "Last time at which this table was vacuumed by the autovacuum daemon"
    - last_analyze:
        usage: "GAUGE"
        description: "Last time at which this table was manually analyzed"
    - last_autoanalyze:
        usage: "GAUGE"
        description: "Last time at which this table was analyzed by the autovacuum daemon"
    - vacuum_count:
        usage: "COUNTER"
        description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
    - autovacuum_count:
        usage: "COUNTER"
        description: "Number of times this table has been vacuumed by the autovacuum daemon"
    - analyze_count:
        usage: "COUNTER"
        description: "Number of times this table has been manually analyzed"
    - autoanalyze_count:
        usage: "COUNTER"
        description: "Number of times this table has been analyzed by the autovacuum daemon"
    - size_bytes:
        usage: "GAUGE"
        description: "Total disk space used by this table, in bytes, including all indexes and TOAST data"

pg_statio_user_tables:
  query: "SELECT current_database() datname, schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of current database"
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - heap_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this table"
    - heap_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this table"
    - idx_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from all indexes on this table"
    - idx_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in all indexes on this table"
    - toast_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this table's TOAST table (if any)"
    - toast_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this table's TOAST table (if any)"
    - tidx_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this table's TOAST table indexes (if any)"
    - tidx_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this table's TOAST table indexes (if any)"

pg_statio_user_indexes:
  query: "SELECT current_database() datname, schemaname, relname, indexrelname, idx_blks_read, idx_blks_hit FROM pg_statio_user_indexes"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of current database"
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - indexrelname:
        usage: "LABEL"
        description: "Name of this index"
    - idx_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this index"
    - idx_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this index"

I understand the features are deprecated but it seems this use case is quite popular and I failed to understand why they were deprecated looking at the history/issues (but I welcome any thoughts on it, given my limited knowledge of the exporter, I'm not arguing whether it is a good or bad thing).

[Vampouille]

Maybe maintainers can re-consider the deprecation ? From my point of view it's 2 key features:

• db auto discovery
• custom SQL queries

@sysadmind Can you get feedback from maintainers ?