PostgreSQL receiver

The PostgreSQL receiver is a component of the OpenTelemetry Collector. It connects to a PostgreSQL instance and supports metrics and logs pipelines.

PostgreSQL support starts from these minimum collector versions:

• Splunk Distribution of the OpenTelemetry Collector (splunk-otel-collector) v0.147.0 or later
• Community (OSS) version of the OpenTelemetry Collector (opentelemetry-collector-contrib) v0.147.0 or later

Supported versions and platforms

Splunk Database Monitoring supports these PostgreSQL versions and platforms:

• Versions: Azure Database for PostgreSQL Flexible Server versions 14.20 and 17.7, Amazon RDS for PostgreSQL versions 14.15 and 17.5
• Platforms: Microsoft Azure as a managed PaaS (single server instance), AWS as a managed PaaS (RDS instance)

Prerequisites

• Create the monitoring user:

  SQLCopy

  CREATE USER otel-user WITH PASSWORD 'otel-user-password'; GRANT pg_monitor TO otel-user; GRANT SELECT ON pg_stat_database TO otel-user;

  CREATE USER otel-user WITH PASSWORD 'otel-user-password';
  GRANT pg_monitor TO otel-user;
  GRANT SELECT ON pg_stat_database TO otel-user;

• Configure the database:

  AWS PostgreSQL

  Tip: New AWS PostgreSQL parameter groups already include pg_stat_statements in shared_preload_libraries.

  To create the extension in each target database, connect to each database the receiver will scrape and run this command:

  SQLCopy

  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  Azure PostgreSQL

  1. Update server parameters in Azure Portal:

     1. In Azure Portal, navigate to PosgreSQM Flexible Server > Settings > Server parameters and set the following parameters:

        Note: If a parameter already has values, use a comma-separated list and add pg_stat_statements instead of removing the existing values:

        • azure.extensions = pg_stat_statements

        • shared_preload_libraries = pg_stat_statements

        • pg_stat_statements.track = all

        • pg_stat_statements.max = 10000

        • pg_stat_statements.track_utility = on

     2. Select Save.

     3. Restart the server.

  2. To create the extension in each target database, connect to each database the receiver will scrape and run this command:

     CODECopy

     CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

     CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

     Sample connection:

     CODECopy

     psql "host=server.postgres.database.azure.com \ port=5432 \ dbname=postgres \ user=otel-user \ sslmode=require"

     psql "host=server.postgres.database.azure.com \
           port=5432 \
           dbname=postgres \
           user=otel-user \
           sslmode=require"

  Standalone PostgreSQL

  1. Enable pg_stat_statements

     top_query requires pg_stat_statements to be loaded at server startup. If your environment supports server configuration changes, do this:

     1. Add this line to postgresql.conf:

        SQLCopy

        shared_preload_libraries = 'pg_stat_statements'

        shared_preload_libraries = 'pg_stat_statements'

     2. Restart PostgreSQL.

     3. If your environment supports ALTER SYSTEM, you can also use:

        SQLCopy

        ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

        ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

     4. Restart PostgreSQL.

  2. To create the extension in each target database, connect to each database the receiver will scrape and run this command:

     SQLCopy

     CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

     CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Configure the receiver

Modify your collector configuration file as follows. All examples are for the Splunk Distribution of the OpenTelemetry Collector.

1. In the receivers: section, add postgresql:

   YAMLCopy

   postgresql: collection_interval: 10s databases: - postgres endpoint: your-service-endpoint:5432 events: db.server.query_sample: enabled: true db.server.top_query: enabled: true username: otel-user password: otel-user-password tls: insecure: true

   postgresql:
     collection_interval: 10s
     databases:
       - postgres
     endpoint: your-service-endpoint:5432
     events:
       db.server.query_sample:
         enabled: true
       db.server.top_query:
         enabled: true
     username: otel-user
     password: otel-user-password
     tls:
       insecure: true

   Important:

   If you're using the Splunk Distribution of OpenTelemetry Collector, leave the following receiver settings at their default values:

   • collection_interval (Default: 10s)

   • query_sample_collection.max_rows_per_query (Default: 100)

   • top_query_collection.collection_interval (Default: 60s)

   • top_query_collection.max_query_sample_count (Default: 1000)

   These values support Database Monitoring without affecting the performance of the database or the collector. If you increase these values you might adversely affect the performance of your database or collector, and this could result in ingest throttling.

2. In the exporters: section, add otlp_http/dbmon:

   YAMLCopy

   otlp_http/dbmon: headers: X-SF-Token: your-splunk-access-token X-splunk-instrumentation-library: dbmon logs_endpoint: https://ingest.your-splunk-realm./v3/event sending_queue: batch: flush_timeout: 15s max_size: 10485760 sizer: bytes

   otlp_http/dbmon:
     headers:
       X-SF-Token: your-splunk-access-token
       X-splunk-instrumentation-library: dbmon
     logs_endpoint: https://ingest.your-splunk-realm./v3/event
     sending_queue:
       batch:
         flush_timeout: 15s
         max_size: 10485760
         sizer: bytes

3. In the service.pipelines: section, create a metrics pipeline named metrics/dbmon and a logs pipeline named logs/dbmon:

   YAMLCopy

   metrics/dbmon: receivers: - postgresql processors: - memory_limiter - batch exporters: - signalfx logs/dbmon: receivers: - postgresql processors: - memory_limiter - batch exporters: - otlp_http/dbmon

   metrics/dbmon:
     receivers:
       - postgresql
     processors:
       - memory_limiter
       - batch
     exporters:
       - signalfx
   logs/dbmon:
     receivers:
       - postgresql
     processors:
       - memory_limiter
       - batch
     exporters:
       - otlp_http/dbmon

   Important: Use an identical list of processors for the metrics and logs/dbmon pipelines, and include these processors in the same order.

4. Restart the collector to apply your configuration changes.

   The restart command varies depending on what platform you deployed the collector on and what tool you used to deploy it. Here are general examples of the restart command:

   Linux

   Linux with installer script:

   BASHCopy

   sudo systemctl restart splunk-otel-collector

   sudo systemctl restart splunk-otel-collector

   Windows

   Windows with installer script:

   BASHCopy

   stop-service splunk-otel-collector start-service splunk-otel-collector

   stop-service splunk-otel-collector
   start-service splunk-otel-collector

   Kubernetes

   Kubernetes with Helm:

   BASHCopy

   helm upgrade your-splunk-otel-collector splunk-otel-collector-chart/splunk-otel-collector -f your-override-values.yaml

   helm upgrade your-splunk-otel-collector splunk-otel-collector-chart/splunk-otel-collector -f your-override-values.yaml

   where splunk-otel-collector-chart is the name you gave to the Helm chart in the helm repo add command.

Your database instance should now be visible on APM > Database monitoring as well as on Infrastructure > Infrastructure monitoring if you have a Database Monitoring license. For troubleshooting, see Troubleshoot data collection .

Advanced configurations

Collect data from multiple database instances

Omit the database name parameter, receivers.postgresql.database. If omitted, the receiver collects metrics from all instances.

Set up APM correlation

See Correlate database queries with Splunk APM traces.

Settings reference

Metrics reference