PostgreSQL

Overview

PostgreSQL is a powerful, open-source relational database system renowned for its reliability, feature robustness, and performance. It supports advanced SQL features, complex queries, and provides extensibility with custom data types, operators, and functions.

Use Cases

• Monitor database connection pools and active sessions
• Track query execution time and slow queries
• Analyze disk usage and table statistics
• Monitor replication lag and streaming replication health
• Track transaction rates, commits, and rollbacks
• Monitor buffer cache efficiency and I/O operations

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

helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update
helm upgrade --install cnpg \
  --namespace cnpg-system \
  --create-namespace \
  cnpg/cloudnative-pg

Deploy a PostgreSQL Cluster

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

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgresql
spec:
  instances: 3

  # Enable monitoring with pg_stat_statements
  postgresql:
    shared_preload_libraries:
      - "pg_stat_statements"

  # Create monitoring user on initialization
  bootstrap:
    initdb:
      postInitApplicationSQL:
        - CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
        - CREATE USER dash0_monitor WITH PASSWORD 'monitor_password'; # Use secrets in production!
        - GRANT pg_monitor TO dash0_monitor;
        - GRANT SELECT ON pg_stat_statements TO dash0_monitor;

  # Storage - only specify what you need to change
  storage:
    size: 10Gi # Default is 1Gi

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:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_io_timing = on

Enable the Extension

Connect to your database and run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Create Monitoring User

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

-- Create monitoring user
CREATE USER dash0_monitor WITH PASSWORD 'secure_password';

-- Grant the pg_monitor role for comprehensive monitoring access
GRANT pg_monitor TO dash0_monitor;

-- If using pg_stat_statements for top query collection
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT SELECT ON pg_stat_statements TO dash0_monitor;

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:

receivers:
  postgresql:
    endpoint: postgresql.your-namespace.svc.cluster.local:5432
    databases:
      - your_database
    username: dash0_monitor
    password: monitor_password # Use ${env:POSTGRES_PASSWORD} in production!
    collection_interval: 10s
    tls:
      insecure: true
      insecure_skip_verify: true
    initial_delay: 1s

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