Can't read pg_stat_statements for unknown reason

[tommiv]

I'm trying to get a mean query time metric via queries.yaml in this naive way:

pg_query_time:
  query: "SELECT avg(total_time / calls) as mean_query_time FROM public.pg_stat_statements"
  metrics:
    - mean_query_time:
        usage: "GAUGE"
        description: "Mean query time over all databases"

However, the postgres_exporter log is flooded with such entries:

level=info msg="Error running query on database \"myserver:5432\": pg_query_time pq: relation \"public.pg_stat_statements\" does not exist" source="postgres_exporter.go:1356"

level=error msg="queryNamespaceMappings returned 1 errors" source="postgres_exporter.go:1474"

More context on this.

1) I use unprivileged user postgres_exporter 2) This user HAVE all needed permissions to read pg_stat_statements view. 3) pg_stat_statements is installed for schema public 4) postgres_exporter user HAS schema public in his defaultSearchPath 5) I can copy the DSN which I use as DATA_SOURCE_NAME env, connect to it with psql and run the exact query from my sample and it works:

DATA_SOURCE_NAME=postgresql://postgres_exporter:password@myserver:5432/postgres

psql -d "postgresql://postgres_exporter:password@myserver:5432/postgres"

postgres=> SELECT avg(total_time / calls) as mean_query_time FROM public.pg_stat_statements;
  mean_query_time
-------------------
 5.517902028453581
(1 row)

6) It works in psql without explicit shema public. declaration 7) This is the most interesting part: it looks like this metric works. I can see it in prometheus and the graph in Grafana works properly. I assume that this query fails for only one particular database as the exceptions says returned 1 errors. I have 5 DBs currently: template0 and template1 (excluded via --exclude-databases), postgres, and two product DBs, stage & prod. 8) Finally. If I have 3 DBs to scrape, and two of them are the same with just different data, and the error says that one of them doesn't have a relation, it's probably obvious to assume that the 3rd (postgres) is a culprit. So I excluded it as well. This doesn't help at all, I still see the same error.

I'm not sure how to debug this, taking into account that everything work as expected when I try to query it by hand and fails inside the postgres_exporter. I've tried to track the source code starting from the :1474 but haven't found anything useful. I'm going to disable this query now. Any ideas?

[tommiv]

Ok, I've investigated further and I think I figured it out. Please correct me if I'm wrong. For the common setup it's enough to scrape postgres db only. There is no point to use --auto-discover-databases as postgres_exporter will connect to every DB it discovered and run custom queries against it. This creates two problems: – you need to install pg_stat_statements for every database in order to not get errors from my first post – if you do so, you'll just get duplicated metrics

The solution: – I've removed --auto-discover-databases and the error gone while I still get all the metrics I needed – I've modified my initial query to a proper one: SELECT datname, sum(mean_time*calls)/sum(calls) as mean_time FROM pg_stat_statements JOIN pg_database ON pg_stat_statements.dbid = pg_database.oid GROUP BY datname. It has two advantages: mean time is now grouped per database and the calculation itself is now probably works right.

[joshuaclausen]

@tommiv, that fixed it for me as well. I added the pg_stat_statements to shared_preload_libraries, along with pg_stat_statements.max and pg_stat_statements.track in my postgres.conf file. Then I ran "CREATE EXTENSION pg_stat_statements;" once, and then I made sure to remove the "--auto-discover-databases" option from my postgres_exporter startup config. After that I no longer saw the errors you had also seen.

[RushikeshChavan96]

@tommiv / @wrouesnel , What if I am using postgres-exporter as third party chart and its deployed on somewhere on cloud. Don't have access of values.yaml file to delete --auto-discover-databases, but I can override it through custom *.yaml file. Please suggest.

I am continuously getting this error in postgres-exporter pod logs: time="2022-09-07T10:05:11Z" level=info msg="Error running query on database \"twxdev-pgflex.postgres.database.azure.com:5432\": pg_stat_statements pq: relation \"pg_stat_statements\" does not exist" source="postgres_exporter.go:1356" time="2022-09-07T10:05:11Z" level=error msg="queryNamespaceMappings returned 1 errors" source="postgres_exporter.go:1474"

[awais-letsbuild]

I fixed my issue using follow instructions on AWS RDS .

How do I know if pg_stat_statements is enabled? Try to select from the table SELECT * FROM pg_stat_statements; If the table doesn't exist, you will get an error message: ERROR: relation "pg_stat_statements" does not exist

On AWS RDS: By default, on RDS the contrib package is installed, and the configuration is already set. So you just need to enable the pg_stat_statements extension, by running on postgres database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements; After that you will be able to see that table.

[michalschott]

For Aurora-PostgreSQL-compatible all you need it to execute

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

on postgres database