Can I run sql exporter in a container ? Can't find any metric except sql_exporter_build_info

[hoangquochung1110]

Despite recommendation of running SQL Exporter in k8s

I'd like to dockerize postgres db, prometheus and SQL Exporter.

My docker compose:

version: '3.8'

services:
  prometheus:
    image: prom/prometheus:${PROMETHEUS_VERSION:-v2.42.0}
    container_name: prometheus
    restart: unless-stopped
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--log.level=error'
      - '--storage.tsdb.path=/prometheus'
      - '--storage.tsdb.retention.time=7d'
      - '--web.console.libraries=/usr/share/prometheus/console_libraries'
      - '--web.console.templates=/usr/share/prometheus/consoles'
      - '--web.external-url=http://localhost:9090'
    volumes:
      - ./prometheus-local.yml:/etc/prometheus/prometheus.yml
      - prometheus-data:/prometheus
    ports:
      - 9090:9090

  sql-exporter:
    image: ghcr.io/justwatchcom/sql_exporter
    container_name: sql-exporter
    volumes:
      - ./sql_exporter.yml:/config/config.yml
    environment:
      - CONFIG=/config/config.yml
    ports:
      - 9237:9237
    restart: always

  db:
    image: postgres:14
    environment:
      - POSTGRES_DB=
      - POSTGRES_USER=
      - POSTGRES_PASSWORD=
    ports:
      - 5432:5432
    restart: unless-stopped
    volumes:
      - ./initdb.d/:/docker-entrypoint-initdb.d/

config.yml (I copied exactly as in README)

---
# jobs is a map of jobs, define any number but please keep the connection usage on the DBs in mind
jobs:
  # each job needs a unique name, it's used for logging and as an default label
- name: "example"
  # interval defined the pause between the runs of this job
  interval: '5m'
  # cron_schedule when to execute the job in the standard CRON syntax
  # if specified, the interval is ignored
  cron_schedule: "0 0 * * *"
  # connections is an array of connection URLs
  # each query will be executed on each connection
  connections:
  - 'postgres://postgres@localhost/postgres?sslmode=disable'
  # startup_sql is an array of SQL statements
  # each statements is executed once after connecting
  startup_sql:
  - 'SET lock_timeout = 1000'
  - 'SET idle_in_transaction_session_timeout = 100'
  # queries is a map of Metric/Query mappings
  queries:
    # name is prefied with sql_ and used as the metric name
  - name: "running_queries"
    # help is a requirement of the Prometheus default registry, currently not
    # used by the Prometheus server. Important: Must be the same for all metrics
    # with the same name!
    help: "Number of running queries"
    # Optional: Column to use as a metric timestamp source.
    # Leave unset if it's not needed
    timestamp: "created_at"
    # Labels is an array of columns which will be used as additional labels.
    # Must be the same for all metrics with the same name!
    # All labels columns should be of type text, varchar or string
    labels:
      - "datname"
      - "usename"
    # Values is an array of columns used as metric values. All values should be
    # of type float
    values:
      - "count"
    # Query is the SQL query that is run unalterted on the each of the connections
    # for this job
    query:  |
            SELECT now() as created_at, datname::text, usename::text, COUNT(*)::float AS count
            FROM pg_stat_activity GROUP BY created_at, datname, usename;
    # Consider the query failed if it returns zero rows
    allow_zero_rows: false

My logs

{"build_context":"(go=go1.20.14, platform=linux/arm64, user=, date=, tags=unknown)","caller":"main.go:56","msg":"Starting sql_exporter","ts":"2024-04-04T10:01:40.760017928Z","version_info":"(version=, branch=, revision=e4c2a529aacd1531ca0e2d22da73a3340fe84846)"}
{"caller":"exporter.go:86","cron_schedule":"0 0 * * *","level":"info","msg":"Scheduled CRON job","name":"example","ts":"2024-04-04T10:01:40.76431047Z"}
{"caller":"main.go:79","level":"info","listenAddress":":9237","msg":"Listening","ts":"2024-04-04T10:01:40.768264928Z"}
{"build_context":"(go=go1.20.14, platform=linux/arm64, user=, date=, tags=unknown)","caller":"main.go:56","msg":"Starting sql_exporter","ts":"2024-04-04T10:02:12.372603304Z","version_info":"(version=, branch=, revision=e4c2a529aacd1531ca0e2d22da73a3340fe84846)"}
{"caller":"exporter.go:86","cron_schedule":"0 0 * * *","level":"info","msg":"Scheduled CRON job","name":"example","ts":"2024-04-04T10:02:12.375065971Z"}
{"caller":"main.go:79","level":"info","listenAddress":":9237","msg":"Listening","ts":"2024-04-04T10:02:12.375117721Z"}
{"build_context":"(go=go1.20.14, platform=linux/arm64, user=, date=, tags=unknown)","caller":"main.go:56","msg":"Starting sql_exporter","ts":"2024-04-04T10:15:35.725275842Z","version_info":"(version=, branch=, revision=e4c2a529aacd1531ca0e2d22da73a3340fe84846)"}
{"caller":"exporter.go:86","cron_schedule":"0 0 * * *","level":"info","msg":"Scheduled CRON job","name":"example","ts":"2024-04-04T10:15:35.730641092Z"}
{"caller":"main.go:79","level":"info","listenAddress":":9237","msg":"Listening","ts":"2024-04-04T10:15:35.730894426Z"}

The problem is that I can't find any metric named running_queries as defined in config.yml at http://localhost:9237/metrics and prometheus dashboard (tried with and w/o sql_ prefix). The only metric (except ones from Go) I got is sql_exporter_build_info

[dewey]

Are there any running queries in your PG instance?

[hoangquochung1110]

Yes I tried to make a few queries to my postgres instances

[dewey]

Don't really have the resources to debug individual issues but here's some hints. Try to run it without Docker and see if it can reach your PG instance, run a simple count(*) query to force it to always have results, watch the logs and see if there's any issues when it's running the cron job.

[hoangquochung1110]

it was docker networking issue. thanks for the hint