pgpro-otel-collector

pgpro-otel-collector (Postgres Pro OpenTelemetry Collector) is an independent utility designed to collect and process statistical data from database instances, such as metrics and activity logs, and deliver it to monitoring systems compatible with OpenTelemetry. It functions as an agent or middleware between the database and the monitoring system.

Overview

A single instance of Postgres Pro generates a significant amount of statistical data, which is stored in system views, tables, and catalogs. Another source of data is activity logs (error logs, slow query logs, etc.), stored as files. To utilize this data, it must be extracted and sent to monitoring systems equipped with advanced tools such as visualization charts, dashboards, alerting tools, reports, and anomaly detection.

After collecting the data, pgpro-otel-collector converts it into a format compatible with various monitoring systems. Depending on the data transfer model, the converted data is either published on the collector side (pull model), or sent to a particular monitoring system (push model). Once stored in the monitoring system, the data becomes available for further analysis and visualization.

pgpro-otel-collector can be used as an alternative to traditional PostgreSQL monitoring agents, such as Postgres Exporter, or Zabbix Agent.

Architecture

pgpro-otel-collector is based on the OpenTelemetry Collector and consists of several components that work together in a pipeline:

• Receivers: The primary components responsible for collecting data from databases. pgpro-otel-collector supports the following receivers:

  • postgrespro: The central component of the collector, which contains the main implementation (built-in plugins) for collecting data from a database instance.
  • filelog: An auxiliary receiver responsible for collecting information from database activity logs.
  • hostmetrics: An auxiliary receiver that collects information from the operating system; may require root access.

• Processors: Receive data from receivers and process it.

• Exporters: Receive processed data from processors and deliver it to external consumers. pgpro-otel-collector supports the following exporters:

  • otlphttp: Exports and sends information over the OTLP protocol (the standard OpenTelemetry protocol used by many monitoring system vendors) with the help of the HTTP protocol.
  • prometheus: Exports metrics over the built-in HTTP server and publishes them on the /metrics page in the format of Prometheus monitoring system. prometheus is used by default because it does not require additional configuration.

Plugins

Plugins are internal components of a receiver responsible for collecting various types of statistical data. Each plugin is designed to collect specific information, which is then converted into metric groups. pgpro-otel-collector provides metric groups that describe various subsystems, events, and processes within the database management system (DBMS), such as table and index usage, background processes, resource usage by queries, and more.

The amount of statistical data may vary depending on the Postgres Pro version and edition. This adds up to variability and complexity of collection methods. Another important role of pgpro-otel-collector is to abstract these differences from monitoring systems while providing comprehensive information about the database.

The postgrespro receiver includes the following plugins:

• activity: Retrieves general activity in the instance from the pg_stat_activity view.
• archiver: Retrieves Write-Ahead Logging (WAL) archiving activity from the pg_stat_archiver view.
• bgwriter: Retrieves background writer and checkpointer process activity from the pg_stat_bgwriter view.
• bloat_indexes: Retrieves information about index bloat.
• bloat_tables: Retrieves information about table bloat.
• buffercache: Monitors shared cache usage (requires the pg_buffercache extension).
• cfs: Monitors Columnar File Storage (CFS) compression usage (available in Postgres Pro Enterprise only).
• databases: Retrieves database usage statistics from the pg_stat_database view.
• functions: Retrieves user function usage statistics from the pg_stat_user_functions view.
• hostmetrics: Collects operating system metrics.
• indexes: Retrieves index usage statistics from the pg_stat_user_indexes and pg_statio_user_indexes views.
• io: Collects Input/Output (I/O) metrics from the pg_stat_io view.
• locks: Retrieves lock and wait state information from the pg_locks view.
• prepared_transactions: Retrieves statistics on prepared transactions from the pg_prepared_xacts view.
• replication: Retrieves metrics about standby nodes and streaming replication from the pg_stat_replication and pg_stat_wal_receiver views.
• replication_slots: Retrieves replication slot usage statistics from the pg_replication_slots view.
• statements_generic: Collects general statistics about statements from the pg_stat_statements view.
• statements_timing: Collects statistics on statement execution time from the pg_stat_statements view.
• statements_planning: Collects statistics on statement planning time from the pg_stat_statements view.
• statements_shared_io: Collects statistics on shared buffer usage by statements from the pg_stat_statements view.
• statements_local_io: Collects statistics on local buffer usage by statements from the pg_stat_statements view.
• statements_temp_io: Collects statistics on temporary file usage by statements from the pg_stat_statements view.
• statements_wal: Collects statistics on the amount of WAL written by statements from the pg_stat_statements view.
• statements_jit: Collects statistics on Just-In-Time (JIT) compiler usage by statements from the pg_stat_statements view.
• statements_queries: Retrieves information about statement texts from the pg_stat_statements view.
• tables: Retrieves table usage statistics from the pg_stat_user_tables and pg_statio_user_tables views.
• tablespaces: Retrieves tablespace usage statistics.
• version: Retrieves metrics with Postgres Pro version information.
• wait_sampling: Retrieves metrics based on wait event samples (requires the pg_wait_sampling extension).
• wal: Retrieves statistics on the amount of WAL written by the instance.

