PostgreSQL

Read the sample application for more details.

1. Verify Docker Installation

   Check that Docker and Docker Compose are properly installed:

   # Check Docker installation
   docker --version
   
   # Check Docker Compose installation
   docker compose version

2. Configure OpenTelemetry Collector

   Create an otel-collector-config.yaml file with the following configuration. This defines the Prometheus receiver for scraping Postgres metrics, processors for batch processing and resource detection, and the Last9 exporter configuration:

   receivers:
     prometheus:
       config:
         scrape_configs:
           - job_name: postgres-exporter
             scrape_interval: 60s
             static_configs:
               - targets: ["postgres-exporter:9187"]
   
   processors:
     batch:
       timeout: 10s
       send_batch_size: 10000
     resourcedetection:
       detectors: [env, system, docker, ec2, azure, gcp]
       timeout: 2s
     resource:
       attributes:
         - key: db_name
           value: postgres-db
           action: upsert
         - key: deployment.environment
           value: dev
           action: upsert
   
   exporters:
     otlp/last9:
       endpoint: "{{ .Logs.WriteURL }}"
       headers:
         "Authorization": "{{ .Logs.AuthValue }}"
   
     debug:
       verbosity: detailed
   
   service:
     pipelines:
       metrics:
         receivers: [prometheus]
         processors: [resourcedetection, resource, batch]
         exporters: [otlp/last9]
   
     telemetry:
       logs:
         level: info

   Replace the placeholder values in the exporters section with your actual Last9 credentials from the Last9 Integrations page.

3. Configure Custom Database Queries

   Create a queries.yaml file to define custom metrics for monitoring slow queries and database performance:

   slow_queries:
     query: |
       SELECT
         pid,
         datname AS database,
         usename AS user,
         application_name,
         client_addr,
         EXTRACT(EPOCH FROM (now() - query_start)) AS query_time_seconds,
         REGEXP_REPLACE(SUBSTRING(query, 1, 500), E'[\n\r]+', ' ', 'g') AS query,
         state,
         wait_event_type,
         wait_event,
         backend_type,
         pg_blocking_pids(pid) AS blocked_by
       FROM pg_stat_activity
       WHERE (now() - query_start) > interval '1 minute'
         AND state <> 'idle'
         AND query NOT ILIKE '%pg_stat%'
         AND query NOT ILIKE '%pg_catalog%'
       ORDER BY query_time_seconds DESC
     metrics:
       - pid:
           usage: "LABEL"
           description: "Process ID"
       - database:
           usage: "LABEL"
           description: "Database name"
       - user:
           usage: "LABEL"
           description: "Username"
       - application_name:
           usage: "LABEL"
           description: "Application name"
       - client_addr:
           usage: "LABEL"
           description: "Client address"
       - query_time_seconds:
           usage: "GAUGE"
           description: "Query execution time in seconds"
       - query:
           usage: "LABEL"
           description: "Query text (first 500 chars)"
       - state:
           usage: "LABEL"
           description: "Query state"
       - wait_event_type:
           usage: "LABEL"
           description: "Type of event the process is waiting for"
       - wait_event:
           usage: "LABEL"
           description: "Name of the event the process is waiting for"
       - backend_type:
           usage: "LABEL"
           description: "Type of backend"
       - blocked_by:
           usage: "LABEL"
           description: "PIDs of sessions blocking this query"

4. Set Up Docker Compose Configuration

   Create a docker-compose.yaml file with your PostgreSQL database connection details:

   services:
     postgres-exporter:
       image: quay.io/prometheuscommunity/postgres-exporter
       environment:
         - DATA_SOURCE_URI=<DB_HOST>/<DB_NAME>
         - DATA_SOURCE_USER=<DB_USER>
         - DATA_SOURCE_PASS=<DB_PASSWORD>
       volumes:
         - ./queries.yaml:/queries.yaml
       command: --extend.query-path="/queries.yaml"
       restart: unless-stopped
       ports:
         - "9187:9187"
     otel-collector:
       image: otel/opentelemetry-collector-contrib:0.118.0
       volumes:
         - ./otel-collector-config.yaml:/etc/otel/collector/config.yaml
       command: --config=/etc/otel/collector/config.yaml
       depends_on:
         - postgres-exporter

   Replace the following placeholders with your actual PostgreSQL database information:

   <DB_HOST>     — Your PostgreSQL database host
   <DB_NAME>     — Your PostgreSQL database name
   <DB_USER>     — Your PostgreSQL database username
   <DB_PASSWORD> — Your PostgreSQL database password

5. Start the Monitoring Stack

   Launch the monitoring services using Docker Compose:

   docker compose -f docker-compose.yaml up -d

   This command starts:

   • Postgres Exporter: Collects metrics from your PostgreSQL database
   • OpenTelemetry Collector: Receives metrics from Postgres Exporter and forwards them to Last9

Read the sample configuration for more details.

1. Create a PostgreSQL Monitoring User

   Connect to PostgreSQL and create a dedicated monitoring user:

   CREATE USER otel WITH PASSWORD 'your_secure_password';
   GRANT pg_monitor TO otel;

2. Enable Slow Query Logging

   Edit postgresql.conf (typically /etc/postgresql/<version>/main/postgresql.conf):

   log_min_duration_statement = 1000   # log queries slower than 1s (ms)
   log_line_prefix = '%t [%p] %u@%d '
   logging_collector = on
   log_directory = '/var/log/postgresql'
   log_filename = 'postgresql-%Y-%m-%d.log'

   Reload PostgreSQL:

   sudo systemctl reload postgresql

