# PostgreSQL

> Monitor PostgreSQL database performance and metrics with OpenTelemetry and Last9

Source: https://last9.io/docs/integrations/databases/postgresql/

Use OpenTelemetry to instrument your PostgreSQL database and send telemetry data to Last9. This integration provides comprehensive monitoring of database performance, slow queries, connection metrics, and system resource utilization.

Choose the setup that matches your environment:

- **With Docker** — run the OTel Collector and a Postgres exporter via Docker Compose.
- **Without Docker** — install the OTel Collector as a binary on the same host as PostgreSQL. No containers required.

## Prerequisites

- Last9 account with integration credentials
- Access to your PostgreSQL database with appropriate permissions
- For the **With Docker** setup: Docker and Docker Compose installed on your system
- For the **Without Docker** setup: PostgreSQL 14+ installed and running on the same host as the collector, with access to `postgresql.conf` to enable slow query logging

## Setup

**With Docker**

Read the [sample application](https://github.com/last9/opentelemetry-examples/tree/main/otel-collector/postgres) for more details.

1. **Verify Docker Installation**

   Check that Docker and Docker Compose are properly installed:

   ```bash
   # 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:

   ```yaml
   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:

   ```yaml
   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:

   ```yaml
   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:

   ```text
   <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:

   ```bash
   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

**Without Docker**

Read the [sample configuration](https://github.com/last9/opentelemetry-examples/tree/main/otel-collector/postgres-no-docker) for more details.

1.  **Create a PostgreSQL Monitoring User**

    Connect to PostgreSQL and create a dedicated monitoring user:

    ```sql
    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`):

    ```ini
    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:

    ```bash
    sudo systemctl reload postgresql
    ```

3.  **Install OpenTelemetry Collector**

**AMD64**

    ```bash
    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
    ```

**ARM64**

    ```bash
    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_arm64.deb
    sudo dpkg -i otelcol-contrib_0.144.0_linux_arm64.deb
    ```

**RPM (RHEL/CentOS)**

    ```bash
    wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.144.0/otelcol-contrib_0.144.0_linux_amd64.rpm
    sudo rpm -ivh otelcol-contrib_0.144.0_linux_amd64.rpm
    ```

4.  **Configure OpenTelemetry Collector**

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

    ```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](https://app.last9.io/integrations).

5.  **Set Environment Variables**

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

    ```bash
    POSTGRESQL_PASSWORD=your_secure_password
    ```

6.  **Grant Log File Access**

    The collector needs read access to PostgreSQL log files:

    ```bash
    sudo usermod -aG adm otelcol-contrib
    ```

7.  **Start the Collector**

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

---

## Understanding the setup (With Docker)

### Postgres Exporter

The Postgres Exporter connects to your PostgreSQL database and exposes metrics in Prometheus format. It's configured to:

- Connect to your database using the provided credentials
- Use custom queries defined in `queries.yaml`
- Expose metrics on port 9187

### Custom Queries

The `queries.yaml` file defines custom metrics to collect from PostgreSQL. The example includes a `slow_queries` metric that:

- Identifies queries running longer than 1 minute
- Collects detailed information about these queries including:
  - Process ID and database name
  - Username and application name
  - Query text and execution time
  - Wait events and blocking processes

### OpenTelemetry Collector

The OpenTelemetry Collector is configured to:

- Scrape metrics from Postgres Exporter every 60 seconds
- Add resource attributes like database name and environment
- Process metrics in batches for efficient transmission
- Export metrics to Last9 using the OTLP protocol

---

## Verification

**With Docker**

1. **Check container status**

   ```bash
   docker ps
   ```

2. **Test Postgres Exporter is exposing metrics**

   ```bash
   curl http://localhost:9187/metrics
   ```

3. **Review collector logs**

   ```bash
   docker logs otel-collector
   ```

4. **Verify metrics in Last9**

   Log into your Last9 account and verify metrics are being received in [Grafana](https://app.last9.io/grafana).

**Without Docker**

1. **Check collector status**

   ```bash
   sudo systemctl status otelcol-contrib
   ```

2. **View collector logs**

   ```bash
   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`.

---

## Troubleshooting

**With Docker**

- **Container issues**

  ```bash
  # Check container status
  docker ps -a

  # View container logs
  docker logs postgres-exporter
  docker logs otel-collector
  ```

- **Connection issues**

  ```bash
  # 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**

  ```bash
  # Check configuration
  docker exec otel-collector cat /etc/otel/collector/config.yaml

  # Restart collector
  docker compose restart otel-collector
  ```

**Without Docker**

- **Connection refused to PostgreSQL**

  ```bash
  # 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**

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

- **Collector cannot read logs**

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

Please get in touch with us on [Discord](https://discord.com/invite/Q3p2EEucx9) or [Email](mailto:support@last9.io) if you have any questions.