> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Monitoring PostgreSQL metrics with ClickStack

> Monitoring PostgreSQL Metrics with ClickStack

export const TrackedLink = ({href, eventName, children, ...rest}) => {
  const handleClick = () => {
    try {
      if (typeof window !== "undefined" && window.galaxy && eventName) {
        window.galaxy.track(eventName, {
          interaction: "click"
        });
      }
    } catch (e) {}
  };
  return <a href={href} onClick={handleClick} {...rest}>
      {children}
    </a>;
};

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

<Info>
  **TL;DR**

  Monitor PostgreSQL performance metrics in ClickStack using the OTel PostgreSQL receiver. Includes a demo dataset and pre-built dashboard.
</Info>

<h2 id="existing-postgres">
  Integration with existing PostgreSQL
</h2>

This section covers configuring your existing PostgreSQL installation to send metrics to ClickStack by configuring the ClickStack OTel collector with the PostgreSQL receiver.

If you would like to test the PostgreSQL metrics integration before configuring your own existing setup, you can test with our preconfigured demo dataset in the [following section](#demo-dataset).

<h5 id="prerequisites">
  Prerequisites
</h5>

* ClickStack instance running
* Existing PostgreSQL installation (version 9.6 or newer)
* Network access from ClickStack to PostgreSQL (default port 5432)
* PostgreSQL monitoring user with appropriate permissions

<Steps>
  <Step>
    <h4 id="monitoring-permissions">
      Ensure monitoring user has required permissions
    </h4>

    The PostgreSQL receiver requires a user with read access to statistics views. Grant the `pg_monitor` role to your monitoring user:

    ```sql theme={null}
    GRANT pg_monitor TO your_monitoring_user;
    ```
  </Step>

  <Step>
    <h4 id="create-custom-config">
      Create custom OTel collector configuration
    </h4>

    ClickStack allows you to extend the base OpenTelemetry collector configuration by mounting a custom configuration file and setting an environment variable.

    Create `postgres-metrics.yaml`:

    ```yaml theme={null}
    receivers:
      postgresql:
        endpoint: postgres-host:5432
        transport: tcp
        username: otel_monitor
        password: ${env:POSTGRES_PASSWORD}
        databases:
          - postgres
          - your_application_db # Replace with your actual database names
        collection_interval: 30s
        tls:
          insecure: true

    processors:
      resourcedetection:
        detectors: [env, system, docker]
        timeout: 5s
      batch:
        timeout: 10s
        send_batch_size: 10000

    exporters:
      clickhouse:
        endpoint: tcp://localhost:9000
        database: default
        ttl: 96h

    service:
      pipelines:
        metrics/postgres:
          receivers: [postgresql]
          processors: [resourcedetection, batch]
          exporters: [clickhouse]
    ```

    <Note>
      The `tls: insecure: true` setting disables SSL verification for development/testing. For production PostgreSQL with SSL enabled, remove this line or configure proper certificates.
    </Note>
  </Step>

  <Step>
    <h4 id="deploy-clickstack">
      Deploy ClickStack with custom configuration
    </h4>

    Mount your custom configuration:

    ```bash theme={null}
    docker run -d \
      --name clickstack-postgres \
      -p 8123:8123 -p 9000:9000 -p 4317:4317 -p 4318:4318 \
      -e HYPERDX_API_KEY=your-api-key \
      -e CLICKHOUSE_PASSWORD=your-clickhouse-password \
      -e POSTGRES_PASSWORD=secure_password_here \
      -e CUSTOM_OTELCOL_CONFIG_FILE=/etc/otelcol-contrib/custom.config.yaml \
      -v "$(pwd)/postgres-metrics.yaml:/etc/otelcol-contrib/custom.config.yaml:ro" \
      clickhouse/clickstack:latest
    ```
  </Step>

  <Step>
    <h4 id="verify-metrics">
      Verify metrics collection
    </h4>

    Once configured, log into HyperDX and verify metrics are flowing:

    1. Navigate to the Metrics explorer
    2. Search for metrics starting with postgresql. (e.g., postgresql.backends, postgresql.commits)
    3. You should see metric data points appearing at your configured collection interval

    Once metrics are flowing, proceed to the [Dashboards and visualization](#dashboards) section to import the pre-built dashboard.
  </Step>
</Steps>

<h2 id="demo-dataset">
  Demo dataset
</h2>

For users who want to test the PostgreSQL metrics integration before configuring their production systems, we provide a pre-generated dataset with realistic PostgreSQL metrics patterns.

<Info>
  **Database-level metrics only**

  This demo dataset includes database-level metrics only to keep the sample data lightweight. Table and index metrics are collected automatically when monitoring a real PostgreSQL database.
</Info>

<Steps>
  <Step>
    <h4 id="download-sample">
      Download the sample metrics dataset
    </h4>

    Download the pre-generated metrics files (24 hours of PostgreSQL metrics with realistic patterns):

    ```bash theme={null}
    # Download gauge metrics (connections, database size)
    curl -O https://datasets-documentation.s3.eu-west-3.amazonaws.com/clickstack-integrations/postgres/postgres-metrics-gauge.csv

    # Download sum metrics (commits, rollbacks, operations)
    curl -O https://datasets-documentation.s3.eu-west-3.amazonaws.com/clickstack-integrations/postgres/postgres-metrics-sum.csv
    ```

    The dataset includes realistic patterns:

    * **Morning connection spike (08:00)** - Login rush
    * **Cache performance issue (11:00)** - Blocks\_read spike
    * **Application bug (14:00-14:30)** - Rollback rate spikes to 15%
    * **Deadlock incidents (14:15, 16:30)** - Rare deadlocks
  </Step>

  <Step>
    <h4 id="start-clickstack">
      Start ClickStack
    </h4>

    Start a ClickStack instance:

    ```bash theme={null}
    docker run -d --name clickstack-postgres-demo \
      -p 8080:8080 -p 4317:4317 -p 4318:4318 \
      clickhouse/clickstack-all-in-one:latest
    ```

    Wait approximately 30 seconds for ClickStack to fully start.
  </Step>

  <Step>
    <h4 id="load-metrics">
      Load metrics into ClickStack
    </h4>

    Load the metrics directly into ClickHouse:

    ```bash theme={null}
    # Load gauge metrics
    cat postgres-metrics-gauge.csv | docker exec -i clickstack-postgres-demo \
      clickhouse-client --query "INSERT INTO otel_metrics_gauge FORMAT CSVWithNames"

    # Load sum metrics
    cat postgres-metrics-sum.csv | docker exec -i clickstack-postgres-demo \
      clickhouse-client --query "INSERT INTO otel_metrics_sum FORMAT CSVWithNames"
    ```
  </Step>

  <Step>
    <h4 id="verify-metrics-demo">
      Verify metrics in HyperDX
    </h4>

    Once loaded, the quickest way to see your metrics is through the pre-built dashboard.

    Proceed to the [Dashboards and visualization](#dashboards) section to import the dashboard and view many PostgreSQL metrics at once.

    <Info>
      **Timezone Display**

      HyperDX displays timestamps in your browser's local timezone. The demo data spans **2025-11-10 00:00:00 - 2025-11-11 00:00:00 (UTC)**. Set your time range to **2025-11-09 00:00:00 - 2025-11-12 00:00:00** to ensure you see the demo metrics regardless of your location. Once you see the metrics, you can narrow the range to a 24-hour period for clearer visualizations.
    </Info>
  </Step>
</Steps>

<h2 id="dashboards">
  Dashboards and visualization
</h2>

To help you get started monitoring PostgreSQL with ClickStack, we provide essential visualizations for PostgreSQL metrics.

<Steps>
  <Step>
    <h4 id="download">
      <TrackedLink href={'/examples/postgres-metrics-dashboard.json'} download="postgres-metrics-dashboard.json" eventName="docs.postgres_metrics_monitoring.dashboard_download">Download</TrackedLink> the dashboard configuration
    </h4>
  </Step>

  <Step>
    <h4 id="import-dashboard">
      Import the pre-built dashboard
    </h4>

    1. Open HyperDX and navigate to the Dashboards section
    2. Click **Import Dashboard** in the upper right corner under the ellipses

    <Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/zXCQbzXFHfeD9FBK/images/clickstack/import-dashboard.png?fit=max&auto=format&n=zXCQbzXFHfeD9FBK&q=85&s=eace17d7f86efbec4d3151bbf428941a" alt="Import dashboard button" width="3024" height="556" data-path="images/clickstack/import-dashboard.png" />

    3. Upload the `postgres-metrics-dashboard.json` file and click **Finish Import**

    <Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/zXCQbzXFHfeD9FBK/images/clickstack/postgres/import-dashboard.png?fit=max&auto=format&n=zXCQbzXFHfeD9FBK&q=85&s=ac5d1d4501422086a25949f417f01a62" alt="Finish import dialog" width="3808" height="1910" data-path="images/clickstack/postgres/import-dashboard.png" />
  </Step>

  <Step>
    <h4 id="created-dashboard">
      View the dashboard
    </h4>

    The dashboard will be created with all visualizations pre-configured:

    <Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/huP88Vza7bEG09HU/images/clickstack/postgres/postgres-metrics-dashboard.png?fit=max&auto=format&n=huP88Vza7bEG09HU&q=85&s=6051178bdc9821b8fc8da0c6bd90d5b2" alt="PostgreSQL metrics dashboard" width="3808" height="1910" data-path="images/clickstack/postgres/postgres-metrics-dashboard.png" />

    <Note>
      For the demo dataset, set the time range to **2025-11-10 00:00:00 - 2025-11-11 00:00:00 (UTC)** (adjust based on your local timezone). The imported dashboard won't have a time range specified by default.
    </Note>
  </Step>
</Steps>

<h2 id="troubleshooting">
  Troubleshooting
</h2>

<h3 id="troubleshooting-not-loading">
  Custom config not loading
</h3>

Verify the environment variable is set:

```bash theme={null}
docker exec <container-name> printenv CUSTOM_OTELCOL_CONFIG_FILE
```

Check the custom config file is mounted:

```bash theme={null}
docker exec <container-name> cat /etc/otelcol-contrib/custom.config.yaml
```

<h3 id="no-metrics">
  No metrics appearing in HyperDX
</h3>

Verify PostgreSQL is accessible:

```bash theme={null}
docker exec <clickstack-container> psql -h postgres-host -U otel_monitor -d postgres -c "SELECT 1"
```

Check OTel collector logs:

```bash theme={null}
docker exec <container> cat /etc/otel/supervisor-data/agent.log | grep -i postgres
```

<h3 id="auth-errors">
  Authentication errors
</h3>

Verify password is set correctly:

```bash theme={null}
docker exec <clickstack-container> printenv POSTGRES_PASSWORD
```

Test credentials directly:

```bash theme={null}
psql -h postgres-host -U otel_monitor -d postgres -c "SELECT version();"
```

<h2 id="next-steps">
  Next steps
</h2>

* Set up [alerts](/clickstack/features/alerts) for critical thresholds (connection limits, high rollback rates, low cache hit ratios)
* Enable query-level monitoring with `pg_stat_statements` extension
* Monitor multiple PostgreSQL instances by duplicating the receiver configuration with different endpoints and service names

<h2 id="going-to-production">
  Going to production
</h2>

This guide extends ClickStack's built-in OpenTelemetry Collector for quick setup. For production deployments, we recommend running your own OTel Collector and sending data to ClickStack's OTLP endpoint. See [Sending OpenTelemetry data](/clickstack/ingesting-data/opentelemetry) for production configuration.