Telegraf: postgresql_extensible.query on pg_stat_all_tables returns metrics from one database not all defined

[julrodriguez]

When specifying a query with pg_stat_all_tables I dont get all metrics from all databases; I just get metrics from the database defined in the address string of the connection.

Telegraf version 1.8.3
Config:

address = "host=localhost user=dbuser password=xxxxx database=postgres"
databases = ["gaia_core","postgres"]

[[inputs.postgresql_extensible.query]]
sqlquery="""select (current_database())::varchar as dbname,
quote_ident(schemaname)||'.'||quote_ident(relname) as tblfullname,
pg_relation_size(relid) as table_size_b,
pg_total_relation_size(relid) as total_relation_size_b,
pg_relation_size((select reltoastrelid from pg_class where oid = ut.relid)) as toast_size_b,
extract(epoch from now() - greatest(last_vacuum, last_autovacuum)) as seconds_since_last_vacuum,
extract(epoch from now() - greatest(last_analyze, last_autoanalyze)) as seconds_since_last_analyze,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
from
pg_stat_all_tables ut
where
schemaname not like 'pg_%'
and relname not like 'pg_%'
and relname not like '%sql%'"""
version=11
withdbname=false
tagvalue="dbname,tblfullname"
measurement="geo.pgtblstat"

Expected behavior:

I would like to get data from all databases on the server

Actual behavior:

I only get metrics from postgres DB (the database defined in the address.
But If I changed this to another database on the server then I get the metrics from that database only.

[danielnelson]

I think you need withdbname = true, this should add IN ('gaia_core', 'postgres') to the query. Let me know if that doesn't fix it and I'll reopen.

[julrodriguez]

I think you need withdbname = true, this should add IN ('gaia_core', 'postgres') to the query. Let me know if that doesn't fix it and I'll reopen.

Adding withdbname = true didnt fix the issue;

[[inputs.postgresql_extensible.query]]
sqlquery="""select (current_database())::varchar as dbname,
quote_ident(schemaname)||'.'||quote_ident(relname) as tblfullname,
pg_relation_size(relid) as table_size_b,
pg_total_relation_size(relid) as total_relation_size_b,
pg_relation_size((select reltoastrelid from pg_class where oid = ut.relid)) as toast_size_b,
extract(epoch from now() - greatest(last_vacuum, last_autovacuum)) as seconds_since_last_vacuum,
extract(epoch from now() - greatest(last_analyze, last_autoanalyze)) as seconds_since_last_analyze,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
from
pg_stat_all_tables ut
where
schemaname not like 'pg_%'
and relname not like 'pg_%'
and relname not like '%sql%'
and current_database() """
version=11
withdbname=true
tagvalue="dbname,tblfullname"
measurement="geo.pgtblstat"

[julrodriguez]

As soon as I change the database name in the connection string from postgres to gaia_core then the metrics start being collected on this database.

address = "host=localhost user=dbuser password=xxxxx database=gaia_core"
databases = ["gaia_core","postgres"]

@danielnelson

The documentation on postgresql_extensible states that the database specified on the connection string is just for initiating the connection. but this doesnt seem to be true.

Documentation:
All connection parameters are optional.
Without the dbname parameter, the driver will default to a database
with the same name as the user. This dbname is just for instantiating a
connection with the server and doesn't restrict the databases we are trying
to grab metrics for.

[danielnelson]

Hmm, this is strange because the value in the DSN should be dbname like:

address = "host=localhost user=dbuser password=xxxxx dbname=gaia_core"

However, maybe this format is also supported and it somehow affects permissions?

https://www.postgresql.org/docs/9.6/libpq-connect.html#LIBPQ-PARAMKEYWORDS

[julrodriguez]

@danielnelson ; I just changed it to dbname and still dont get metrics (pg_stat_all_tables); only I get them if I switch specifically to the other DB. It doesnt get metrics for all databases on the server.
Note: Im using a superuser .
pg_stat_all_tables: One row for each table in the current database, showing statistics about accesses to that specific table
So I guess if the plugin is not able to switch to the other datbaases on the server that's why I cannot get the metrics. it needs to connect using the same user to the rest of the databases. and I believe that's not happening.

[[inputs.postgresql_extensible]]
address = "host=localhost user=xxxxx password=xxxxx dbname=postgres"
databases = ["gaia_core","postgres"]

[SanderDR]

same issue, could someone please check this out ?

[stedounet]

I could plan to take time and make a PR @danielnelson
I think "databases" and "ignored_databases" should be used to launch queries on each database, with some for loop

Some parameter like 'doNotRunOnMultipleDB' (with better naming) would need to be added as well to avoid useless duplicatas on some tables (pg_stat_activity for exemple, as the results is identical for every database)

[danielnelson]

This would be a breaking change, and a fairly large modification to the plugin as it introduces another layer of concurrency. There is a new set of SQL plugins in the works for postgres, mysql, sqlserver and I think we should this functionality there as an array of connection strings.

[stedounet]

It could be implemented to be retrocompatible

I understand you prefer to work toward this new generic plugin, but postgres_extensible today cannot get the work done for a lot of critical metrics (vacuum metrics, analyze metrics, queries performance)
This feature will not be that useful on other engine, this issue is really a postgres one

To be precise about the issue, every view/functions used for metrics are derived from pg_class table.
This table is unique per database, and you cannot access it from another database. This is why we need to connect to each databases