Request a Demo
3.2. Working with Metrics
Prev UpChapter 3. Configuration and SetupHome Next

3.2. Working with Metrics #

3.2.1. General Setup
3.2.1.1. Configuring the postgrespro Receiver
3.2.1.2. Configuring the host_metrics Receiver
3.2.1.3. Configuring the OTLP Receiver
3.2.1.4. Configuring the SQL Query Receiver
3.2.1.5. Configuring the metrics_transform Processor
3.2.1.6. Configuring the prometheus Exporter
3.2.1.7. Configuring the otlp_http Exporter
3.2.1.8. Configuring the kafka Exporter
3.2.1.9. Configuring the Prometheus Remote Write Exporter
3.2.1.10. Setting up a Pipeline
3.2.1.11. Disabling and Enabling Metrics
3.2.2. Use Cases
3.2.2.1. Collecting Metrics from a BiHA Cluster
3.2.2.2. Collecting Metrics from a Postgres Pro Shardman Cluster

This section describes the steps required to manage metrics.

Important

While TLS (Transport Layer Security) is always enabled by default, it is recommended to configure mutual TLS (mTLS). For more details, refer to Section 3.6.4.

3.2.1. General Setup #

3.2.1.1. Configuring the postgrespro Receiver #

To collect metrics from the database instance, add the postgrespro receiver to the receivers section and specify its configuration.

Required configuration:

  • database instance connection parameters

  • the list of plugins for data collection

Additional configuration:

  • TLS parameters (refer to Section 3.6.4 for details)

  • collection parameters: parallelism, delay, interval 

receivers:
  postgrespro:
    max_threads: 3
    collection_interval: 60s
    initial_delay: 1s
    transport: tcp
    endpoint: localhost:5432
    # tls:
    #   insecure: false
    #   insecure_skip_verify: false
    #   ca_file: /etc/pgpro-otel-collector/cert.d/authorities.crt
    #   cert_file: /etc/pgpro-otel-collector/cert.d/postgresql.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/postgresql.key
    #   min_version: "1.3"
    database: postgres
    username: postgres
    password: ${env:POSTGRESQL_PASSWORD}
    metrics: null
    plugins:
      activity:
        enabled: true
      bgwriter:
        enabled: true
      locks:
        enabled: true
      version:
        enabled: true
      wal:
        enabled: true
      cache:
        enabled: true

Some plugins have additional configuration parameters. For example, the plugins for metrics collection from DBMS objects (tablespaces, databases, tables, indexes) can be configured in such a way that the collection will only take place in a specified number of objects. This allows controlling the load on the database instance and the amount of data sent through the pipeline to an exporter. The detailed description of configuration parameters for each plugin can be found in the /usr/share/doc/pgpro-otel-collector/examples directory.

The receiver can also use Unix sockets when the endpoint is defined as shown below. 

receivers:
  postgrespro:
    ...
    transport: unix
    endpoint: /tmp:5432 # Or 'tmp:5432'
    ...

For the full list of postgrespro configuration parameters, refer to the basic.yml file.

3.2.1.2. Configuring the host_metrics Receiver #

Important

hostmetrics has been renamed to host_metrics to comply with the component naming convention. The deprecated alias is still supported but produces warnings and will be completely removed in future releases. Update your configuration accordingly.

The host_metrics receiver is an open-source component of the OpenTelemetry Collector and is used for collecting metrics from the operating system. For detailed information about this receiver, refer to the OpenTelemetry documentation.

To configure the host_metrics receiver, it is sufficient to list the plugins (scrapers) for data collection. Collection parameters are also available: delay and interval.

Some plugins also have additional configuration parameters. 

receivers:
  host_metrics:
    collection_interval: 60s
    initial_delay: 1s
    scrapers:
      cpu:
        metrics:
          system.cpu.utilization:
            enabled: true
      disk: null
      load: null
      memory: null
      network: null

3.2.1.3. Configuring the OTLP Receiver #

To collect data in the OpenTelemetry Protocol (OTLP) format over gRPC or HTTP, pgpro-otel-collector uses the otlp receiver — an open-source component of the OpenTelemetry Collector.

