4.6. Integration with External Data Sources
Prev UpChapter 4. Installation and SetupHome Next

4.6. Integration with External Data Sources #

4.6.1. Integration with Prometheus
4.6.2. Integration with Elasticsearch and APM

This section explains how to integrate PPEM with Prometheus, as well as with Elasticsearch and Application Performance Monitoring (APM).

Fictional service names are used in the configuration examples:

For more information about the monitoring architecture, refer to Section 1.2.4.

4.6.1. Integration with Prometheus #

Integration with external Prometheus data sources is used for reading metrics written by pgpro-otel-collector.

Note

You can use VictoriaMetrics instead of Prometheus, since these solutions use similar interfaces for reading and writing metrics.

The components below are required for integration.

pgpro-otel-collector #

The monitoring agent performing the following functions:

  • collecting statistics from Postgres Pro DBMS instances and converting them to metrics

  • publishing metrics for further collection by the Prometheus monitoring system

Prometheus #

The monitoring system performing the following functions:

  • collecting metrics from pgpro-otel-collector monitoring agents

  • storing metrics from monitoring agents according to the internal configuration parameters

  • providing the HTTP interface for receiving metrics

PPEM #

The Postgres Pro Enterprise Manager system performing the following functions:

  • accessing the Prometheus monitoring system for receiving DBMS instance metrics

  • providing the user with the monitoring interface in the form of graphs

The integration process includes the following steps:

Additional configuration of the agent is not required.

Configuring pgpro-otel-collector for Prometheus

  1. Enable and configure the postgrespro and hostmetrics receivers: 

    receivers:
  hostmetrics:
    initial_delay: 1s
    collection_interval: 60s
    scrapers:
      cpu:
        metrics:
          system.cpu.utilization:
            enabled: true
      disk: null
      filesystem: null
      load: null
      memory: null
      network: null
      paging: null
      processes: null
  postgrespro:
    max_threads: 3
    initial_delay: 1s
    collection_interval: 60s
    transport: tcp
    endpoint: localhost:5432
    database: postgres
    username: postgres
    password: ${env:POSTGRESQL_PASSWORD}
    plugins:
      activity:
        enabled: true
      archiver:
        enabled: true
      bgwriter:
        enabled: true
      cache:
        enabled: true
      databases:
        enabled: true
      io:
        enabled: true
      locks:
        enabled: true
      version:
        enabled: true
      wal:
        enabled: true

  2. Configure metrics publishing using prometheusexporter and the pipeline.

    PPEM expects the metrics sent by pgpro-otel-collector to have the instance label with the node FQDN and DBMS instance port number separated by a colon, such as postgresql_activity_connections{instance="postgresql-01.example.org:5432"}.

    Configuration example: 

    exporters:
  prometheus:
    const_labels:
      instance: postgresql-01.example.org:5432
    endpoint: :8889
    send_timestamps: true

service:
  extensions: []
  pipelines:
    metrics:
      exporters:
      - prometheus
      receivers:
      - postgrespro
      - hostmetrics

  3. Start the collector and ensure that metrics are published on its side: 

    # systemctl status pgpro-otel-collector

# systemctl status pgpro-otel-collector
● pgpro-otel-collector.service - PostgresPro OpenTelemetry Collector
    Loaded: loaded (/lib/systemd/system/pgpro-otel-collector.service; enabled; preset: enabled)
    Active: active (running) since Thu 2025-03-20 01:18:08 MSK; 4h 13min ago
  Main PID: 6991 (pgpro-otel-coll)
    Tasks: 8 (limit: 3512)
    Memory: 119.3M
      CPU: 2min 49.311s
    CGroup: /system.slice/pgpro-otel-collector.service
            └─6991 /usr/bin/pgpro-otel-collector --config /etc/pgpro-otel-collector/basic.yml

Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.366656,"msg":"Setting up own telemetry..."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.367178,"msg":"Skipped telemetry setup."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.3679142,"msg":"Development component. May change in the future.","kind":"receiver","name":"postgrespro","data_type":"metrics"}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"warn","ts":1742422688.3494158,"caller":"envprovider@v1.16.0/provider.go:59","msg":"Configuration references unset environment variable","name":"POSTGRESQL_P>
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.4481084,"msg":"Starting pgpro-otel-collector...","Version":"v0.3.1","NumCPU":1}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.4481149,"msg":"Starting extensions..."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"warn","ts":1742422688.4483361,"msg":"Using the 0.0.0.0 address exposes this server to every network interface, which may facilitate Denial of Service attack>
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.4515307,"msg":"Starting stanza receiver","kind":"receiver","name":"filelog","data_type":"logs"}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.451749,"msg":"Everything is ready. Begin running and processing data."}

