Using EDB OTEL

The edb_otel extension allows metrics to be sent through the OpenTelemetry (OTEL) protocol.

edb_otel APIs

The following functions are available in edb_otel:

• edb_otel.report_metric
• edb_otel.command_from_pg_exporter_definition
• edb_otel.schedule_from_pg_exporter_definition

The following views are available in edb_otel:

• edb_otel.pg_exporter_base_definitions

edb_otel.report_metric

Use this function to define and group metrics:

edb_otel.report_metric(
	meter_name TEXT, 
	metric_name TEXT, 
	metric_type INT, 
	value TEXT, 
	labels TEXT
)

Example:

SELECT edb_otel.report_metric('m1', 'foo', 4, '10', '{"dbname": "foo", "schemaname": "bar", "relname": "baz"}');

edb_otel.command_from_pg_exporter_definition

Use this function to generate a command that can run the query and send the results through edb_otel. It accepts a JSONB value containing the same fields as pg_exporter definitions.

edb_otel.command_from_pg_exporter_definition(
    meter jsonb
)

edb_otel.schedule_from_pg_exporter_definition

Use this function to define and schedule custom queries. The query and its metadata, such as metric types and tags, must use a JSONB structure. It accepts a JSONB value containing the parameters that are usually consumed by pg_exporter. It also schedules a pg_cron job that runs the query periodically and sends the results to the OpenTelemetry endpoint.

edb_otel.schedule_from_pg_exporter_definition(
    meter jsonb, 
    schedule text
)

Example:

This custom query adds a count of client backends to the schedule:

SELECT edb_otel.schedule_from_pg_exporter_definition(
  meter := $${
      "name": "user_activity",
      "query": "SELECT count(*) AS user_count
                FROM pg_stat_activity
                WHERE backend_type = 'client backend'",
      "metrics": [{"user_count": {"usage": "GAUGE"}}]
  }$$::jsonb,
	schedule := '* * * * *');

edb_otel.pg_exporter_base_definitions

This view contains the meter (queries and their metrics) definitions that pg_exporter brings by default. You can refresh them from the upm-metrics-pg-exporter or the pg_exporter collector by running yq on the directory:

yq eval-all -o=json '. as $item ireduce ({}; . * $item)' [...]/config/collector/*.yml

Job scheduling

You can use pg_cron to schedule jobs for the pg_exporter. edb_otel has auxiliary functions to ease the scheduling of monitoring queries. To start using pg_exporter queries, you need the following:

• The meter name. This is the top key in the YAML files.
• A scheduling definition. This is a schedule that pg_cron will accept,

For example, the 410-pg_activity.yml query pulls output from pg_stat_activity. To run this every minute, use the cron syntax: '* * * * *'. To create the job in pg_cron:

SELECT edb_otel.schedule_from_pg_exporter_definition(meters->'pg_activity', '* * * * *')
FROM edb_otel.pg_exporter_base_definitions;

The view edb_otel.pg_exporter_base_definitions contains the metadata of the pg_activity task, which is filtered by the JSONB -> operator, and sent into the function edb_otel.schedule_from_pg_exporter_definition. The edb_otel.schedule_from_pg_exporter_definition function then uses the edb_otel.command_from_pg_exporter_definition function to convert the JSONB metadata into a text command, which gets scheduled in pg_cron.

The function returns the same jobid that pg_cron returns:

-[ RECORD 1 ]------------------------+---
schedule_from_pg_exporter_definition | 29

You can then view this in the pg_cron job table:

SELECT jobname, active, jobid, schedule FROM cron.job;

-[ RECORD 1 ]---------
jobname  | pg_activity
active   | t
jobid    | 29
schedule | * * * * *