To set up the otlp receiver for metric collection, follow the example procedure below.

  1. Create the otlp_receiver.yml configuration file.

  2. In the receivers.otlp.protocols section of the created configuration file, specify the protocol and its connection parameters.

    For HTTP: 

    receivers:
  otlp:
    protocols:
      http:
        endpoint: 0.0.0.0:4318
        max_request_body_size: 20971520
        include_metadata: false
        read_timeout: 0
        read_header_timeout: 1m
        idle_timeout: 1m
        keep_alives_enabled: true
        compression_algorithms: ["", "gzip"]

    Where:

    • endpoint defines the host:port combination on which the receiver listens. Default: localhost:4318.

    • max_request_body_size controls the maximum allowed size of incoming payloads in bytes. Default: 20971520 (20MiB).

    • include_metadata enables the client metadata propagation from the incoming requests to the downstream consumers. Default: false (disabled).

    • read_timeout defines the maximum amount of time allowed for reading the entire request, including the body. A zero or negative value means there will be no timeout. Default: 0.

    • read_header_timeout defines the maximum amount of time allowed for reading request headers. If zero, the read_timeout value is used. If both are zero, there is no timeout. Default: 1m.

    • idle_timeout defines the maximum amount of time allowed for waiting for the next request with keepalives enabled (keep_alives_enabled: true). Default: 1m.

    • keep_alives_enabled enables HTTP keepalives. Default: true (enabled).

    • compression_algorithms defines the list of compression algorithms the server can accept. Default: ["", "gzip", "zstd", "zlib", "snappy", "deflate", "lz4"].

    For gRPC: 

    receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
        max_recv_msg_size_mib: 16
        max_concurrent_streams: 0

    Where:

    • endpoint defines the host:port combination on which the receiver listens. Default: localhost:4317.

    • max_recv_msg_size_mib controls the maximum allowed size of incoming payloads in MiB. Default: 4.

    • max_concurrent_streams defines the limit for concurrent gRPC streams. If zero (default), there will be no limit.

  3. For HTTP, configure CORS (Cross-Origin Resource Sharing) — a browser security mechanism that allows a server to specify which external origins a browser is allowed to request resources from: 

          cors:
        allowed_origins:
          - http://test.com
          - https://*.example.com
        allowed_headers:
          - Example-Header
        max_age: 7200

    Where:

    • allowed_origins specifies the list of origins allowed to send requests to the receiver. An origin may contain a wildcard (*).

      Warning

      Do not use a plain wildcard ["*"] — it will be rejected because Access-Control-Allow-Credentials is enabled.

    • allowed_headers allows CORS requests to include headers outside the default allowlist.

    • max_age sets the value of the Access-Control-Max-Age header — a period of time, in seconds, during which the results of CORS preflight requests can be cached. Default: 5.

  4. Configure TLS: 

            tls:
          cert_file: /tmp/certs/cert.pem
          key_file: /tmp/certs/cert-key.pem
          client_ca_file: /tmp/certs/client-ca.pem
          min_version: "1.3"
          max_version: "1.3"

    For details on TLS configuration parameters, refer to Section 3.6.4.

  5. Enable basic authentication: 

            auth:
          authenticator: basicauth/otlp

  6. For gRPC, configure keepalive parameters to control connection lifetime, ensuring timely cleanup of dead connections: 

            keepalive:
          enforcement_policy:
            min_time: 5m
            permit_without_stream: false
          server_parameters:
            max_connection_age: 256s
            max_connection_age_grace: 60s
            max_connection_idle: 256s
            time: 5m
            timeout: 20s

    Where:

    • min_time sets the minimum time the client must wait between keepalive messages. If the client sends messages more frequently than the set value, the server may close the connection. Default: 5m.

    • permit_without_stream allows keepalive messages with no active requests. If this parameter is disabled, and the client sends a message without any requests being processed, the server will close the connection. Default: false (disabled).

    • max_connection_age defines the maximum lifetime of a connection. Once this limit is reached, the server sends GOAWAY and closes the connection. There is no limit by default.

    • max_connection_age_grace adds an extra grace period after max_connection_age before forcibly closing the connection. There is no limit by default.

    • max_connection_idle defines how long to keep an idle connection before closing it. There is no limit by default.

    • time defines how long to wait for activity before sending a keepalive message to the client. If set below 1s, a minimum of 1s is used. Default: 2h.

    • timeout defines how long to wait for a response to a keepalive message before closing the connection. Default: 20s.

  7. Configure the basic authentication extension: 

    extensions:
  basicauth/otlp:
    htpasswd:
      inline: |
        ${env:BASIC_AUTH_USERNAME}:${env:BASIC_AUTH_PASSWORD}

    For more details on basic authentication, refer to Section 3.6.6.

  8. Configure the exporters, processors, and the service pipeline: 

    exporters:
  otlp_http:
    compression: gzip
    endpoint: https://otlp.example.org:4318
    # tls:
    #   insecure: false
    #   ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt
    #   cert_file: /etc/pgpro-otel-collector/cert.d/client.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/client.key
    #   min_version: "1.3"
processors:
  batch:
    send_batch_size: 8192
    timeout: 10s
service:
  extensions: [ basicauth/otlp ]
  pipelines:
    metrics:
      receivers:  [ otlp ]
      processors: [ batch ]
      exporters:  [ otlp_http ]

  9. Start pgpro-otel-collector with the created configuration file along with the main configuration file: 

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

To support both HTTP and gRPC protocols simultaneously, configure each separately, as shown in the full example below. To disable a protocol, simply omit it from the protocols list. 

