> ## Documentation Index
> Fetch the complete documentation index at: https://docs.querycomment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Connect PostgreSQL

> Connect PostgreSQL to QueryComment with pg_stat_statements and a read-only monitoring user.

Connect PostgreSQL by enabling `pg_stat_statements`, creating a dedicated monitoring user, and running the QueryComment collector.

<Note>
  QueryComment reads aggregate query, engine, and infrastructure telemetry. The collector does not proxy application traffic and does not write to your application tables.
</Note>

## Prepare PostgreSQL

<Steps>
  <Step title="Enable pg_stat_statements">
    QueryComment uses `pg_stat_statements` to read aggregate statement statistics keyed by `queryid`.

    Check whether it is already loaded:

    ```sql theme={null}
    SHOW shared_preload_libraries;
    ```

    If `pg_stat_statements` is not listed, add it to `postgresql.conf` or your managed database parameter group. Also disable utility statement tracking:

    ```ini theme={null}
    shared_preload_libraries = 'pg_stat_statements'
    pg_stat_statements.track_utility = off
    ```

    Restart PostgreSQL, then create the extension in the database you want to monitor:

    ```sql theme={null}
    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    ```

    <Warning>
      Changing `shared_preload_libraries` requires a PostgreSQL restart. Schedule this during a maintenance window for production databases.
    </Warning>
  </Step>

  <Step title="Create the monitoring user">
    Connect as a privileged PostgreSQL user and run:

    ```sql theme={null}
    CREATE USER querycomment WITH PASSWORD 'replace-with-strong-password';
    GRANT pg_monitor TO querycomment;
    GRANT pg_read_all_stats TO querycomment;
    ```

    `pg_monitor` gives the QueryComment collector read access to PostgreSQL monitoring views. `pg_read_all_stats` lets the receiver read `queryid` and statement statistics for statements run by other roles.
  </Step>

  <Step title="Verify access">
    Confirm the monitoring user can read statement statistics:

    ```sql theme={null}
    SET ROLE querycomment;
    SELECT queryid, calls, query
    FROM pg_stat_statements
    LIMIT 5;
    RESET ROLE;
    ```

    If this query fails, confirm that `pg_stat_statements` is installed in the database used by the collector connection string.
  </Step>
</Steps>

## Configure the QueryComment collector

Use the [QueryComment collector](https://github.com/querycomment/collector/releases/tag/v0.1.2) with the bundled PostgreSQL receiver. The collector is available as the [`ghcr.io/querycomment/collector:0.1.2`](https://github.com/querycomment/collector/pkgs/container/collector) Docker image or as a release package for your operating system.

Set these environment variables before starting the collector:

| Variable             | Description                                                                                            |
| -------------------- | ------------------------------------------------------------------------------------------------------ |
| `PG_HOST`            | PostgreSQL host name or IP address.                                                                    |
| `PG_PORT`            | PostgreSQL port. Use `5432` unless your instance uses a different port.                                |
| `PG_USER`            | Monitoring user name.                                                                                  |
| `PG_PASSWORD`        | Monitoring user password.                                                                              |
| `PG_DATABASE`        | Database to connect to. Use a database where `pg_stat_statements` is installed.                        |
| `PG_TLS_INSECURE`    | Optional. Set to `true` only when the connection should skip TLS. Defaults to `false`.                 |
| `PG_TLS_SKIP_VERIFY` | Optional. Set to `true` only when TLS certificate verification should be skipped. Defaults to `false`. |
| `QC_ENDPOINT`        | Optional QueryComment ingest endpoint. Defaults to `ingest.querycomment.com:443`.                      |
| `QC_INGEST_TOKEN`    | QueryComment ingest token from your dashboard.                                                         |
| `QC_DATABASE_NAME`   | Database or cluster name to attach to exported metrics.                                                |
| `OTEL_ENVIRONMENT`   | Deployment environment. Defaults to `production` if omitted.                                           |

Example `.env` file:

```bash theme={null}
PG_HOST=db.example.com
PG_PORT=5432
PG_USER=querycomment
PG_PASSWORD=replace-with-strong-password
PG_DATABASE=app

QC_INGEST_TOKEN=replace-with-querycomment-ingest-token
QC_DATABASE_NAME=production-postgres
OTEL_ENVIRONMENT=production
```

<Expandable title="postgresql.yaml">
  ```yaml theme={null}
  receivers:
    host_metrics:
      collection_interval: 30s
      scrapers:
        cpu: {}
        load: {}
        memory: {}
        paging: {}
        processes: {}
        system: {}
        disk: {}
        filesystem:
          exclude_fs_types:
            fs_types: [nfs, nfs4, cifs, smb3]
            match_type: strict
        network:
          metrics:
            system.network.connections:
              enabled: false

    postgresql:
      endpoint: ${env:PG_HOST}:${env:PG_PORT:-5432}
      transport: tcp
      username: ${env:PG_USER}
      password: ${env:PG_PASSWORD}
      databases:
        - ${env:PG_DATABASE}
      collection_interval: 20s
      tls:
        insecure: ${env:PG_TLS_INSECURE:-false}
        insecure_skip_verify: ${env:PG_TLS_SKIP_VERIFY:-false}
      query_metrics:
        enabled: ${env:QC_QUERY_METRICS_ENABLED:-true}
        limit: ${env:QC_QUERY_METRICS_LIMIT:-250}

  processors:
    memory_limiter:
      check_interval: 1s
      limit_percentage: 80
      spike_limit_percentage: 25
    resource_detection:
      detectors: [env, system]
      timeout: 5s
      override: true
      system:
        hostname_sources: [dns, os]
    resource/database:
      attributes:
        - key: db.system
          value: postgresql
          action: upsert
        - key: service.name
          value: ${env:QC_DATABASE_NAME}
          action: upsert
        - key: deployment.environment
          value: ${env:OTEL_ENVIRONMENT:-production}
          action: upsert
    batch:
      send_batch_size: 8192
      send_batch_max_size: 10000
      timeout: 5s

  exporters:
    otlp_grpc/querycomment:
      endpoint: ${env:QC_ENDPOINT:-ingest.querycomment.com:443}
      tls:
        insecure: ${env:QC_ENDPOINT_INSECURE:-false}
      headers:
        authorization: Bearer ${env:QC_INGEST_TOKEN}
      compression: gzip
      retry_on_failure:
        enabled: true
        initial_interval: 5s
        max_interval: 30s
        max_elapsed_time: 60s
      sending_queue:
        enabled: true
        num_consumers: 4
        queue_size: 5000

  service:
    pipelines:
      metrics:
        receivers: [host_metrics, postgresql]
        processors: [memory_limiter, resource_detection, resource/database, batch]
        exporters: [otlp_grpc/querycomment]
  ```
</Expandable>

Run the collector with Docker:

```bash theme={null}
docker run --rm \
  --env-file .env \
  ghcr.io/querycomment/collector:0.1.2 \
  --config /etc/querycommentcol/postgresql.yaml
```

Or choose the release package for your operating system and architecture. This example uses Linux amd64:

```bash theme={null}
curl -L -o querycommentcol_0.1.2_linux_amd64.tar.gz \
  https://github.com/querycomment/collector/releases/download/v0.1.2/querycommentcol_0.1.2_linux_amd64.tar.gz
tar -xzf querycommentcol_0.1.2_linux_amd64.tar.gz
cd querycommentcol_0.1.2_linux_amd64
set -a
. ../.env
set +a
./querycommentcol --config config/postgresql.yaml
```