PostgreSQL Monitoring - Database Observability

Overview

Monitor PostgreSQL performance and health using the OpenTelemetry Collector with the PostgreSQL receiver. Get visibility into connections, queries, replication, and resource usage.

What You'll Monitor

• Connection counts and pool usage
• Query performance and slow queries
• Database size and table statistics
• Replication lag
• Lock contention
• Buffer cache hit ratio

OpenTelemetry Collector Setup

Use the OpenTelemetry Collector with the PostgreSQL receiver to collect database metrics.

1. Create a Monitoring User

-- Create a dedicated monitoring user
CREATE USER qorrelate_monitor WITH PASSWORD 'secure_password';

-- Grant necessary permissions
GRANT pg_monitor TO qorrelate_monitor;

-- Or for older PostgreSQL versions (< 10):
GRANT SELECT ON pg_stat_database TO qorrelate_monitor;
GRANT SELECT ON pg_stat_bgwriter TO qorrelate_monitor;
GRANT SELECT ON pg_stat_activity TO qorrelate_monitor;

2. Configure the Collector

# otel-collector-config.yaml
receivers:
  postgresql:
    endpoint: localhost:5432
    username: qorrelate_monitor
    password: ${POSTGRES_PASSWORD}
    databases:
      - myapp_production
      - myapp_analytics
    collection_interval: 30s
    tls:
      insecure: false
      ca_file: /etc/ssl/certs/ca-certificates.crt

exporters:
  otlphttp:
    endpoint: https://qorrelate.io
    headers:
      Authorization: "Bearer ${QORRELATE_API_KEY}"
      X-Organization-Id: "${QORRELATE_ORG_ID}"

service:
  pipelines:
    metrics:
      receivers: [postgresql]
      exporters: [otlphttp]

3. Run the Collector

# Docker
docker run -v ./otel-collector-config.yaml:/etc/otel/config.yaml \
  -e POSTGRES_PASSWORD=secure_password \
  -e QORRELATE_API_KEY=your_api_key \
  -e QORRELATE_ORG_ID=your_org_id \
  otel/opentelemetry-collector-contrib:latest \
  --config=/etc/otel/config.yaml

# Or Kubernetes (add as sidecar or DaemonSet)

Available Metrics

Metric	Description	Type
`postgresql.backends`	Active connections by database	Gauge
`postgresql.commits`	Committed transactions	Counter
`postgresql.rollbacks`	Rolled back transactions	Counter
`postgresql.db_size`	Database size in bytes	Gauge
`postgresql.rows_fetched`	Rows fetched by queries	Counter
`postgresql.rows_inserted`	Rows inserted	Counter
`postgresql.rows_updated`	Rows updated	Counter
`postgresql.rows_deleted`	Rows deleted	Counter
`postgresql.blocks_read`	Disk blocks read	Counter
`postgresql.blocks_hit`	Buffer cache hits	Counter
`postgresql.replication.lag`	Replication lag in bytes	Gauge

Application-Level Query Tracing

To trace individual SQL queries from your application, use OpenTelemetry SDK instrumentation.

Python (psycopg2/asyncpg)

pip install opentelemetry-instrumentation-psycopg2
# or
pip install opentelemetry-instrumentation-asyncpg

from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor

# Auto-instrument all psycopg2 connections
Psycopg2Instrumentor().instrument()

# Your queries are now traced automatically
cursor.execute("SELECT * FROM users WHERE id = %s", [user_id])

Node.js (pg)

npm install @opentelemetry/instrumentation-pg

const { PgInstrumentation } = require('@opentelemetry/instrumentation-pg');

// Add to your instrumentation setup
instrumentations: [
  new PgInstrumentation({
    enhancedDatabaseReporting: true
  })
]

Java (JDBC)

<!-- The Java agent auto-instruments JDBC -->
<!-- Just run with: -javaagent:opentelemetry-javaagent.jar -->

Slow Query Logging

Configure PostgreSQL to log slow queries, then forward them to Qorrelate:

1. Enable Slow Query Logging in PostgreSQL

-- In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET log_min_duration_statement = '500';  -- Log queries > 500ms
ALTER SYSTEM SET log_statement = 'none';  -- Don't log all statements
SELECT pg_reload_conf();

2. Forward Logs to Qorrelate

# Add filelog receiver to collector
receivers:
  filelog:
    include: [/var/log/postgresql/*.log]
    operators:
      - type: regex_parser
        regex: '^(?P<timestamp>[\d-]+ [\d:.]+) .* LOG:  duration: (?P<duration>[\d.]+) ms  statement: (?P<query>.*)$'
      - type: add
        field: attributes.service.name
        value: postgresql

Pre-built Dashboard Queries

Use these PromQL queries to build your PostgreSQL dashboard:

Active Connections

sum by (database) (postgresql_backends)

Buffer Cache Hit Ratio

100 * sum(rate(postgresql_blocks_hit[5m])) / 
  (sum(rate(postgresql_blocks_hit[5m])) + sum(rate(postgresql_blocks_read[5m])))

Transaction Rate

sum(rate(postgresql_commits[5m])) + sum(rate(postgresql_rollbacks[5m]))

Rollback Ratio

100 * sum(rate(postgresql_rollbacks[5m])) / 
  (sum(rate(postgresql_commits[5m])) + sum(rate(postgresql_rollbacks[5m])))

Recommended Alerts

Connection Pool Exhaustion

postgresql_backends / postgresql_max_connections > 0.8

Alert when >80% of connections are in use

Replication Lag

postgresql_replication_lag_bytes > 104857600

Alert when replication lag exceeds 100MB

Low Cache Hit Ratio

100 * sum(rate(postgresql_blocks_hit[5m])) / 
  (sum(rate(postgresql_blocks_hit[5m])) + sum(rate(postgresql_blocks_read[5m]))) < 90

Alert when buffer cache hit ratio drops below 90%