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

# Monitoring PostgreSQL metrics with ClickHouse Observability

> Collect PostgreSQL metrics with the OpenTelemetry collector and send them to ClickHouse Observability.

Collect PostgreSQL metrics with the OpenTelemetry PostgreSQL receiver and send them to ClickHouse Observability. Use the [demo dataset](#demo-dataset) to try the integration without installing PostgreSQL.

## Prerequisites

* A ClickHouse Observability service
* Docker and `curl`
* For your own metrics, PostgreSQL 14 or newer reachable from the collector, and a monitoring user

### Connection details

> [tab] Fully managed
>
> [Beta offering](https://clickhouse.com/docs/reference/settings/beta-and-experimental-features.md#beta-features)
>
> Open ClickHouse Observability and go to **Team Settings → Data → Managed Telemetry**. Copy your OTLP HTTP endpoint and API key. You can use **Copy connection details** to copy the configuration, or **Reveal key** to view the masked key.
>
> ![Managed Telemetry connection details](https://mintcdn.com/private-7c7dfe99/Zk1feNn6fCQBpold/images/clickstack/getting-started/observability-managed-telemetry.png?fit=max\&auto=format\&n=Zk1feNn6fCQBpold\&q=85\&s=e63a2ac2e6ab996d3fc360ebe0f487a5)
>
> For the OTLP exporter examples below, set these variables in the terminal where you will run the collector:
>
> ```bash theme={null}
> export OTEL_EXPORTER_OTLP_ENDPOINT='https://YOUR_OTEL_ENDPOINT:4318'
> export OTEL_EXPORTER_OTLP_AUTHORIZATION='Bearer YOUR_API_KEY'
> ```
>
> Replace the placeholders with the exact endpoint, including its port, and API key from your service. Use the base HTTP endpoint without a `/v1/metrics` suffix; the exporter appends the signal path.

> [tab] Self-managed
>
> Use your collector's reachable OTLP HTTP endpoint (for example, `http://YOUR_COLLECTOR_HOST:4318`) and authentication settings. If your collector does not require authentication, omit the exporter's `headers` block from the configurations below. See the [self-managed getting-started guide](https://clickhouse.com/docs/clickstack/self-configured/getting-started/managed.md) for setup guidance.
>
> ```bash theme={null}
> export OTEL_EXPORTER_OTLP_ENDPOINT='http://YOUR_COLLECTOR_HOST:4318'
> export OTEL_EXPORTER_OTLP_AUTHORIZATION='YOUR_INGESTION_KEY'
> ```

The endpoint must be reachable from the collector container. For a destination collector running on this computer, use `http://host.docker.internal:4318`. The Docker commands below include the host mapping.

## Integration with existing PostgreSQL

**1. Configure the monitoring user**

As a PostgreSQL administrator, create a monitoring user if you do not already have one, and grant access to statistics:

```sql theme={null}
CREATE USER otel_monitor WITH PASSWORD 'REPLACE_WITH_A_STRONG_PASSWORD';
GRANT pg_monitor TO otel_monitor;
GRANT CONNECT ON DATABASE postgres TO otel_monitor;
```

Grant `CONNECT` on each database you want to monitor. Ensure PostgreSQL accepts connections from the collector through its network and `pg_hba.conf` settings.

Set the database connection variables in the same terminal as your OTLP connection variables:

```bash theme={null}
export POSTGRES_ENDPOINT='YOUR_POSTGRES_HOST:5432'
export POSTGRES_USERNAME='otel_monitor'
export POSTGRES_PASSWORD='YOUR_MONITORING_PASSWORD'
```

For PostgreSQL on the Docker host, use `host.docker.internal:5432`. For containers on a shared Docker network, use the database container hostname and add `--network YOUR_NETWORK` to the collector command.

**2. Configure the collector**

Save this as `postgres-metrics.yaml`. Replace the `databases` list with your database names:

```yaml theme={null}
receivers:
  postgresql:
    endpoint: ${env:POSTGRES_ENDPOINT}
    transport: tcp
    username: ${env:POSTGRES_USERNAME}
    password: ${env:POSTGRES_PASSWORD}
    databases:
      - postgres
    collection_interval: 30s
    tls:
      insecure: false
      insecure_skip_verify: false
processors:
  resource/postgres:
    attributes:
      - key: service.name
        value: postgresql-service
        action: upsert
  batch:
    timeout: 5s
    send_batch_size: 500
    send_batch_max_size: 500
exporters:
  otlp_http/observability:
    endpoint: ${env:OTEL_EXPORTER_OTLP_ENDPOINT}
    headers:
      authorization: ${env:OTEL_EXPORTER_OTLP_AUTHORIZATION}
service:
  pipelines:
    metrics/postgres:
      receivers: [postgresql]
      processors: [resource/postgres, batch]
      exporters: [otlp_http/observability]
```

This configuration enables TLS and verifies the server certificate. For a private CA, set `tls.ca_file` and mount the CA file into the collector. For a local PostgreSQL server without TLS, set `tls.insecure: true`; this disables TLS for the database connection. See the [PostgreSQL receiver configuration](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/v0.161.0/receiver/postgresqlreceiver#configuration).

**3. Run the collector**

```bash theme={null}
docker run --rm --name postgres-metrics-collector \
  --add-host=host.docker.internal:host-gateway \
  --memory=512m \
  -e POSTGRES_ENDPOINT \
  -e POSTGRES_USERNAME \
  -e POSTGRES_PASSWORD \
  -e OTEL_EXPORTER_OTLP_ENDPOINT \
  -e OTEL_EXPORTER_OTLP_AUTHORIZATION \
  -v "$(pwd)/postgres-metrics.yaml:/etc/otelcol-contrib/config.yaml:ro" \
  otel/opentelemetry-collector-contrib:0.161.0 \
  --config=/etc/otelcol-contrib/config.yaml
```

Leave the terminal open and allow at least two collection intervals. Stop the collector with **Ctrl+C** when finished testing.

**4. Verify metrics**

Open **Chart Explorer**, select your **Metrics** source, filter for `ServiceName:postgresql-service`, and select **Last 15 minutes**. Look for `postgresql.backends`, `postgresql.commits`, or `postgresql.db_size`.

## Demo dataset

The sample contains 8,928 gauge and sum points sampled every five minutes across 24 hours. It preserves the original dataset's connection spikes, cache activity, rollbacks, and deadlocks. The sample includes database-level metrics; live collection also provides table and index metrics.

Set the [connection variables](#connection-details) before starting.

**1. Download the sample metrics**

[Download the sample bundle](https://clickhouse.com/docs/assets/postgres-metrics-demo.zip) (about 64 KB) and extract `postgres-metrics-demo.json` into your working directory.

**2. Configure the demo collector**

Save this as `postgres-metrics-demo.yaml`:

```yaml theme={null}
receivers:
  otlp:
    protocols:
      http:
        endpoint: 0.0.0.0:4318
processors:
  resource/demo:
    attributes:
      - key: service.name
        value: postgresql-demo
        action: upsert
  transform/demo_timestamps:
    error_mode: propagate
    metric_statements:
      - context: datapoint
        statements:
          - set(cache["interval_ns"], time_unix_nano - start_time_unix_nano)
          - set(time, Now() - Duration("2m") - (Time("2025-11-11T05:55:00Z", "%Y-%m-%dT%H:%M:%SZ") - time))
          - set(start_time_unix_nano, time_unix_nano - cache["interval_ns"])
  batch:
    timeout: 5s
    send_batch_size: 500
    send_batch_max_size: 500
exporters:
  otlp_http/managed:
    endpoint: ${env:OTEL_EXPORTER_OTLP_ENDPOINT}
    headers:
      authorization: ${env:OTEL_EXPORTER_OTLP_AUTHORIZATION}
service:
  pipelines:
    metrics:
      receivers: [otlp]
      processors: [resource/demo, transform/demo_timestamps, batch]
      exporters: [otlp_http/managed]
```

> [!NOTE]
>
> The demo-only transform refreshes timestamps while preserving metric values and the interval between each point's start and end timestamps. Omit it for live PostgreSQL metrics. The batch limit keeps uploads small enough for the destination collector.

**3. Run the demo collector**

From the directory containing the configuration, run the collector and leave this terminal open:

```bash theme={null}
docker run --rm --name postgres-metrics-demo-collector \
  --add-host=host.docker.internal:host-gateway \
  --memory=512m \
  -p 127.0.0.1:14318:4318 \
  -e OTEL_EXPORTER_OTLP_ENDPOINT \
  -e OTEL_EXPORTER_OTLP_AUTHORIZATION \
  -v "$(pwd)/postgres-metrics-demo.yaml:/etc/otelcol-contrib/config.yaml:ro" \
  otel/opentelemetry-collector-contrib:0.161.0 \
  --config=/etc/otelcol-contrib/config.yaml
```

**4. Send the sample**

In another terminal, run this command from the download directory:

```bash theme={null}
curl --fail-with-body -sS http://localhost:14318/v1/metrics \
  -H 'Content-Type: application/json' \
  --data-binary @postgres-metrics-demo.json
```

A response such as `{}` or `{"partialSuccess":{}}` indicates the local collector accepted the request. Check the collector terminal for export errors and allow a few seconds for forwarding. Stop it with **Ctrl+C** after verifying the data. Sending the sample again can produce duplicate points.

**5. Verify the metrics**

Import the dashboard in the [next section](#dashboards), filter for `ServiceName:postgresql-demo`, and select **Last 2 days**. The sample includes both gauge and sum metrics; no direct database access is needed.

## Dashboards and visualization

**1. [Download the dashboard configuration](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/examples/postgres-metrics-dashboard.json)**

**2. Import the dashboard**

1. Open **Dashboards** and click **Import** in the upper-right corner.
2. Upload `postgres-metrics-dashboard.json`.
3. Map the charts to your **Metrics** source and click **Finish Import**.

![Import dashboard button](https://mintcdn.com/private-7c7dfe99/dW6bcEakZdn3a5oP/images/clickstack/observability-import-dashboard.png?fit=max\&auto=format\&n=dW6bcEakZdn3a5oP\&q=85\&s=16bcb7e6365ca0f6decac5c693e228eb)

**3. View the dashboard**

For the sample, optionally filter for `ServiceName:postgresql-demo` and select **Last 2 days**. For your own metrics, use `ServiceName:postgresql-service` and **Last 15 minutes**.

![PostgreSQL metrics dashboard showing active connections, transactions, rollbacks, database size, row operations, and disk blocks read](https://mintcdn.com/private-7c7dfe99/dW6bcEakZdn3a5oP/images/clickstack/observability-postgres-metrics-dashboard.png?fit=max\&auto=format\&n=dW6bcEakZdn3a5oP\&q=85\&s=61769458c85d3274c5434dc3d8c60384)

## Troubleshooting

### Collector fails to start

Check the collector output for configuration errors. Confirm the configuration file is mounted and the connection variables are set in the terminal where Docker runs.

### No metrics appearing

Check the collector output for database connection or export errors. Confirm the database hostname is reachable from the container, the database names are correct, and PostgreSQL allows the monitoring user's connection. Check your **Metrics** source, service-name filter, and time range.

### Authentication or TLS errors

Confirm the monitoring user has `pg_monitor` and `CONNECT` permissions. Test the database credentials with `psql` from a host that can reach PostgreSQL:

```bash theme={null}
psql -h YOUR_POSTGRES_HOST -U otel_monitor -d postgres -W -c 'SELECT 1;'
```

Match the receiver's TLS settings to your server and mount any required CA certificate. For export errors, check the OTLP endpoint and authorization against your service's connection details.

## Next steps

* Set up [alerts](https://clickhouse.com/docs/clickstack/observability/features/alerts.md) for connection limits, rollback rates, or cache performance.
* Collect [PostgreSQL logs](https://clickhouse.com/docs/clickstack/observability/integration-examples/postgres-logs.md) alongside your metrics.
* See [Sending OpenTelemetry data](https://clickhouse.com/docs/clickstack/observability/ingesting-data/opentelemetry.md) for collector deployment guidance.