Create the sqlquery.yml configuration file.

In the created configuration file, specify database connection parameters in the receivers.sqlquery section:

receivers:
  sqlquery:
    driver: postgres
    host: localhost
    port: 5432
    database: postgres
    username: postgres
    password: ${env:POSTGRESQL_PASSWORD}
    # Additional driver-specific connection parameters
    additional_params:
      application_name: pgpro-otel-collector
      sslmode: disable
    # The time interval between query executions. Default: 10s
    collection_interval: 60s
    # Defines setup for the component's own telemetry
    telemetry:
      logs:
        # If true, each executed query is logged at debug level
        query: false
    # The maximum number of open connections to the Postgres Pro server. Default: 0 (unlimited)
    max_open_conn: 5

The password parameter supports environment variable substitution, as shown in the example. Special characters in the credentials are automatically URL-encoded to ensure proper connection string formatting.

Alternatively, use the datasource parameter to provide a complete connection string:

datasource: "host=localhost port=5432 user=postgres password=postgres application_name=pgpro-otel-collector sslmode=disable"

List queries to collect metrics. Each query consists of an SQL statement and a metrics section. There may be several metrics sections, but at least one such section is required. Each metric in the configuration produces one OpenTelemetry metric per row returned from the SQL query.

receivers:
  sqlquery:
    ...
    queries:
      - sql: >-
          SELECT p.id, p.name, p.price, sum(s.units) AS sold
          FROM products p LEFT JOIN sales s ON (p.id = product_id)
          GROUP BY p.id, p.name, p.price
        metrics:
          # Gauge metric example
          # The name assigned to the OpenTelemetry metric
          - metric_name: postgresql.products.price
            value_column: price
            data_type: gauge
            value_type: double
            description: Price of the product
            unit: rub
            static_attributes:
              database: postgres
            attribute_columns: ["name"]
          # Sum metric example
          - metric_name: postgresql.products.sold
            value_column: sold
            data_type: sum
            value_type: int
            description: Total count of sold products
            # Whether a cumulative sum value is monotonically increasing (i.e. never rolls over or resets)
            # Default = false
            monotonic: true
            aggregation: cumulative
            static_attributes:
              database: postgres
            attribute_columns: ["name"]

This example assumes the following database schema:

CREATE TABLE products (id INTEGER, name TEXT, price NUMERIC);
INSERT INTO products VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99);

CREATE TABLE sales (id BIGINT, product_id BIGINT, units INT, sold_at TIMESTAMP DEFAULT now());
INSERT INTO sales VALUES (1, 1, 10), (2, 2, 20), (2, 2, 50), (3, 3, 30);

The query creates two metrics: a gauge for current product prices and a cumulative sum for total units sold per product.

Use data_type to specify the metric type. Set it to gauge for metrics representing a current value, or sum for metrics representing an aggregated total. When using sum, set the aggregation parameter to either cumulative (default) or delta.

Configure exporters, processors, and the service pipeline:

...
exporters:
  prometheus:
    endpoint: :8889
    send_timestamps: true
  otlphttp/sqlquery/metrics:
    compression: ''
    endpoint: https://metrics.example.org:8080
    headers:
      X-Ppem-Source-Agent-Name: local
      X-Ppem-Source-Instance-Port: '5432'
processors:
  batch/sqlquery:
    send_batch_size: 2048
    timeout: 10s
service:
  # Telemetry for the collector itself
  telemetry:
    logs:
      # Sets the minimum enabled logging level
      # Values: debug, info, warn, error
      # Default = info
      level: info
  pipelines:
    metrics/sqlquery:
      receivers: [ sqlquery ]
      processors: [ batch/sqlquery ]
      exporters: [ prometheus, otlphttp/sqlquery/metrics ]

Start pgpro-otel-collector with the created configuration file:

build/pgpro-otel-collector/pgpro-otel-collector --config configs/sqlquery.yml

Create the metrics_transform.yml configuration file.

Add the configuration sections that match your needs from the examples below:

Configure exporters and the service pipeline in accordance with the chosen sections:

exporters:
  prometheus:
    endpoint: :8889
    send_timestamps: true
    # translation_strategy: UnderscoreEscapingWithoutSuffixes
    # If true, all the resource attributes will be converted to metric labels by default
    # resource_to_telemetry_conversion:
    #   enabled: true
service:
  pipelines:
    metrics:
      receivers:
        - postgrespro
      processors:
        - metricstransform/rename
        # - metricstransform/rename_by_regexp
        # - metricstransform/create_new
        # - metricstransform/add_label
        # - metricstransform/rename_label
        # - metricstransform/rename_label_multiple
        # - metricstransform/rename_label_value
        # - metricstransform/delete_by_label_value
        # - metricstransform/convert_data_type
        # - metricstransform/aggregate_labels
        # - metricstransform/aggregate_label_values
        # - metricstransform/combine_metrics
        # - metricstransform/group_metrics
        # - metricstransform/experimental_create_by_label
        # - metricstransform/experimental_scale_value
      exporters:
        - prometheus

Start pgpro-otel-collector with both the main configuration file and metrics_transform.yml:

build/pgpro-otel-collector/pgpro-otel-collector --config configs/basic.yml --config configs/metrics_transform.yml

Create a configuration file called biha.yml with the following content:

receivers:
  postgrespro/biha:
    transport: tcp
    endpoint: &endpoint localhost:5432
    database: biha_db
    username: biha_replication_user
    password: ${env:POSTGRESQL_PASSWORD}
    collection_interval: 60s
    initial_delay: 1s
    max_threads: 3
    plugins:
      biha:
        enabled: true
        cluster_name: cluster_name
exporters:
  prometheus/biha:
    endpoint: :8889
    send_timestamps: true
    # Defines how long metrics remain exposed without updates
    # Since some BiHA metrics include 'biha_state' in their labels,
    # it is best to set this parameter equal to the collection_interval,
    # so that outdated states do not appear on the Prometheus page.
    metric_expiration: 60s
processors:
  batch/metrics:
    send_batch_size: 8192
    timeout: 10s
  memory_limiter/metrics:
    check_interval: 1s
    limit_mib: 2048
service:
  pipelines:
    metrics/biha:
      receivers:
        - postgrespro/biha
      processors:
        - memory_limiter/metrics
        - batch/metrics
      exporters:
        - prometheus/biha

Start pgpro-otel-collector with the created configuration file:

build/pgpro-otel-collector/pgpro-otel-collector --config configs/biha.yml