receivers:
  otlp:
    protocols:
      http:
        endpoint: 0.0.0.0:4318
        max_request_body_size: 20971520
        include_metadata: false
        read_timeout: 0
        read_header_timeout: 1m
        idle_timeout: 1m
        keep_alives_enabled: true
        compression_algorithms: ["", "gzip"]
        cors:
          allowed_origins:
            - http://test.com
            - https://*.example.com
          allowed_headers:
            - Example-Header
          max_age: 7200
        tls:
          cert_file: /tmp/certs/cert.pem
          key_file: /tmp/certs/cert-key.pem
          client_ca_file: /tmp/certs/client-ca.pem
          min_version: "1.3"
          max_version: "1.3"
        auth:
          authenticator: basicauth/otlp
      grpc:
        endpoint: 0.0.0.0:4317
        max_recv_msg_size_mib: 16
        max_concurrent_streams: 0
        tls:
          cert_file: /tmp/certs/cert.pem
          key_file: /tmp/certs/cert-key.pem
          client_ca_file: /tmp/certs/client-ca.pem
        auth:
          authenticator: basicauth/otlp
        keepalive:
          enforcement_policy:
            min_time: 5m
            permit_without_stream: false
          server_parameters:
            max_connection_age: 256s
            max_connection_age_grace: 60s
            max_connection_idle: 256s
            time: 5m
            timeout: 20s
extensions:
  basicauth/otlp:
    htpasswd:
      inline: |
        ${env:BASIC_AUTH_USERNAME}:${env:BASIC_AUTH_PASSWORD}
exporters:
  otlp_http:
    compression: gzip
    endpoint: https://otlp.example.org:4318
    # tls:
    #   insecure: false
    #   ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt
    #   cert_file: /etc/pgpro-otel-collector/cert.d/client.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/client.key
    #   min_version: "1.3"
processors:
  batch:
    send_batch_size: 8192
    timeout: 10s
service:
  extensions: [ basicauth/otlp ]
  pipelines:
    metrics:
      receivers:  [ otlp ]
      processors: [ batch ]
      exporters:  [ otlp_http ]

For the full list of the otlp receiver configuration parameters, refer to the OpenTelemetry documentation.

3.2.1.4. Configuring the SQL Query Receiver #

The sqlquery receiver is an open-source component of the OpenTelemetry Collector for gathering metrics and/or logs from custom SQL queries.

Warning

The sqlquery receiver is currently experimental and is not recommended for production use.

To set up the sqlquery receiver for collecting metrics, follow the example procedure below.

  1. Create the sqlquery.yml configuration file.

  2. 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
    # TLS is configured via sslmode and cert paths
    additional_params:
      application_name: pgpro-otel-collector
      # Whether to disable transport security
      # Possible values: disable, require, verify-ca, verify-full
      # Default: disable
      # sslmode: verify-full
      # Path to the CA cert to validate the server certificate
      # sslrootcert: /etc/pgpro-otel-collector/cert.d/ca.crt
      # Path to the TLS certificate for client authentication (mTLS)
      # sslcert: /etc/pgpro-otel-collector/cert.d/client.crt
      # Path to the TLS private key. Must be provided alongside sslcert
      # sslkey: /etc/pgpro-otel-collector/cert.d/client.key
      # Minimum SSL/TLS protocol version to allow
      # Possible values: TLSv1.0, TLSv1.1, TLSv1.2, TLSv1.3
      # Default: TLSv1.2
      # ssl_min_protocol_version: TLSv1.3
      # Maximum SSL/TLS protocol version to allow. If not set, the backend's maximum is used
      # ssl_max_protocol_version: TLSv1.3
    # 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: "postgresql://postgres:postgres@localhost:5432/postgres?application_name=pgpro-otel-collector&sslmode=verify-full&sslrootcert=/etc/pgpro-otel-collector/cert.d/ca.crt&sslcert=/etc/pgpro-otel-collector/cert.d/client.crt&sslkey=/etc/pgpro-otel-collector/cert.d/client.key"

  3. 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) ON CONFLICT DO NOTHING;
INSERT INTO products VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99) ON CONFLICT DO NOTHING;

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

    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.

    Note

    Avoid queries that produce NULL values. If a query returns NULL in a column referenced in the configuration, errors will be logged, but the receiver will continue operating.

  4. Configure exporters, processors, and the service pipeline: 

    ...
exporters:
  prometheus:
    endpoint: :8889
    send_timestamps: true
    # TLS configuration for the Prometheus HTTP endpoint.
    # When configured, the exporter will serve HTTPS instead of HTTP.
    # tls:
    #   cert_file: /etc/pgpro-otel-collector/cert.d/server.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/server.key
    #   client_ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt # mTLS
    #   min_version: "1.3"
  otlp_http/sqlquery/metrics:
    compression: ''
    endpoint: https://metrics.example.org:8080
    # tls:
    #   insecure: false
    #   ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt
    #   cert_file: /etc/pgpro-otel-collector/cert.d/client.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/client.key
    #   min_version: "1.3"
    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, otlp_http/sqlquery/metrics ]

    For more details on TLS configuration parameters, refer to Section 3.6.4.

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

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

For the full list of the sqlquery configuration parameters, refer to the OpenTelemetry documentation.

3.2.1.5. Configuring the metrics_transform Processor #

Important

metricstransform has been renamed to metrics_transform to comply with the component naming convention. The deprecated alias is still supported but produces warnings and will be completely removed in future releases. Update your configuration accordingly.