curl -s 127.0.0.1:8889/metrics |grep -c postgres
4254

Configuring Prometheus

You can configure collection of metrics using pgpro-otel-collector on the side of Prometheus in different ways. One of the ways is collecting using the static configuration: 

- job_name: pgpro-otel-collector
  static_configs:
    targets:
    - postgresql-01.example.org:8889/metrics

For more information about other collection methods, refer to the official Prometheus documentation.

PPEM does not require additional Prometheus configuration.

Checking for Metrics in Prometheus

After configuring metrics collection using pgpro-otel-collector, ensure that metrics are received by the monitoring system.

For this check, you can use the built-in expression browser graphical tool or the promtool utility from Prometheus.

Example check using the promtool utility: 

promtool query instant https://prometheus.example.org 'postgresql_activity_connections{instance="postgresql-01.example.org:5432"}'

Where:

  • https://prometheus.example.org: The URL of the monitoring service.

  • postgresql_activity_connections{instance="postgresql-01.example.org:5432"}: The name of the metric.

Response example: 

postgresql_activity_connections{database="postgres", instance="postgresql-01.example.org:5432", job="pgpro-otel-collector", state="active", user="postgres"} 5
postgresql_activity_connections{database="postgres", instance="postgresql-01.example.org:5432", job="pgpro-otel-collector", state="idle", user="postgres"} 10

Configuring a Metrics Data Source

  1. In the navigation panel, go to Infrastructure → Data sources → Metrics storages.

  2. In the top-right corner of the page, click Create storage.

  3. Specify the metrics storage parameters (parameters marked with an asterisk are required):

    • Name: The unique name of the metrics storage. For example, Prometheus.

    • URL: The network address for connecting to the metrics storage. For example, https://prometheus.example.org/select/0/prometheus.

    • User: The unique name of the user if authorization is used.

    • Password: The password of the user if the authorization is enabled.

    • Description: The description of the metrics storage.

    • Make default datasource: Specifies whether the metrics storage is used by default for all metric queries.

  4. Click Save.

Checking the Operation of a Metrics Storage

  1. In the navigation panel, go to Monitoring → Metrics.

  2. In the top-right corner of the page, select the instance for which there are metrics in the storage.

  3. Change the default data source to the internal one and ensure that graphs are displayed without errors.

4.6.2. Integration with Elasticsearch and APM #

Integration with external Elasticsearch data sources is used for reading logs written by pgpro-otel-collector.

The components below are required for integration.

pgpro-otel-collector #

The monitoring agent performing the following functions:

  • collecting activity logs from Postgres Pro DBMS instances

  • sending activity logs to APM Elasticsearch

APM Elasticsearch #

The application performance monitoring system based on Elastic Stack performing the following functions:

  • receiving data from the monitoring agent and converts it to the ES document format

  • sending converted data to Elasticsearch

Elasticsearch #

The activity log storage system performing the following functions:

  • receiving activity logs from the application performance monitoring system

  • storing activity logs according to internal storage parameters

  • providing the interface for receiving activity logs

PPEM #

The Postgres Pro Enterprise Manager system performing the following functions:

  • accessing Elasticsearch for receiving DBMS instance activity logs

  • providing the user with the monitoring interface based on activity logs in the form of text data

The integration process includes the following steps:

Additional configuration of the agent is not required.

