postgres_exporter pg_stat_activity

Could you give me some advice for this problem? Thanks My metrics is 0，for example: the pg_stat_activity_count as follows: pg_stat_activity_count{datname="check_split_004123145c2d11edb9110025908bebf4",server="0.0.0.0:5432",state="active"} 0 pg_stat_activity_count{datname="check_split_004123145c2d11edb9110025908bebf4",server="0.0.0.0:5432",state="disabled"} 0 pg_stat_activity_count{datname="check_split_004123145c2d11edb9110025908bebf4",server="0.0.0.0:5432",state="fastpath function call"} 0 pg_stat_activity_count{datname="check_split_004123145c2d11edb9110025908bebf4",server="0.0.0.0:5432",state="idle"} 0 pg_stat_activity_count{datname="check_split_004123145c2d11edb9110025908bebf4",server="0.0.0.0:5432",state="idle in transaction"} 0 pg_stat_activity_count{datname="check_split_004123145c2d11edb9110025908bebf4",server="0.0.0.0:5432",state="idle in transaction (aborted)"} 0 pg_stat_activity_count{datname="check_split_076881c45b7311edb4d2002590f91981",server="0.0.0.0:5432",state="active"} 0 pg_stat_activity_count{datname="check_split_076881c45b7311edb4d2002590f91981",server="0.0.0.0:5432",state="disabled"} 0 pg_stat_activity_count{datname="check_split_076881c45b7311edb4d2002590f91981",server="0.0.0.0:5432",state="fastpath function call"} 0 pg_stat_activity_count{datname="check_split_076881c45b7311edb4d2002590f91981",server="0.0.0.0:5432",state="idle"} 0 pg_stat_activity_count{datname="check_split_076881c45b7311edb4d2002590f91981",server="0.0.0.0:5432",state="idle in transaction"} 0 pg_stat_activity_count{datname="check_split_076881c45b7311edb4d2002590f91981",server="0.0.0.0:5432",state="idle in transaction (aborted)"} 0 pg_stat_activity_count{datname="check_split_0a18ba8c5c1411edacd90025908bebb9",server="0.0.0.0:5432",state="active"} 0 pg_stat_activity_count{datname="check_split_0a18ba8c5c1411edacd90025908bebb9",server="0.0.0.0:5432",state="disabled"} 0 pg_stat_activity_count{datname="check_split_0a18ba8c5c1411edacd90025908bebb9",server="0.0.0.0:5432",state="fastpath function call"} 0 pg_stat_activity_count{datname="check_split_0a18ba8c5c1411edacd90025908bebb9",server="0.0.0.0:5432",state="idle"} 0 pg_stat_activity_count{datname="postgres",server="0.0.0.0:5432",state="active"} 2 pg_stat_activity_count{datname="postgres",server="0.0.0.0:5432",state="disabled"} 0 pg_stat_activity_count{datname="postgres",server="0.0.0.0:5432",state="fastpath function call"} 0 pg_stat_activity_count{datname="postgres",server="0.0.0.0:5432",state="idle"} 0 pg_stat_activity_count{datname="postgres",server="0.0.0.0:5432",state="idle in transaction"} 0 pg_stat_activity_count{datname="postgres",server="0.0.0.0:5432",state="idle in transaction (aborted)"} 0

This is the actual number of connections to my database ps -ef| grep postgres |wc -l 179

Nov 07 '22 04:11 githubtianfeng

What does the pg_stat_activity table say in your database? These numbers should match. Did you redact the server ip? That doesn't look like a proper IP address.

Nov 07 '22 23:11 sysadmind

First thanks for your response. Surprisingly, my other matrics is not 0, such as pg_database_size_bytes, pg_stat_database_blks_hit, pg_stat_database_blks_read. Here is my other matrics as follows: pg_database_size_bytes{datname="check_split_c90c68805e6211ed8f620025908bebb9",server="0.0.0.0:5432"} 5.891236012e+09 pg_database_size_bytes{datname="check_split_d7d1be9a5f0411eda8c5002590f91981",server="0.0.0.0:5432"} 1.585332396e+09 pg_stat_database_blks_read{datid="1108795958",datname="check_split_c6adb6e45e6611eda1900025908bebb9",server="0.0.0.0:5432"} 2.760041e+06 pg_stat_database_tup_updated{datid="1114641718",datname="work_expimp_8c193e245ec311eda5b20025908bebb9",server="0.0.0.0:5432"} 97732 Here is my exporter environment variables: DATA_SOURCE_NAME="postgresql://map:[email protected]:${port}/postgres?sslmode=disable"

It seems like a proper IP address.

Nov 08 '22 02:11 githubtianfeng

Hi, I have also encountered the same problem. How did you solve it?

Apr 01 '24 02:04 Wj-creat

Set the follwing env and check your queries.yaml has included pg_stat_activity may resove your problem. export DATA_SOURCE_NAME="postgresql://map:79162qgh@${HOST_NAME}:${port}/postgres?sslmode=disable" export PG_EXPORTER_DISABLE_DEFAULT_METRICS=true export PG_EXPORTER_EXTEND_QUERY_PATH=${WORKDIR}/queries.yaml

pg_stat_activity:
  query: |
    SELECT
    pg_database.datname,
    tmp.state,
    COALESCE(count,0) as count,
    COALESCE(max_tx_duration,0) as max_tx_duration
    FROM
    ....

Last start the pg_exporter. nohup ./$BIN_NAME --web.listen-address=:8099 1> nohup.out 2>&1 &

Apr 20 '24 07:04 githubtianfeng

pg_stat_activity_count is 0