The metrics_transform processor is an open-source component of the OpenTelemetry Collector for renaming metrics and managing labels, including scaling and aggregation operations. For more details, refer to the OpenTelemetry documentation.

The example setup of the metrics_transform processor is shown in the procedure below.

  1. Create the metrics_transform.yml configuration file.

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

    • Rename a metric — for example, postgresql.databases.size_bytes to postgresql.db.size_bytes: 

      processors:
  metrics_transform/rename:
    transforms:
      - include: postgresql.databases.size_bytes
        # Default = strict
        match_type: strict
        action: update
        new_name: postgresql.db.size_bytes

      The action parameter controls the transformation type: update modifies existing metrics, insert creates cloned metrics, and combine merges multiple metrics into a new one.

    • Use regular expressions to rename multiple metrics at once: 

        metrics_transform/rename_by_regexp:
    transforms:
      - include: ^postgresql\.databases\.(.*)$$
        match_type: regexp
        action: update
        new_name: postgresql.db.$${1}

    • Create a duplicate of the metric with a new name: 

        metrics_transform/create_new:
    transforms:
      - include: postgresql.databases.size_bytes
        match_type: strict
        action: insert
        new_name: postgresql.db.size_bytes

    • Add a new label to a metric: 

        metrics_transform/add_label:
      transforms:
        - include: postgresql.databases.size_bytes
          action: update
          operations:
            - action: add_label
              new_label: new_label
              new_value: "value 1"

    • Rename an existing label: 

        metrics_transform/rename_label:
    transforms:
      - include: postgresql.databases.size_bytes
        action: update
        operations:
          - action: update_label
            label: database
            new_label: db

    • Rename multiple labels using a regular expression: 

        metrics_transform/rename_label_multiple:
    transforms:
      - include: ^postgresql\.databases\.(.*)$$
        match_type: regexp
        action: update
        operations:
          - action: update_label
            label: database
            new_label: db

    • Rename a specific label value: 

        metrics_transform/rename_label_value:
    transforms:
      - include: postgresql.databases.size_bytes
        action: update
        operations:
          - action: update_label
            label: database
            value_actions:
              - value: postgres
                new_value: default

    • Delete data points that have a certain label value: 

        metrics_transform/delete_by_label_value:
    transforms:
      - include: postgresql.databases.size_bytes
        action: update
        operations:
          - action: delete_label_value
            label: database
            # Specifies the label value whose data points will be removed
            label_value: db11

    • Convert data type from double to int and vice versa: 

        metrics_transform/convert_data_type:
    transforms:
      - include: postgresql.databases.size_bytes
        action: update
        operations:
          - action: toggle_scalar_data_type

    • Aggregate data points that have the labels excluded in label_set: 

        metrics_transform/aggregate_labels:
    transforms:
      - include: postgresql.activity.connections
        action: update
        operations:
          - action: aggregate_labels
            # Contains a list of labels that will remain after aggregation
            label_set:
              #- database
              - user
              #- backend_process_state
            # Defines how combined data points will be aggregated
            # Possible values: sum, mean, min, max, count, median
            aggregation_type: sum

      Note that only the sum aggregation function is supported for histogram and exponential histogram data types.

    • Aggregate data points that have a specific label value: 

        metrics_transform/aggregate_label_values:
    transforms:
      - include: postgresql.activity.connections
        action: update
        operations:
          - action: aggregate_label_values
            label: database
            # Contains a list of label values that will be aggregated
            aggregated_values: [ db11, db12, db13 ]
            new_value: all_db1
            aggregation_type: sum

      Note that only the sum aggregation function is supported for histogram and exponential histogram data types.

    • Combine several related metrics into one. For example, combine multiple tuples_* metrics into tuples_total with a type label: 

        metrics_transform/combine_metrics:
    transforms:
      - include: ^postgresql\.databases\.tuples_(?P<type>.*)$$
        match_type: regexp
        action: combine
        new_name: postgresql.databases.tuples_total
        aggregation_type: sum
        submatch_case: lower

      aggregation_type defines how combined data points are aggregated. This parameter is required when action is set to combine. Possible values: sum, mean, min, max, count, and median.

      The submatch_case parameter controls the case of label values extracted from regular expression submatches during combine operations. Leave empty to preserve the original case. Possible values: lower or upper.

    • Group metrics from a single resource and report them as multiple resource metrics: 

        metrics_transform/group_metrics:
    transforms:
      - include: ^postgresql.databases.size_bytes$$
        match_type: regexp
        action: group
        group_resource_labels: {"resource.type": "default", "source": "default"}
      - include: ^postgresql.databases.orphaned_files_size_bytes$$
        match_type: regexp
        action: group
        group_resource_labels: {"resource.type": "orphaned", "source": "orphaned"}

    • Create a new metric filtered by a label value (experimental feature): 

        metrics_transform/experimental_create_by_label:
    transforms:
      - include: postgresql.databases.size_bytes
        match_type: regexp
        experimental_match_labels: {"database": "db1.*"}
        action: insert
        new_name: postgresql.db1.size

      If experimental_match_labels is specified, transformations apply only to metrics that match the specified label values. This works with both strict and regexp match_type.

    • Scale metric values from bytes to bits (experimental feature): 

        metrics_transform/experimental_scale_value:
    transforms:
      - include: postgresql.databases.size_bytes
        match_type: strict
        action: insert
        new_name: postgresql.databases.size_bits
        operations:
          - action: experimental_scale_value
            experimental_scale: 8

      The experimental_scale parameter defines the scalar to apply to metric values. Note that scaling exponential histograms inherently involves some loss of accuracy.

  3. 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
    # TLS configuration for the Prometheus HTTP endpoint.
    # When configured, the exporter will serve HTTPS instead of HTTP.
    # tls:
    #   cert_file: /etc/pgpro-otel-collector/cert.d/server.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/server.key
    #   client_ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt # mTLS
    #   min_version: "1.3"