Configuring Elasticsearch

  1. Install the Elasticsearch APM server using the standard documentation.

  2. Integrate the Elasticsearch APM server with Elasticsearch using the standard documentation.

  3. Configure the pgpro-otel-collector ingest pipeline.

    This is required for compatibility between document (log) fields and the Elasticsearch Common Schema (ECS) field naming schema.

    Pipeline configuration example (both queries are executed sequentially in Kibana Developer Tools): 

    PUT _ingest/pipeline/postgrespro-otelcol-enrich-logs
{
  "description": "Enrich PostgresPro Otel collector logs",
  "processors": [
    {
      "rename": {
        "if": "ctx?.labels?.message != null",
        "field": "labels.message",
        "target_field": "message",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.pid != null",
        "field": "labels.pid",
        "target_field": "process.pid",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.error_severity != null",
        "field": "labels.error_severity",
        "target_field": "log.level",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.user != null",
        "field": "labels.user",
        "target_field": "user.name",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.session_start != null",
        "field": "labels.session_start",
        "target_field": "session.start_time",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.session_id != null",
        "field": "labels.session_id",
        "target_field": "session.id",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.numeric_labels?.tx_id != null",
        "field": "numeric_labels.tx_id",
        "target_field": "transaction.id",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.log_file_name != null",
        "field": "labels.log_file_name",
        "target_field": "log.file.path",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "rename": {
        "if": "ctx?.labels?.dbname != null",
        "field": "labels.dbname",
        "target_field": "db.name",
        "ignore_failure": true,
        "ignore_missing": false,
        "override": true
      }
    },
    {
      "gsub": {
        "if": "ctx?.service?.node?.name != null",
        "field": "service.node.name",
        "target_field": "host.name",
        "pattern": ":.+$",
        "replacement": "",
        "ignore_failure": true,
        "ignore_missing": false
      }
    },
    {
      "remove": {
        "field": [
          "observer.version",
          "observer.hostname",
          "service.language.name"
        ],
        "ignore_failure": true
      }
    },
    {
      "remove": {
        "field": "agent.version",
        "if": "ctx?.agent?.version == \"unknown\"",
        "ignore_failure": true
      }
    }
  ]
}

    PUT _ingest/pipeline/logs-apm.app@custom
{
  "processors": [
    {
      "pipeline": {
        "name": "postgrespro-otelcol-enrich-logs"
      }
    }
  ]
}

Configuring pgpro-otel-collector for Elasticsearch

  1. Enable and configure the filelog receiver.

    Receiver configuration example for the scenario when PostgreSQL logs are generated in the JSON format: 

    receivers:
  filelog:
    include:
    - /var/log/postgresql/*.json
    operators:
    - parse_ints: true
      timestamp:
        layout: '%Y-%m-%d %H:%M:%S.%L %Z'
        layout_type: strptime
        parse_from: attributes.timestamp
      type: json_parser
    - field: attributes.timestamp
      type: remove
    retry_on_failure:
      enabled: true
      initial_interval: 1s
      max_elapsed_time: 5m
      max_interval: 30s
    start_at: end

  2. Configure processors: 

    processors:
  attributes/convert:
    actions:
    - action: convert
      converted_type: string
      key: query_id
    - action: convert
      converted_type: string
      key: pid
  resource:
    attributes:
    - action: upsert
      key: service.name
      value: postgresql
    - action: upsert
      key: service.instance.id
      value: postgresql-01.example.org:5432

    Where:

    • service.name is the key for naming the data stream (data stream) and, consequently, indexes.

    • service.instance.id is the key for identifying the instance.

    • For logs in the JSON format, converting query_id to a string is required because integers are displayed incorrectly in ES.

    Important

    Data streams are used for storing data. The target stream is selected automatically and has the logs-apm.app.service.name-namespace format.

    The service.name value is specified in the collector configuration, in the processors.resource.attributes list, by the key: service.name element.

    The namespace value is defined by the element with the service.environment key. It is not sent in this configuration so the default value is entered by default. If this configuration is used, activity logs will be stored in the stream named logs-apm.app.postgresql-default.

  3. Configure logs sending using otlphttpexporter and the pipeline: 

    exporters:
  otlphttp/elastic_logs:
    compression: gzip
    endpoint: https://elasticsearch-apm.example.org
    tls:
      insecure_skip_verify: false

service:
  extensions: []
  pipelines:
    logs:
      receivers:
      - filelog
      processors:
      - resource
      - attributes/convert
      exporters:
      - otlphttp/elastic_logs

  4. Start the collector and ensure that metrics are published on its side: 

    # systemctl status pgpro-otel-collector

# systemctl status pgpro-otel-collector
● pgpro-otel-collector.service - PostgresPro OpenTelemetry Collector
    Loaded: loaded (/lib/systemd/system/pgpro-otel-collector.service; enabled; preset: enabled)
    Active: active (running) since Thu 2025-03-20 01:18:08 MSK; 4h 13min ago
  Main PID: 6991 (pgpro-otel-coll)
    Tasks: 8 (limit: 3512)
    Memory: 119.3M
      CPU: 2min 49.311s
    CGroup: /system.slice/pgpro-otel-collector.service
            └─6991 /usr/bin/pgpro-otel-collector --config /etc/pgpro-otel-collector/basic.yml

Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.366656,"msg":"Setting up own telemetry..."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.367178,"msg":"Skipped telemetry setup."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.3679142,"msg":"Development component. May change in the future.","kind":"receiver","name":"postgrespro","data_type":"metrics"}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"warn","ts":1742422688.3494158,"caller":"envprovider@v1.16.0/provider.go:59","msg":"Configuration references unset environment variable","name":"POSTGRESQL_P>
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.4481084,"msg":"Starting pgpro-otel-collector...","Version":"v0.3.1","NumCPU":1}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.4481149,"msg":"Starting extensions..."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"warn","ts":1742422688.4483361,"msg":"Using the 0.0.0.0 address exposes this server to every network interface, which may facilitate Denial of Service attack>
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.4515307,"msg":"Starting stanza receiver","kind":"receiver","name":"filelog","data_type":"logs"}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.451749,"msg":"Everything is ready. Begin running and processing data."}
Mar 20 01:18:08 postgresql-01.example.org pgpro-otel-collector[6991]: {"level":"info","ts":1742422688.6523068,"msg":"Started watching file","kind":"receiver","name":"filelog","data_type":"logs","component":"fileconsumer","path":"/var/log/postgresql/postgresql-2025-03-20.json"}

Checking Logs in Elasticsearch

After configuring logs sending from pgpro-otel-collector in Elasticsearch, ensure that metrics are received by the log storage system.

For this check, you can execute a query to the storage using the curl utility.

Query example: 

curl -s -XGET "https://elasticsearch.example.org:9200/logs-apm.app.postgresql-default/_search?size=10" -H 'Content-Type: application/json' -d'
{
  "_source": ["message","service.node.name","@timestamp"],
  "sort": [
    { "@timestamp": "desc" }
  ],
  "query": {
        "bool": {
            "filter": [
                { "term":{"service.node.name":"postgresql-01.example.org:5432" }}]
        }
    }
}'

Where:

  • https://elasticsearch.example.org:9200 is the URL of the log storage system.

  • logs-apm.app.postgresql-default is the name of the data stream for the search.

  • size=10 is the size of the sample.

  • "_source": ["message","service.node.name","@timestamp"] are requested fields.

Response example: 

{
  "took": 18,
  "timed_out": false,
  "_shards": {
    "total": 11,
    "successful": 11,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 10000,
      "relation": "gte"
    },
    "max_score": null,
    "hits": [
      {
        "_index": ".ds-logs-apm.app.postgresql-default-2025.03.19-000379",
        "_id": "qmuArJUB2PKtie47RffA",
        "_score": null,
        "_source": {
          "message": "checkpoint complete: wrote 2038 buffers (16.6%); 0 WAL file(s) added, 0 removed, 10 recycled; write=269.563 s, sync=1.192 s, total=270.962 s; sync files=246, longest=0.677 s, average=0.005 s; distance=162419 kB, estimate=174180 kB; lsn=6/62000850, redo lsn=6/583C4DD8",
          "@timestamp": "2025-03-19T03:44:01.336Z",
          "service": {
            "node": {
              "name": "postgresql-01.example.org:5432"
            }
          }
        },
        "sort": [
          1742355841336
        ]
      }
    ]
  }
}

Configuring a Log Data Source

  1. In the navigation panel, go to Infrastructure → Data sources → Message storages.

  2. In the top-right corner of the page, click Create storage.

  3. Specify the log storage parameters (parameters marked with an asterisk are required):

    • Name: The unique name of the log storage. For example, Elasticsearch.

    • URL: The network address for connecting to the log storage. For example, https://elasticsearch.example.org.

    • Elasticsearch index: The name of the index (stream) for search queries.

      Specify logs-apm.app.postgresql-default.

    • User: The unique name of the user if authorization is used.

    • Password: The password of the user if the authorization is enabled.

    • Description: The description of the log storage.

    • Make default datasource: Specifies whether the log storage is used by default for all metric queries.

Checking the Operation of the Log Storage

In the navigation panel, go to Monitoring → Message journal.

Prev Up Next
4.5. Installation and Configuration of Logging and Monitoring Tools Home 4.7. Integration with OpenLDAP and Active Directory