pg_exporter collector issue - metrics not appearing in Prometheus

[anadyrov]

Hello,

We are configuring pg_exporter based on the Pigsty documentation to monitor PostgreSQL.
We are using the latest version of vonng/pg_exporter.
The pg_exporter_pg-1.yml configuration is correct, and the collector/*.yml files are standard, without any custom modifications.

What has been configured:
pg_exporter is running as a systemd service
collector/.yml files were copied from the official vonng/pg_exporter repository
queries: is set as - file: collector/.yml
Connection to the PostgreSQL database works (pg_up = 1)
Some basic metrics appear (pg_up, pg_exporter_*), however...

The issue:
The collections from collector/.yml are not being loaded, specifically:
Metrics like pg_stat_database, pg_stat_activity, pg_stat_monitor, etc. are missing
In journalctl logs we see: metrics count: 0, installed: includeDatabase, excludeDatabase, but no mention of pg_stat_ collections
collector_dir path and queries: section are verified - no YAML parsing errors

We have already tried:
Validating all YAML files -syntax is correct
Testing pg_exporter with a minimal config
Restarting the service and checking logs but metrics from collector/.yml do not appear
Could you please help us identify why the metrics are not being loaded, and how to correctly activate pg_stat_ and other collections?

Thank you!

[Vonng]

use the --config to specifiy collector config file? and try to use --log.level="debug" to get more detailed startup log

[anadyrov]

1. pg_exporter config /etc/pg_exporter/pg_exporter_pg-1.yml
   global:
   scrape_interval: 10s

server:
web_listen_address: ":9630"
disableCache: false
autoDiscovery: true

postgres:
dsn: "postgres://postgres:secret@10.0.0.22:5432/postgres?sslmode=disable"

collector:
files:
- /etc/pg_exporter/collector/pg_stat_monitor.yml

includeDatabase:
postgres: true

excludeDatabase:
template0: true
template1: true

constLabels:
job: "pg"
cls: "pg-test"
ins: "pg-1"
node: "kgc-b-db-pg-1"
ip: "10.0.0.22"
2. Collector config /etc/pg_exporter/collector/pg_stat_monitor.yml
queries:

• name: pg_stat_monitor
  sql: |
  SELECT userid, dbid, queryid, calls, total_exec_time, rows
  FROM pg_stat_monitor;
  metrics:
  • name: pg_stat_monitor_calls
    type: counter
    help: Number of calls
    value: calls
    labels: [userid, dbid, queryid]

3. Systemd юнит-файл /etc/systemd/system/pg_exporter_pg-1.service
   [Unit]
   Description= PG Exporter @ 9630 for pg-1@10.0.0.22:5432
   Documentation=https://github.com/Vonng/pg_exporter
   After=network.target

[Service]
EnvironmentFile=-/etc/default/pg_exporter_pg-1
ExecStart=/usr/bin/pg_exporter --config=/etc/pg_exporter/pg_exporter_pg-1.yml --log.level=debug
ExecReload=/usr/bin/kill -HUP $MAINPID
Restart=on-failure
RestartSec=5s

CPUQuota=10%
MemoryLimit=200M

[Install]
WantedBy=multi-user.target
4. /etc/default/pg_exporter_pg-1
DATA_SOURCE_NAME=postgres://postgres:secret@10.0.0.22:5432/postgres?sslmode=disable

5. psql -h 10.0.0.22 -U postgres -d postgres -c "SELECT userid, dbid, queryid, calls, total_exec_time, rows FROM pg_stat_monitor LIMIT 5;"
   userid | dbid | queryid | calls | total_exec_time | rows
   --------+------+----------------------+-------+--------------------+------
   10 | 5 | 6374888885576088997 | 6 | 1.3302859999999999 | 6
   10 | 5 | -8935392832077029178 | 6 | 0.564327 | 6
   10 | 5 | 6374888885576088997 | 6 | 1.669806 | 6
   10 | 5 | -8935392832077029178 | 6 | 0.728105 | 6
   10 | 5 | -8935392832077029178 | 6 | 0.5170980000000001 | 6
   (5 rows)

Problem
Despite all configurations being in place, pg_exporter still fails to connect to the specified PostgreSQL instance.
In the logs, we see:
fallback on default url: postgresql:///?sslmode=disable
fail connecting to primary server: dial tcp [::1]:5432: connect: connection refused
The DATA_SOURCE_NAME environment variable is correctly set and verified (via /proc//environ), but it seems to be ignored by pg_exporter.

The /metrics endpoint does not contain any pg_stat_monitor_, pg_up, or pg_exporter_ metrics.
Question
What is the correct and reliable way to specify the PostgreSQL connection string for pg_exporter, if DATA_SOURCE_NAME (set via Environment or EnvironmentFile) is being ignored?
Are there any documented flags, environment variables, or YAML config fields that can guarantee pg_exporter connects to a remote PostgreSQL host instead of defaulting to localhost?

pg_exporter --config=/etc/pg_exporter/pg_exporter_pg-1.yml --log.level=debug
time=2025-05-13T13:04:19.641+05:00 level=DEBUG msg="init pg_exporter, configPath=/etc/pg_exporter/pg_exporter_pg-1.yml constLabels= disableCache=false autoDiscovery=false excludeDatabase=template0,template1,postgres includeDatabase= connectTimeout=100ms webConfig=[:9630] metricPath=/metrics"
time=2025-05-13T13:04:19.641+05:00 level=WARN msg="fail retrieving target url, fallback on default url: postgresql:///?sslmode=disable"
time=2025-05-13T13:04:19.641+05:00 level=INFO msg="retrieve config path /etc/pg_exporter/pg_exporter_pg-1.yml from command line"
time=2025-05-13T13:04:19.642+05:00 level=DEBUG msg="load 7 queries from /etc/pg_exporter/pg_exporter_pg-1.yml, "
time=2025-05-13T13:04:19.642+05:00 level=DEBUG msg="exporter init with 7 queries"
time=2025-05-13T13:04:19.642+05:00 level=DEBUG msg="check primary server connectivity"
time=2025-05-13T13:04:19.643+05:00 level=ERROR msg="fail connecting to primary server: fail fetching server version: dial tcp [::1]:5432: connect: connection refused, retrying in 10s"

[Vonng]

There's no DATA_SOURCE_NAME, just PG_EXPORTER_URL

[anadyrov]

Problem
pg_stat_monitor metrics are not exported via pg_exporter (vonng fork), despite the following:
The pg_stat_monitor extension is installed and active (returns rows via SELECT * FROM pg_stat_monitor LIMIT 1;);
pg_stat_monitor metrics do not appear in /metrics;
The pg_stat_monitor.yml file is valid and explicitly included in the configuration;
All other metrics are working correctly.

cat /etc/pg_exporter/collector/pg_stat_monitor.yml

---

name: pg_stat_monitor
sql: |
SELECT
datname,
userid::regrole::text AS user,
COALESCE(LEFT(encode(sha256(queryid::text::bytea), 'hex'), 12), 'unknown') AS queryid_hash,
sum(calls) AS calls,
sum(rows) AS rows,
sum(total_exec_time) / 1000 AS total_exec_time_seconds
FROM pg_stat_monitor
WHERE queryid IS NOT NULL
GROUP BY datname, userid, queryid;
metric_prefix: pg_stat_monitor
ttl: 10
min_version: 130000
columns:

• field: calls
  usage: COUNTER
  description: Total number of calls
  labels: [datname, user, queryid_hash]
• field: rows
  usage: COUNTER
  description: Total number of rows
  labels: [datname, user, queryid_hash]
• field: total_exec_time_seconds
  usage: GAUGE
  description: Total execution time in seconds
  labels: [datname, user, queryid_hash]

What is configured and working

1. pg_stat_monitor extension
   Installed via:
   pig ext add pg_stat_monitor -i kgc-b-db-pg-1
   Check:
   psql -h 10.0.0.22 -U postgres -c "SHOW shared_preload_libraries;"
   shared_preload_libraries

---

pg_stat_statements,auto_explain,pg_stat_monitor
(1 row)

2. pg_exporter (vonng)
   Running as a systemd service:
   systemctl status pg_exporter_pg-1
   ● pg_exporter_pg-1.service - PG Exporter @ 9630 for pg-1@10.0.0.22:5432
   Loaded: loaded (/etc/systemd/system/pg_exporter_pg-1.service; enabled; vendor preset: disabled)
   Active: active (running)
   Docs: https://github.com/Vonng/pg_exporter
   Environment:
   cat /etc/default/pg_exporter_pg-1
   DATA_SOURCE_NAME=postgres://postgres:secret@10.0.0.22:5432/postgres?sslmode=disable

3. Metrics that are already working
   pg_up, pg_exporter_build_info, pg_stat_activity, pg_stat_database
   Replication, slot, WAL, bgwriter, locks, etc.
   All standard pg_ metrics appear in /metrics, except pg_stat_monitor_*.

4. pg_exporter metric configuration
   Explicitly set in pg_exporter_pg-1.yml:
   global:
   scrape_interval: 10s

server:
web_listen_address: ":9630"
autoDiscovery: false
logLevel: DEBUG

postgres:
dsn: "postgres://postgres:secret@10.0.0.22:5432/postgres?sslmode=disable"

collector:
directory: /etc/pg_exporter/collector/

includeDatabase:
postgres: true

constLabels:
job: "pg"
cls: "pg-test"
ins: "pg-1"
node: "kgc-b-db-pg-1"
ip: "10.0.0.22"

5. Manual test in debug mode
   PG_EXPORTER_DEBUG=1
   PG_EXPORTER_URL="postgres://postgres:secret@10.0.0.22:5432/postgres?sslmode=disable"
   pg_exporter --config /etc/pg_exporter/pg_exporter_pg-1.yml

Logs:
INFO msg="server [postgres] planned with 6 queries, 6 installed, 0 discarded, installed: constLabels, global, server, postgres, collector, includeDatabase , discarded: "
Conclusion
Despite the correct installation of pg_stat_monitor and proper inclusion of the collector config in pg_exporter, the metrics are not being exported. There are no errors, and pg_stat_monitor does not appear in logs or /metrics.
Most likely, this is a problem with pg_exporter (vonng fork), which, for some reason, silently ignores the pg_stat_monitor collector even when the SQL is explicitly defined.

[Vonng]

Is your pg_stat_monitor collector generated by vibe coding tools?
because it is not even close to any other collectors in the config dir.

[anadyrov]

Thank you, we’ve updated the examples: https://github.com/pgsty/pg_exporter/tree/main/config

The pg_stat_monitor extension is installed,
the table contains data,
the YAML collector is in place and pg_exporter sees it.

It’s strange that we still don’t see any pg_stat_monitor metrics.

In the pg_exporter logs: installed: collector, includeDatabase, constLabels, global, server, postgres
I think we should be seeing something like: installed: ...pg_stat_monitor... ???

[Vonng]

does debug log output contains any useful infomation?

does the monitor user have correct search_path & privillege to use the extension?

[anadyrov]

Hello!

Could you please clarify if any manual configuration is required for pg_stat_monitor to work with pg_exporter? We noticed that there are no scripts for this on the official page: https://github.com/pgsty/pg_exporter/tree/main/config.

Are we correct in understanding that manual configuration is needed, or is this supposed to work automatically through an integrated UI with the current system? We would prefer an automated solution with such a UI, and we would like to understand how it should work in our case.

[anadyrov]

After reviewing the page https://pigsty.io/ext/stat/pg_stat_monitor/, we’ve concluded that Pigsty only handles the installation of the pg_stat_monitor extension, but does not commit to visualizing its output in the interface.

Apologies for the misunderstanding — it seems we overestimated what the product provides out of the box.

[Vonng]

we do not use the pg_stat_monitor by default. it has crash record on production server. So you can install & enable it by your own, and add customize metrics collector in pg_exporter.yml.