Monitoring PostgreSQL metrics with ClickStack

TL;DRMonitor PostgreSQL performance metrics in ClickStack using the OTel PostgreSQL receiver. Includes a demo dataset and pre-built dashboard.

Integration with existing PostgreSQL

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.

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

Ensure monitoring user has required permissions

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

GRANT pg_monitor TO your_monitoring_user;

Create custom OTel collector configuration

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:

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]

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

Deploy ClickStack with custom configuration

Mount your custom configuration:

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

Verify metrics collection

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

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

Once metrics are flowing, proceed to the Dashboards and visualization section to import the pre-built dashboard.

Demo dataset

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.

Database-level metrics onlyThis 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.

Download the sample metrics dataset

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

# 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

Start ClickStack

Start a ClickStack instance:

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.

Load metrics into ClickStack

Load the metrics directly into ClickHouse:

# 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"

Verify metrics in HyperDX

Once loaded, the quickest way to see your metrics is through the pre-built dashboard.Proceed to the Dashboards and visualization section to import the dashboard and view many PostgreSQL metrics at once.

Timezone DisplayHyperDX 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.

Dashboards and visualization

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

the dashboard configuration

Import the pre-built dashboard

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

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

View the dashboard

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

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.

Troubleshooting

Custom config not loading

Verify the environment variable is set:

docker exec <container-name> printenv CUSTOM_OTELCOL_CONFIG_FILE

Check the custom config file is mounted:

docker exec <container-name> cat /etc/otelcol-contrib/custom.config.yaml

No metrics appearing in HyperDX

Verify PostgreSQL is accessible:

docker exec <clickstack-container> psql -h postgres-host -U otel_monitor -d postgres -c "SELECT 1"

Check OTel collector logs:

docker exec <container> cat /etc/otel/supervisor-data/agent.log | grep -i postgres

Authentication errors

Verify password is set correctly:

docker exec <clickstack-container> printenv POSTGRES_PASSWORD

Test credentials directly:

psql -h postgres-host -U otel_monitor -d postgres -c "SELECT version();"

Next steps

Set up 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

Going to production

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 for production configuration.

​Integration with existing PostgreSQL

​Ensure monitoring user has required permissions

​Create custom OTel collector configuration

​Deploy ClickStack with custom configuration

​Verify metrics collection

​Demo dataset

​Download the sample metrics dataset

​Start ClickStack

​Load metrics into ClickStack

​Verify metrics in HyperDX

​Dashboards and visualization

​ the dashboard configuration

​Import the pre-built dashboard

​View the dashboard

​Troubleshooting

​Custom config not loading

​No metrics appearing in HyperDX

​Authentication errors

​Next steps

​Going to production

Integration with existing PostgreSQL

Ensure monitoring user has required permissions

Create custom OTel collector configuration

Deploy ClickStack with custom configuration

Verify metrics collection

Demo dataset

Download the sample metrics dataset

Start ClickStack

Load metrics into ClickStack

Verify metrics in HyperDX

Dashboards and visualization

the dashboard configuration

Import the pre-built dashboard

View the dashboard

Troubleshooting

Custom config not loading

No metrics appearing in HyperDX

Authentication errors

Next steps

Going to production