service:
  pipelines:
    metrics:
      receivers:
        - postgrespro
      processors:
        - metrics_transform/rename
        # - metrics_transform/rename_by_regexp
        # - metrics_transform/create_new
        # - metrics_transform/add_label
        # - metrics_transform/rename_label
        # - metrics_transform/rename_label_multiple
        # - metrics_transform/rename_label_value
        # - metrics_transform/delete_by_label_value
        # - metrics_transform/convert_data_type
        # - metrics_transform/aggregate_labels
        # - metrics_transform/aggregate_label_values
        # - metrics_transform/combine_metrics
        # - metrics_transform/group_metrics
        # - metrics_transform/experimental_create_by_label
        # - metrics_transform/experimental_scale_value
      exporters:
        - prometheus

    For more details on TLS configuration parameters, refer to Section 3.6.4.

  4. 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

For a complete list of configuration parameters, refer to the OpenTelemetry documentation.

3.2.1.6. Configuring the prometheus Exporter #

The prometheus exporter is an open-source component of the OpenTelemetry Collector. For detailed information, refer to the OpenTelemetry documentation.

prometheus is the easiest to use — it does not require the external component configuration and can be enabled by default. To set it up, it is sufficient to specify the address to listen for incoming requests: 

exporters:
  prometheus:
    endpoint: "1.2.3.4:8889"
    send_timestamps: true

3.2.1.7. Configuring the otlp_http Exporter #

Important

otlphttp has been renamed to otlp_http to comply with the component naming convention. The deprecated alias is still supported but produces warnings and will be completely removed in future releases. Update your configuration accordingly.

The otlp_http exporter is an open-source component of the OpenTelemetry Collector and is used for exporting collected logs to an OTLP-compatible storage or monitoring system that has to be predeployed and accessible. For more details, refer to the OpenTelemetry documentation.

To configure the otlp_http exporter, it is sufficient to specify the address of the target system where data should be sent: 

exporters:
  otlp_http:
    endpoint: https://otlp.example.org

3.2.1.8. Configuring the kafka Exporter #

The kafka exporter is an open-source component of the OpenTelemetry Collector for sending metrics and logs to Apache Kafka. For more details, refer to the OpenTelemetry documentation.

Below is the example of setting it up for sending metrics. 

receivers:
  postgrespro:
    max_threads: 3
    collection_interval: 60s
    initial_delay: 1s
    transport: tcp
    endpoint: localhost:5432
    # tls:
    #   insecure: false
    #   insecure_skip_verify: false
    #   ca_file: /etc/pgpro-otel-collector/cert.d/authorities.crt
    #   cert_file: /etc/pgpro-otel-collector/cert.d/postgresql.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/postgresql.key
    #   min_version: "1.3"
    database: postgres
    username: postgres
    password: ${env:POSTGRESQL_PASSWORD}
    metrics: null
    plugins:
      activity:
        enabled: true
      bgwriter:
        enabled: true
      locks:
        enabled: true
      version:
        enabled: true
      wal:
        enabled: true
      cache:
        enabled: true
exporters:
  kafka:
    brokers:
      - localhost:9092
    protocol_version: 2.1.0
    client_id: pgpro-otel-collector
    metrics:
      topic: otlp_metrics
      encoding: otlp_json # proto supported
    include_metadata_keys:
      - service.name
      - service.instance.id
    # tls:
    #   insecure: false
    #   # Required for self-signed certificates (system root CAs are used if empty)
    #   ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt
    #   # cert_file/key_file: required for mTLS
    #   cert_file: /etc/pgpro-otel-collector/cert.d/client.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/client.key
    #   min_version: "1.3"
    timeout: 30s
    producer:
      max_message_bytes: 1000000
      required_acks: 1
      compression: none # gzip, snappy, lz4, and zstd;
processors:
  batch/kafka:
    send_batch_size: 1024
    timeout: 1s
  resource:
    attributes:
      - key: service.name
        action: upsert
        value: postgresql
      - key: service.instance.id
        action: upsert
        value: address-of-postgres-instance:5432
