Query exporter

The query exporter has a complex installation process that may cause issues with the operation of Python applications on the server, so it is recommended to run it in a separate container. This guide will describe the process of running query_exporter in Docker(Debian/Ubuntu), as well as in Podman(RHEL/CentOs).

Prepare config file
Run in docker in daemon mode;
Run in podman in daemon mode
Configure access and encryption
Configure firewall
Check metrics

1. Prepare config file.

Check documentation, available on query_exporter GitHub page.
Create folder and config file for you database, for example we create for sqllite. Run this commands in terminal

Bash

sudo mkdir -p /usr/local/bin/query_exporter

Create config file with nano.

Bash

sudo nano /usr/local/bin/query_exporter/config.yaml

And paste into file

Bash

databases:
  db1:
    dsn: sqlite://
    connect-sql:
      - PRAGMA application_id = 123
      - PRAGMA auto_vacuum = 1
    labels:
      region: us1
      app: app1
  db2:
    dsn: sqlite://
    keep-connected: false
    labels:
      region: us2
      app: app1

metrics:
  metric1:
    type: gauge
    description: A sample gauge
  metric2:
    type: summary
    description: A sample summary
    labels: [l1, l2]
    expiration: 24h
  metric3:
    type: histogram
    description: A sample histogram
    buckets: [10, 20, 50, 100, 1000]
  metric4:
    type: enum
    description: A sample enum
    states: [foo, bar, baz]

queries:
  query1:
    interval: 5
    databases: [db1]
    metrics: [metric1]
    sql: SELECT random() / 1000000000000000 AS metric1
  query2:
    interval: 20
    timeout: 0.5
    databases: [db1, db2]
    metrics: [metric2, metric3]
    sql: |
      SELECT abs(random() / 1000000000000000) AS metric2,
             abs(random() / 10000000000000000) AS metric3,
             "value1" AS l1,
             "value2" AS l2
  query3:
    schedule: "*/5 * * * *"
    databases: [db2]
    metrics: [metric3, metric4]
    sql: |
      SELECT value FROM (
        SELECT "foo" AS metric4 UNION
        SELECT "bar" AS metric3 UNION
        SELECT "baz" AS metric4
      )
      ORDER BY random()
      LIMIT 1

2. Run exporter in docker Debian (Ubuntu).

We are run query_exporter with docker in daemon mode with restart policy “unless-stopped”. In this mode container restart after reboot and all other interraption. Until you not stop container manualy. Run:

Bash

docker run -u 1000:1000 -d --restart unless-stopped -p 9560:9560/tcp -v /usr/local/bin/query_exporter:/config adonato/query-exporter:latest

After container start your query metrics exposed on http://127.0.0.1:9560

3. Run exporter in podman Fedora (RHEL, CentOS).

We are run query_exporter with podman in daemon mode with restart policy “unless-stopped”. In this mode container restart after reboot and all other interraption. Until you not stop container manualy. Run:

Bash

podman run -u 1000:1000 -d --restart unless-stopped -p 9560:9560/tcp -v /usr/local/bin/query_exporter:/config adonato/query-exporter:latest

After container start your query metrics exposed on http://127.0.0.1:9560

4. Configure access and encryption.

For configure access and encryption need install Nginx reverse proxy as it configured in this doc Install NGINX as reverse proxy to add basic auth and tls encription for exporters not support this out of box

5. Configure firewall

Configure firewall on resource

Nginx revese proxy uses default port 443, so allow it in firewall on resource.

Fedora (RHEL, CentOS)

Bash

sudo firewall-cmd --zone=public --add-port=443/tcp --permanent
sudo systemctl reload firewalld

Check firewall configuration

Bash

sudo firewall-cmd --list-all

You should see in output:

ports: 443/tcp

Debian (Ubuntu)

Bash

sudo ufw allow 443

You should see in output:

Bash

Rules updated
Rules updated (v6)

Check firewall configuration

Bash

sudo ufw status

You should see in output:

Bash

To                             Action          From
--                             ------          ----
443                           ALLOW           Anywhere

its good

6. Check metrics

Check with browser if it is accessible at:

https://resource-hostname-or-ip/metrics

After entering exporter’s user name and password you should see page like this:

Bash

# HELP database_errors_total Number of database errors
# TYPE database_errors_total counter
# HELP queries_total Number of database queries
# TYPE queries_total counter
queries_total{database="pg",query="active_customers",status="success"} 66.0
queries_total{database="pg",query="active_level_1_customers",status="success"} 66.0
queries_total{database="pg",query="active_level_2_customers",status="success"} 66.0
...

Now blackbox exporter is ready to accept data and then expose it to Prometheus.