pg_stat_statements pq: relation "pg_stat_statements" does not exist"

[chinaboy007]

me too I have the same problem。

postgrepsql version: 12.0 postgreps-exporter version: 0.14.0

i have aleady install pg_stat_statements extension;

logs

ts=2023-09-28T09:10:37.268Z caller=main.go:86 level=warn msg="Error loading config" err="Error opening config file "postgres_exporter.yml": open postgres_exporter.yml: no such file or directory" ts=2023-09-28T09:10:37.268Z caller=proc.go:250 msg="Excluded databases" databases=[] ts=2023-09-28T09:10:37.268Z caller=main.go:99 level=warn msg="The extended queries.yaml config is DEPRECATED" file=queries.yaml ts=2023-09-28T09:10:37.269Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9187 ts=2023-09-28T09:10:37.269Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9187 ts=2023-09-28T09:10:43.986Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=10.16.65.12:5432 ts=2023-09-28T09:10:43.988Z caller=postgres_exporter.go:613 level=info msg="Semantic version changed" server=10.16.65.12:5432 from=0.0.0 to=12.0.0 ts=2023-09-28T09:10:44.026Z caller=namespace.go:236 level=info err="Error running query on database "192.168.2.11:5432": pg_stat_statements pq: relation "pg_stat_statements" does not exist" ts=2023-09-28T09:10:44.027Z caller=postgres_exporter.go:682 level=error err="queryNamespaceMappings returned 1 errors" ts=2023-09-28T09:10:51.891Z caller=namespace.go:236 level=info err="Error running query on database "192.168.2.11:5432": pg_stat_statements pq: relation "pg_stat_statements" does not exist" ts=2023-09-28T09:10:51.911Z caller=postgres_exporter.go:682 level=error err="queryNamespaceMappings returned 1 errors"

[omgnull]

The solution for me was the altering default search path and add public schema.

GRANT pg_monitor TO postgres_exporter;
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog,public;

[vvitad]

thanks @omgnull

[candlerb]

I was also getting the same error:

2023-12-07 22:39:38.110 UTC [339] postgres@postgres ERROR:  relation "pg_stat_statements" does not exist at character 536

This is postgres 12 under Ubuntu 20.04. For simplicity I'm not running the exporter with its own postgres_exporter user, I just have the following environment variables set:

DATA_SOURCE_NAME='user=postgres host=/var/run/postgresql/ sslmode=disable'
PG_EXPORTER_EXTEND_QUERY_PATH=/etc/prometheus/queries.yaml

I note from postgres documentation:

The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module. In addition, query identifier calculation must be enabled in order for the module to be active, which is done automatically if compute_query_id is set to auto or on, or any third-party module that calculates query identifiers is loaded.

When pg_stat_statements is active, it tracks statistics across all databases of the server. To access and manipulate these statistics, the module provides views pg_stat_statements and pg_stat_statements_info, and the utility functions pg_stat_statements_reset and pg_stat_statements. These are not available globally but can be enabled for a specific database with CREATE EXTENSION pg_stat_statements.

So I set the following in /etc/postgresql/12/main/postgresql.conf:

shared_preload_libraries = 'pg_stat_statements' # (change requires restart)

then systemctl restart postgresql, and I also did CREATE EXTENSION pg_stat_statements; in psql when connected as the postgres user.

With those changes, it's happy now.

[vvitad]

My solution was to explicitly say schema name: "public.pg_stat_statements"