service:
  pipelines:
    metrics/kafka:
      receivers: [ postgrespro ]
      processors: [ batch/kafka,resource ]
      exporters: [ kafka ]

For more details on TLS configuration parameters, refer to Section 3.6.4.

3.2.1.9. Configuring the Prometheus Remote Write Exporter #

To send metrics to Prometheus-compatible systems (Cortex, Mimir, Thanos, VictoriaMetrics, etc.) via the remote write protocol, pgpro-otel-collector uses the prometheus_remote_write exporter — an open-source component of the OpenTelemetry Collector.

Warning

The prometheus_remote_write exporter does not support non-cumulative monotonic, histogram, and summary OTLP metrics.

The example procedure below shows how to set up the prometheus_remote_write exporter to send metrics to VictoriaMetrics.

This example assumes that the receivers and processors are already configured.

  1. Create the prometheus_remote_write.yml configuration file.

  2. In the exporters.prometheus_remote_write section of the created configuration file, specify the connection parameters: 

    exporters:
  prometheus_remote_write:
    endpoint: http://victoria-metrics:8428/api/v1/write
    namespace: ""
    external_labels:
      instance: address-of-postgres-instance:5432
    headers:
      Authorization: "Bearer ${env:PROMETHEUS_RW_TOKEN}"
    translation_strategy: UnderscoreEscapingWithSuffixes
    send_metadata: true

    Where:

    • endpoint specifies the URL to receive remote write requests.

    • namespace specifies a prefix for all exported metric names.

    • external_labels specifies the labels to attach to every metric. The instance label uniquely identifies the target Postgres Pro instance.

    • headers specifies the additional headers attached to each HTTP request.

      Note

      The following headers cannot be changed: Content-Encoding, Content-Type, X-Prometheus-Remote-Write-Version, and User-Agent.

    • translation_strategy controls how OTLP metric and attribute names are converted to Prometheus metric and label names. Possible values: UnderscoreEscapingWithSuffixes (default), UnderscoreEscapingWithoutSuffixes, NoUTF8EscapingWithSuffixes, and NoTranslation. This parameter takes precedence over add_metric_suffixes.

    • send_metadata enables Prometheus metadata generation and export. Default: false (disabled).

  3. Configure TLS: 

        tls:
      insecure: false
      insecure_skip_verify: false
      ca_file: /etc/pgpro-otel-collector/cert.d/ca.crt
      cert_file: /etc/pgpro-otel-collector/cert.d/client.crt
      key_file: /etc/pgpro-otel-collector/cert.d/client.key

    For details on TLS configuration parameters, refer to Section 3.6.4.

  4. Configure data processing and queuing parameters: 

        resource_to_telemetry_conversion:
      enabled: true
      exclude_service_attributes: false
    target_info:
      enabled: true
    disable_scope_info: false
    remote_write_queue:
      enabled: true
      queue_size: 10000
      num_consumers: 5

    Where:

    • resource_to_telemetry_conversion.enabled enables conversion of resource attributes to metric labels. Default: false (disabled).

    • resource_to_telemetry_conversion.exclude_service_attributes excludes service.name, service.instance.id, and service.namespace resource attributes from the final metrics, as they are already converted to job and instance labels. Default: false (disabled).

    • target_info.enabled enables target_info metric generation to collect target metadata. Default: true (enabled).

    • disable_scope_info excludes the scope info labels (otel_scope_* attributes) from export. Default: false (disabled).

    • remote_write_queue.enabled enables the sending queue. Default: true (enabled).

    • remote_write_queue.queue_size defines the number of OTLP metrics that can be queued. Default: 10000.

    • remote_write_queue.num_consumers defines the minimum number of workers to use for concurrent outgoing requests. Default: 5.

  5. Configure WAL (Write-Ahead-Log) parameters: 

        wal:
      directory: ./prom_rw
      buffer_size: 300
      truncate_frequency: 1m
      lag_record_frequency: 15s

    Where:

    • directory specifies the directory to store the WAL. Default: "".

    • buffer_size specifies the number of elements to be read from the WAL before truncating. Default: 300.

    • truncate_frequency defines how often the WAL is truncated. Default: 1m.

    • lag_record_frequency defines how often the exporter records the WAL lagging. Default: 15s.

  6. Configure batching: 

        max_batch_size_bytes: 3000000
    max_batch_request_parallelism: 5

    Where:

    • max_batch_size_bytes specifies the maximum batch size in bytes. Batches exceeding this value are split. This parameter is ignored if the WAL is in use. Default: 3000000.

    • max_batch_request_parallelism specifies the maximum number of parallel threads for outgoing requests. If the endpoint does not support out-of-order samples, this parameter value should be 1. Default: 5.

  7. Configure retry and timeout parameters: 

        timeout: 30s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_interval: 30s
      max_elapsed_time: 300s
      multiplier: 1.5

    Where:

    • timeout sets the time to wait for each send attempt. Default: 5s.

    • retry_on_failure.enabled enables retries in case of an export failure. Default: true (enabled).

    • retry_on_failure.initial_interval sets the initial retry delay after the first failure. Default: 5s.

    • retry_on_failure.max_interval sets the maximum delay between retries. Default: 30s.

    • retry_on_failure.max_elapsed_time sets the total time allowed for retrying a batch. Default: 300s.

    • retry_on_failure.multiplier sets the factor by which the retry interval increases. Default: 1.5.

  8. Configure the service pipeline: 

    service:
  pipelines:
    metrics/prometheusremotewrite:
      receivers:  [ postgrespro ]
      processors: [ batch/metrics, memory_limiter/metrics ]
      exporters:  [ prometheus_remote_write ]

  9. Start pgpro-otel-collector with the created configuration file along with the main configuration file: 

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