3. Install OpenTelemetry Collector

   • AMD64
   • ARM64
   • RPM (RHEL/CentOS)

   sudo apt-get update && sudo apt-get install -y wget
   wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.144.0/otelcol-contrib_0.144.0_linux_amd64.deb
   sudo dpkg -i otelcol-contrib_0.144.0_linux_amd64.deb

4. Configure OpenTelemetry Collector

   Create /etc/otelcol-contrib/config.yaml:

   receivers:
     postgresql:
       endpoint: localhost:5432
       username: otel
       password: ${env:POSTGRESQL_PASSWORD}
       databases:
         - postgres
       collection_interval: 60s
       tls:
         insecure: true
   
     filelog:
       # Adjust path for your OS and PostgreSQL version:
       # Ubuntu/Debian: /var/log/postgresql/postgresql-<version>-main.log
       # RHEL/CentOS:   /var/lib/pgsql/<version>/data/log/postgresql-*.log
       include: [/var/log/postgresql/postgresql-*.log]
       include_file_path: true
       start_at: end
       retry_on_failure:
         enabled: true
       multiline:
         line_start_pattern: '^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}'
       operators:
         - type: regex_parser
           if: body matches "duration:"
           regex: '(?P<timestamp>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}\s\w+)\s+\[(?P<pid>\d+)\]\s+(?P<user>[^@]+)@(?P<database>\S+)\s+LOG:\s+duration:\s+(?P<duration_ms>[\d.]+)\s+ms\s+(?P<stmt_type>\w+):\s+(?P<query>.*)'
           on_error: send
   
     hostmetrics:
       collection_interval: 60s
       scrapers:
         cpu:
           metrics:
             system.cpu.logical.count:
               enabled: true
         memory:
           metrics:
             system.memory.utilization:
               enabled: true
             system.memory.limit:
               enabled: true
         load:
         disk:
         filesystem:
           metrics:
             system.filesystem.utilization:
               enabled: true
         network:
         paging:
   
   processors:
     batch:
       timeout: 5s
       send_batch_size: 10000
       send_batch_max_size: 10000
     resourcedetection/system:
       detectors: ["system"]
       system:
         hostname_sources: ["os"]
     transform/logs:
       flatten_data: true
       log_statements:
         - context: log
           statements:
             - set(observed_time, Now())
             - set(time_unix_nano, observed_time_unix_nano) where time_unix_nano == 0
             - set(resource.attributes["service.name"], "postgresql")
             - set(resource.attributes["deployment.environment"], "production")
     transform/slow_queries:
       log_statements:
         - context: log
           conditions:
             - attributes["duration_ms"] != nil
           statements:
             - set(attributes["db.system"], "postgresql")
             - set(attributes["db.operation.duration_ms"], attributes["duration_ms"])
             - set(attributes["db.user"], attributes["user"])
             - set(attributes["db.namespace"], attributes["database"])
             - set(attributes["db.query.text"], attributes["query"])
             - set(attributes["db.pid"], attributes["pid"])
             - set(attributes["slow_query"], true)
             - set(severity_text, "WARN")
   
   exporters:
     otlp/last9:
       endpoint: "{{ .Logs.WriteURL }}"
       headers:
         "Authorization": "{{ .Logs.AuthValue }}"
     debug:
       verbosity: detailed
   
   service:
     pipelines:
       logs:
         receivers: [filelog]
         processors: [batch, resourcedetection/system, transform/logs, transform/slow_queries]
         exporters: [otlp/last9, debug]
       metrics:
         receivers: [postgresql, hostmetrics]
         processors: [batch, resourcedetection/system]
         exporters: [otlp/last9, debug]

   Replace the endpoint and Authorization values with your actual Last9 credentials from the Integrations page.

5. Set Environment Variables

   Edit /etc/otelcol-contrib/otelcol-contrib.env and add:

   POSTGRESQL_PASSWORD=your_secure_password

6. Grant Log File Access

   The collector needs read access to PostgreSQL log files:

   sudo usermod -aG adm otelcol-contrib

7. Start the Collector

   sudo systemctl daemon-reload
   sudo systemctl enable otelcol-contrib
   sudo systemctl start otelcol-contrib --feature-gates transform.flatten.logs

1. Check container status

   docker ps

2. Test Postgres Exporter is exposing metrics

   curl http://localhost:9187/metrics

3. Review collector logs

   docker logs otel-collector

4. Verify metrics in Last9

   Log into your Last9 account and verify metrics are being received in Grafana.

1. Check collector status

   sudo systemctl status otelcol-contrib

2. View collector logs

   sudo journalctl -u otelcol-contrib -f

3. Verify metrics in Last9

   Log into your Last9 account and verify metrics are being received. Look for metrics like postgresql.backends, postgresql.commits, and postgresql.rows.

• Container issues

  # Check container status
  docker ps -a
  
  # View container logs
  docker logs postgres-exporter
  docker logs otel-collector

• Connection issues

  # Check Postgres Exporter logs for connection errors
  docker logs postgres-exporter

  Common connection issues include:

  • Incorrect database credentials
  • Network connectivity problems
  • PostgreSQL not allowing connections from the exporter

• OpenTelemetry Collector issues

  # Check configuration
  docker exec otel-collector cat /etc/otel/collector/config.yaml
  
  # Restart collector
  docker compose restart otel-collector

• Connection refused to PostgreSQL

  # Verify PostgreSQL is running
  sudo systemctl status postgresql
  
  # Test connection with monitoring user
  psql -U otel -h localhost -c "SELECT 1;"

• Log files not found

  # Find your log directory
  sudo -u postgres psql -c "SHOW log_directory;"
  sudo -u postgres psql -c "SHOW log_filename;"

• Collector cannot read logs

  # Check file permissions
  ls -la /var/log/postgresql/
  # Ensure otelcol-contrib is in the adm group
  groups otelcol-contrib