Adapt new collector package for database scoped collectors

[sysadmind]

--auto-discover-databases is deprecated. This is because it's designed to support the dynamic SQL metrics which is also deprecated. It did however provide value for certain collectors that provide metrics only for the current database.

The new collector/ subsystem only supports a single database connection per exporter instance. NewPostgresCollector takes a single dsn string (not a slice).

The old exporter/ subsystem with --auto-discover-databases would:

1. Connect to your configured DSN
2. Query pg_database for all databases on the server
3. Open a separate connection to each database and scrape it

This matters because several PostgreSQL system views are per-database. So a user who previously ran one exporter pointed at their postgres database with --auto-discover-databases would get table/index/lock stats for all their application databases. In the new collector pattern, they only get those stats for the single database in their DSN.

Proposal

I am proposing that we add the functionality back to address the user issues and restore that level of metrics collection for the end users. Below is an outline of how it could work so that we can review and decide on the direction.

How to Add It Back

Update collector registration to specify cluster or database scoped

  const (
      ClusterScoped  = false
      DatabaseScoped = true
  )

  func registerCollector(name string, isDefaultEnabled bool, isDatabaseScoped bool, createFunc func(collectorConfig) (Collector, error))

Store isDatabaseScoped in a parallel map (like collectorState). All existing registrations default to ClusterScoped; the four per-DB collectors flip to DatabaseScoped.

Add a discovery Option to PostgresCollector

  func WithDatabaseDiscovery(include, exclude []string) Option

When set, PostgresCollector stores the filter lists alongside the primary instance.

Split Collect() by scope

When discovery is enabled, Collect() becomes:

1. Run cluster-scoped collectors once on the primary instance (current behavior)
2. Query pg_database on the primary connection, filter by include/exclude
3. For each discovered DB: open a new instance pointing at that DB, run only database-scoped collectors, close the instance

Without discovery (default), behavior is identical to today — all collectors run against the single instance, including the database-scoped ones (which scrape the connected DB only, as now).

Key properties of this design:

• Zero behavior change for existing users who don't opt in
• No changes to the Collector interface — individual collectors don't need to know about discovery
• The deduplication problem is solved structurally: cluster-scoped metrics are emitted once, per-DB metrics are emitted once per database with the datname label already in place
• Works for both PostgresCollector (the /metrics endpoint) and the ProbeCollector the same way

Related Issues

#1068
#999
#987
#786
#750
#735

[SuperQ]

This makes a lot of sense. I think one of the big confusions we've had over time is Server vs Database terminology.

One thing we should be clear about, when you mention "cluster", I think you mean "server". There is no "cluster" in PostgreSQL. PostgreSQL is inherently a non-clustered database server. Unlike, for example, Cassandra. I think we should avoid the word "cluster" and stick with "server".