For the full list of configuration parameters, refer to the OpenTelemetry documentation.

3.2.1.10. Setting up a Pipeline #

Once receivers and exporters are added and configured, they need to be combined into a pipeline. The pipeline is configured in the service section. The pipeline contents depend altogether on the previously added components (there is no default configuration).

The example below shows how to set up a pipeline for metric management. The data is collected by the postgrespro and host_metrics receivers, processed by the batch processor and exported by the prometheus and otlp_http exporters.

Thus, all the components used in the pipeline should also be added in the configuration file and set up. 

service:
  extensions: []
  pipelines:
    metrics:
      receivers:
        - postgrespro
        - host_metrics
      processors:
        - batch
      exporters:
        - prometheus
        - otlp_http

3.2.1.11. Disabling and Enabling Metrics #

Sending unused metrics leads to excessive resource consumption, especially when dealing with a large number of metrics generated by, for example, the tables plugin. To avoid such an overhead, disable all metrics by default and then explicitly enable only the required ones. This is done using the default section: 

metrics
  default:
    enabled: false
  postgresql.up:
    enabled: true

Note

This configuration affects only Postgres Pro metrics and does not apply to system metrics. Metric names must be specified in OTLP format. For example, the postgresql_pgpro_statements_wal_records_total metric should be specified as postgresql.pgpro_statements.wal_records.

3.2.2. Use Cases #

3.2.2.1. Collecting Metrics from a BiHA Cluster #

pgpro-otel-collector can be configured to collect metrics from BiHA clusters using the biha plugin. The example procedure for such setup is as follows:

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

    receivers:
  postgrespro/biha:
    transport: tcp
    endpoint: &endpoint localhost:5432
    # tls:
    #   insecure: false
    #   insecure_skip_verify: false
    #   ca_file: /etc/pgpro-otel-collector/cert.d/authorities.crt
    #   cert_file: /etc/pgpro-otel-collector/cert.d/postgresql.crt
    #   key_file: /etc/pgpro-otel-collector/cert.d/postgresql.key
    #   min_version: "1.3"
    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

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

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

To check which metrics are included in the biha plugin, refer to Section 4.19.

Note

Plugins with special access privileges must be configured separately.

If you need to include plugins that require special access privileges, add all of them in a separate postgrespro section with their own usernames. For example: 

receivers:
   postgrespro/biha:
     username: biha_replication_user
     ...
     plugins:
       biha:
         enabled: true
         ...
   postgrespro/otel:
     username: otel
     ...
     plugins:
       databases:
         enabled: true

For more details on the access privileges, refer to Section 1.3.1.

Note

By default, the postgres database is not copied to a node in the referee or referee_with_wal mode (unless the --referee-with-postgres-db option is used).

When collecting database-level, table-level, index-level, or function-level statistics from these nodes, you must explicitly exclude the postgres database using the acl.databases.deny section, as shown below. This exclusion is not required when collecting per-instance statistics. For more information about allowlists and denylists, refer to Section 3.6.5. 

acl:
  databases:
    deny:
      - name: postgres

3.2.2.2. Collecting Metrics from a Postgres Pro Shardman Cluster #

Postgres Pro Shardman is a distributed object-relational DBMS based on Postgres Pro Enterprise that offers fast scalability and high availability for large workloads.

Postgres Pro Shardman provides two kinds of monitoring views: local views that hold per-node data and must be queried on every node, and global views that broadcast a query to the whole cluster and return one row per replication group (rgid label). You can collect both kinds of data with pgpro-otel-collector, as described in the example procedure below.

Important

