PostgreSQL

Overview

PostgreSQL exposes comprehensive metrics through its built-in statistics views that can be collected using the OpenTelemetry Collector's PostgreSQL receiver. This integration provides deep visibility into database performance, query execution, connection management, and resource utilization.

Prerequisites

Before setting up PostgreSQL monitoring, ensure:

• PostgreSQL is running and accessible
• You have credentials to connect to the PostgreSQL database
• The pg_stat_statements extension is enabled for query-level metrics (optional but recommended)
• OpenTelemetry Collector is deployed in your environment using the OpenTelemetry Collector Contrib distribution.

Setting up PostgreSQL

For Kubernetes environments, we recommend using a PostgreSQL operator for production-ready deployments. Here's an example using CloudNativePG, which provides comprehensive PostgreSQL clusters with built-in monitoring capabilities.

Note: This is just one example of a production setup. You can use any PostgreSQL deployment method (including other operators like Zalando PostgreSQL Operator, CrunchyData PGO, or manual deployments) as long as the PostgreSQL receiver can connect to your database.

Install CloudNativePG Operator

Deploy a PostgreSQL Cluster

Here's a simple example that relies on CloudNativePG's defaults:

Production Tips

For production environments:

1. Use Kubernetes Secrets - Use CloudNativePG's secret management features instead of inline passwords
2. Set instances: 3 for high availability
3. Use dedicated app user instead of postgres superuser as the database owner
4. Adjust resources based on your workload
5. Use secure storage class with encryption at rest

Manual PostgreSQL Setup

For non-Kubernetes environments or custom setups:

Enable pg_stat_statements Extension

For comprehensive query monitoring, enable the pg_stat_statements extension:

PostgreSQL Configuration

Add to your postgresql.conf:

Enable the Extension

Connect to your database and run:

Create Monitoring User

For security best practices, create a dedicated monitoring user with the built-in pg_monitor role:

Collector Configuration

The OpenTelemetry Collector uses the PostgreSQL receiver to collect metrics from PostgreSQL statistics views. Add the following configuration to your OpenTelemetry collector:

Collector Configuration

Add the PostgreSQL receiver to your collector configuration:

For more options see the PostgreSQL receiver documentation

Collector Deployment

Deploy the OpenTelemetry Collector with the PostgreSQL receiver configuration. For detailed deployment instructions, see our other integrations for deploying the OpenTelemetry Collector:

• OpenTelemetry Operator for Kubernetes

• Helm Chart for The OpenTelemetry Collector

Verification

After configuring the collector, verify that metrics are being collected:

1. Check the collector logs for successful connections to PostgreSQL
2. Navigate to the Metrics Explorer in Dash0
3. Search for metrics with prefix postgresql.*
4. Confirm connections and capacity metrics are visible:
   • postgresql.backends (active connections)
   • postgresql.connection.max (configured max connections)
   • postgresql.db_size (database size in bytes)
   • postgresql.commits (transaction commits)
   • postgresql.rollbacks (transaction rollbacks)

References

• OpenTelemetry PostgreSQL Receiver Documentation
• CloudNativePG Documentation
• CloudNativePG Security Guide
• PostgreSQL Statistics Views
• pg_stat_statements Extension
• PostgreSQL Monitoring Best Practices