Each of these plugins can provide dozens of types of metrics. Depending on the number of objects in the instance, there may be thousands of metrics. However, some metrics or even metric groups may not be needed. Therefore, pgpro-otel-collector allows for the disabling of selected plugins, enabling control over the amount and quality of collected data.

Requirements and Limitations

For pgpro-otel-collector to operate correctly, the following requirements should be met:

• pgpro-otel-collector must be able to connect to a Postgres Pro instance. Connection settings are defined in the configuration file and can be modified as needed.
• The user connecting to the instance must have access to internal activity statistics.
• Privileged access (root) to the procfs file system may be required for operating system metric collection.
• Collecting instance activity logs requires access to the relevant directories and log files.

Note that at the moment, a single instance of pgpro-otel-collector supports data collection from a single database instance only.

Compatibility

pgpro-otel-collector supports the following PostgreSQL and Postgres Pro versions and editions:

• PostgreSQL versions as part of the official versioning policy. Compatibility with older versions is not guaranteed and may be revoked in case of any conflicts or complexity of support.
• Postgres Pro Standard and Enterprise editions, as well as those within the Postgres Pro official versioning policy.

Installation and Setup

1. pgpro-otel-collector is installed from the Postgres Pro software repository. To install the repository, download and run the script pgpro-repo-add.sh.
2. Once Postgres Pro repositories are installed and configured, update the package manager metadata and install the pgpro-otel-collector package.

Example for Debian/Ubuntu Linux distributions:

# wget https://repo.postgrespro.ru/otelcol/otelcol/keys/pgpro-repo-add.sh
# sh ./pgpro-repo-add.sh
# apt update
# apt install pgpro-otel-collector

3. After pgpro-otel-collector is installed, ensure that the configuration file /etc/pgpro-otel-collector/basic.yml contains valid connection parameters and the required set of plugins. Then start pgpro-otel-collector:

# systemctl start pgpro-otel-collector

pgpro-otel-collector will automatically start collecting and sending data according to the settings in the configuration file.

Configuration Parameters

The main configuration file is located in /etc/pgpro-otel-collector/basic.yml and is used by default. The file contains settings for pgpro-otel-collector internal components and a minimal set of plugins. It is assumed that with this minimal set, pgpro-otel-collector will always be able to collect information from any supported version of Postgres Pro. In the directory /usr/share/doc/pgpro-otel-collector/examples, you can find additional configuration files to enable other plugins. Before enabling any plugins, ensure that statistics are available on the database instance side. Note that enabling extra plugins can lead to high overhead (data collection costs) and should be considered carefully.

The postgrespro Receiver Settings

The postgrespro receiver settings can be divided into the following groups:

• Instance connection settings: By default, pgpro-otel-collector uses the address localhost:5432, the database postgres and the user postgres to connect. If necessary, these connection parameters can be overridden.
• Collection settings: These settings include the collection interval, the number of threads for parallel collection, and other parameters.
• Plugin settings: These settings control which plugins will be used and what information will be collected.

The otlphttp Exporter Settings

The otlphttp exporter is not used by default, because it requires additional configuration like specifying the monitoring system address where the information should be sent.

Service Settings

Services are the internal parts of pgpro-otel-collector responsible for its internal functions. pgpro-otel-collector uses the following services:

• telemetry: The telemetry services track pgpro-otel-collector activity, including logging and publishing internal metrics using the built-in HTTP server.
• pipelines: The pipeline services are responsible for data processing. Their task is to build chains of receivers and exporters, where receivers will collect data and transfer it to exporters, who will then provide the data to external consumers.

Troubleshooting

Use the below commands to display system details.

• pgpro-otel-collector status:

# systemctl status pgpro-otel-collector

• pgpro-otel-collector activity logs:

# journalctl -u pgpro-otel-collector

Logging level can be controlled by the service.telemetry.logs.level parameter and can take the following values: debug, info (default), warn, error.