This configuration is only available starting with Postgres Pro and Postgres Pro Shardman 18.

  1. Create a configuration file shardman.yml.

  2. In the receivers section of the created file, add and configure a postgrespro receiver for each node to collect per-node statistics from the shardman.pg_stat_fast_path local view using the shardman_fast_path plugin. 

    receivers:
  postgrespro/sdm01:
    transport: tcp
    endpoint: localhost:15432
    username: otelcol
    password: ${env:POSTGRESQL_PASSWORD}
    collection_interval: 60s
    initial_delay: 1s
    shardman_cluster_name: cluster0
    plugins:
      shardman_fast_path:
        enabled: true
  postgrespro/sdm02:
    transport: tcp
    endpoint: localhost:15433
    username: otelcol
    password: ${env:POSTGRESQL_PASSWORD}
    collection_interval: 60s
    initial_delay: 1s
    shardman_cluster_name: cluster0
    plugins:
      shardman_fast_path:
        enabled: true
  postgrespro/sdm03:
    transport: tcp
    endpoint: localhost:15434
    username: otelcol
    password: ${env:POSTGRESQL_PASSWORD}
    collection_interval: 60s
    initial_delay: 1s
    shardman_cluster_name: cluster0
    plugins:
      shardman_fast_path:
        enabled: true

    Where:

    • endpoint specifies the node host and port.

    • username must contain the dedicated otelcol monitoring role.

      Important

      The SELECT privilege on the shardman.pg_stat_fast_path view must be explicitly granted to the otelcol role to read shardman.* objects.

    • shardman_cluster_name is an optional parameter that sets the cluster label on all Postgres Pro Shardman plugin metrics of this receiver. The value must be identical on every receiver of the same cluster.

      If omitted, the label is emitted as an empty string (cluster=""). Prometheus-based systems (Prometheus, VictoriaMetrics) drop empty labels when collecting metrics, so the label will be absent in PromQL (Prometheus Query Language) expressions and dashboards. Other exporters (OTLP, Zabbix) receive the empty string as is.

  3. Configure a dedicated receiver to gather cluster-wide statistics from the shardman.gv_stat_monitor global view using the shardman_gv_monitor plugin.

    Warning

    Enable shardman_gv_monitor on exactly one receiver. Enabling it on multiple nodes duplicates cluster-wide data and multiplies the load. Do not copy this block to the per-node receivers. The same holds for copying local view plugins over to the global receiver. 

      postgrespro/sdm_global:
    transport: tcp
    endpoint: localhost:15432
    username: postgres
    password: ${env:POSTGRESQL_SU_PASSWORD}
    collection_interval: 60s
    initial_delay: 1s
    shardman_cluster_name: cluster0
    plugins:
      shardman_gv_monitor:
        enabled: true

    Where:

    • endpoint must point to any primary node of the cluster. In this example, it is sdm01. Specifying a standby will trigger a warning, and the metric collection will be skipped.

    • username must be a superuser. In Postgres Pro Shardman 18, the shardman.gv_* views are superuser-only: the internal shardman.execute_query() function rejects non-superusers regardless of granted privileges, so the otelcol monitoring role cannot read them.

    For more details on the postgrespro receiver configuration, refer to Section 3.2.1.1 and the basic.yml file.

  4. Configure the exporters: 

    exporters:
  prometheus/shardman:
    endpoint: :8889
    send_timestamps: true
    metric_expiration: 60s

  5. In the processors section, set the per-node instance label for each receiver using a dedicated attributes processor. Local Postgres Pro Shardman views have no node-identifying column, so without this label the receivers would publish colliding time series. Each processor must be placed in its own pipeline — one per node receiver — so that its value applies only to that node.

    For global views, rows already differ by rgid. The instance label is added only to keep the label set uniform with the per-node receivers; its value is the entry node — the node from which the global query is issued, as configured in postgrespro/sdm_global. 

    processors:
  batch/metrics:
    send_batch_size: 8192
    timeout: 10s
  memory_limiter/metrics:
    check_interval: 1s
    limit_mib: 2048
  attributes/sdm01:
    actions:
      - action: insert
        key: instance
        value: sdm01:5432
  attributes/sdm02:
    actions:
      - action: insert
        key: instance
        value: sdm02:5432
  attributes/sdm03:
    actions:
      - action: insert
        key: instance
        value: sdm03:5432
  attributes/sdm_global:
    actions:
      - action: insert
        key: instance
        value: sdm01:5432

  6. In the service section, define the pipelines — one per node receiver: 

    service:
  pipelines:
    metrics/sdm01:
      receivers:
        - postgrespro/sdm01
      processors:
        - memory_limiter/metrics
        - attributes/sdm01
        - batch/metrics
      exporters:
        - prometheus/shardman
    metrics/sdm02:
      receivers:
        - postgrespro/sdm02
      processors:
        - memory_limiter/metrics
        - attributes/sdm02
        - batch/metrics
      exporters:
        - prometheus/shardman
    metrics/sdm03:
      receivers:
        - postgrespro/sdm03
      processors:
        - memory_limiter/metrics
        - attributes/sdm03
        - batch/metrics
      exporters:
        - prometheus/shardman
    metrics/sdm_global:
      receivers:
        - postgrespro/sdm_global
      processors:
        - memory_limiter/metrics
        - attributes/sdm_global
        - batch/metrics
      exporters:
        - prometheus/shardman

  7. Start pgpro-otel-collector with the created configuration file, providing the required passwords as environment variables: 

    POSTGRESQL_PASSWORD=otelcol_password POSTGRESQL_SU_PASSWORD=superuser_password \
build/pgpro-otel-collector/pgpro-otel-collector --config configs/shardman.yml

    To avoid exposing credentials in the command line, passwords can be stored in an environment file and loaded through EnvironmentFile in the systemd service unit.

Prev Up Next
3.1. Configuration File Home 3.3. Working with Logs
epub pdf