pg_stat_statements metrics collection issue with postgres-exporter

[aunghtetnay]

I am encountering an issue with postgres-exporter where it fails to collect metrics from the pg_stat_statements extension in my PostgreSQL database. Here are the details of my setup and the problem:

Setup Details:

Docker Compose Configuration:
services:
grafana:
image: grafana/grafana:latest
volumes:
- grafana-storage:/var/lib/grafana
ports:
- 3000:3000

prometheus:
image: prom/prometheus:latest
ports:
- 9090:9090
volumes:
- ./prometheus.yml:/etc/prometheus/prometheus.yml:ro

postgres-exporter:
image: quay.io/prometheuscommunity/postgres-exporter
ports:
- 9187:9187
environment:
DATA_SOURCE_NAME: "postgresql://postgres:passsword@database-endpoint:5432/db-name?sslmode=disable"
volumes:
- ./queries.yml:/etc/queries.yml
command:
- '--web.listen-address=:9187 --web.telemetry-path=/metrics --collector.database_wraparound --collector.long_running_transactions --collector.postmaster --collector.process_idle --collector.stat_activity_autovacuum --collector.stat_statements --collector.stat_wal_receiver --collector.statio_user_indexes'
- '--extend.query-path=/etc/queries.yml'

volumes:
grafana-storage:
and
queries.yml Configuration for pg_stat_statements:
pg_stat_statements:
query: |
SELECT
queryid,
query,
calls
FROM
pg_stat_statements;
metrics:
- queryid:
usage: "LABEL"
description: "Query ID"
- query:
usage: "LABEL"
description: "SQL query text"
- calls:
usage: "GAUGE"
description: "Number of times executed"

Issue Details:

Expected Behavior:
postgres-exporter should collect metrics from pg_stat_statements and expose them at http://localhost:9187/metrics.

Actual Behavior:
The exporter returns HTTP status 500 Internal Server Error when querying http://postgres-exporter:9187/metrics.

Additional Information:

I have verified that the pg_stat_statements extension is installed and enabled in my PostgreSQL database.
The SELECT query from pg_stat_statements works correctly when executed directly in the database.
Error logs from postgres-exporter show no specific errors related to the pg_stat_statements query itself.

[abhinav-zeeve]

I am also facing the same issue, I am getting all the metric, except the one associated with pg_stat_statements_*

[Gu1nness]

I do have the exact same issue here

[jhsee11]

i have the same issue, is all metrics for pg_stat_statements should be collected by pg_exporter by default ?
or we supposed to use queries.yml for it ?

[Muti9289]

Im also stuck at that point, i've got all checked and the journalctl doesnt give me any critical error, but the pg_statement info is blank

root@smvmoodledev:/etc/systemd/system# curl http://localhost:9187/metrics | grep -i pg_stat_statements
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0# HELP pg_settings_pg_stat_statements_max Server Parameter: pg_stat_statements.max

TYPE pg_settings_pg_stat_statements_max gauge

pg_settings_pg_stat_statements_max{server="localhost:5432"} 50000

HELP pg_settings_pg_stat_statements_save Server Parameter: pg_stat_statements.save

TYPE pg_settings_pg_stat_statements_save gauge

pg_settings_pg_stat_statements_save{server="localhost:5432"} 1

HELP pg_settings_pg_stat_statements_track_planning Server Parameter: pg_stat_statements.track_planning

TYPE pg_settings_pg_stat_statements_track_planning gauge

pg_settings_pg_stat_statements_track_planning{server="localhost:5432"} 1

HELP pg_settings_pg_stat_statements_track_utility Server Parameter: pg_stat_statements.track_utility

TYPE pg_settings_pg_stat_statements_track_utility gauge

pg_settings_pg_stat_statements_track_utility{server="localhost:5432"} 1
100 121k 0 121k 0 0 1661k 0 --:--:-- --:--:-- --:--:-- 1685k
root@smvmoodledev:/etc/systemd/system#

[Muti9289]

To all of you, try using another user for postgres_exporter, i was using the root(that it supposed to have all the privilege) doesnt work but using a new user created as they explain in the github it works). https://github.com/prometheus-community/postgres_exporter where its the title Running as non-superuser.