Monitoring PostgreSQL Logs with ClickStack

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/clickstack/ingesting-data/integration-examples/postgres-logs.md)

TL;DR

Collect and visualize PostgreSQL server logs (CSV format) in ClickStack using the OTel `filelog` receiver. Includes a demo dataset and pre-built dashboard.

## Integration with existing PostgreSQL[​](#existing-postgres "Direct link to Integration with existing PostgreSQL")

This section covers configuring your existing PostgreSQL installation to send logs to ClickStack by modifying the ClickStack OTel collector configuration.

If you would like to test the PostgreSQL logs integration before configuring your own existing setup, you can test with our preconfigured setup and sample data in the ["Demo dataset"](/docs/use-cases/observability/clickstack/integrations/postgresql-logs#demo-dataset) section.

##### Prerequisites[​](#prerequisites "Direct link to Prerequisites")

- ClickStack instance running
- Existing PostgreSQL installation (version 9.6 or newer)
- Access to modify PostgreSQL configuration files
- Sufficient disk space for log files

Configure PostgreSQL logging

#### Configure PostgreSQL logging[​](#configure-postgres "Direct link to Configure PostgreSQL logging")

PostgreSQL supports multiple log formats. For structured parsing with OpenTelemetry, we recommend CSV format which provides consistent, parseable output.

The `postgresql.conf` file is typically located at:

- **Linux (apt/yum)**: `/etc/postgresql/{version}/main/postgresql.conf`
- **macOS (Homebrew)**: `/usr/local/var/postgres/postgresql.conf` or `/opt/homebrew/var/postgres/postgresql.conf`
- **Docker**: Configuration is usually set via environment variables or mounted config file

Add or modify these settings in `postgresql.conf`:

```
# Required for CSV logging
logging_collector = on
log_destination = 'csvlog'

# Recommended: Connection logging
log_connections = on
log_disconnections = on

# Optional: Tune based on your monitoring needs
#log_min_duration_statement = 1000  # Log queries taking more than 1 second
#log_statement = 'ddl'               # Log DDL statements (CREATE, ALTER, DROP)
#log_checkpoints = on                # Log checkpoint activity
#log_lock_waits = on                 # Log lock contention
```

Note

This guide uses PostgreSQL's `csvlog` format for reliable structured parsing. If you're using `stderr` or `jsonlog` formats, you'll need to adjust the OpenTelemetry collector configuration accordingly.

After making these changes, restart PostgreSQL:

```
# For systemd
sudo systemctl restart postgresql

# For Docker
docker restart
```

Verify logs are being written:

```
# Default log location on Linux
tail -f /var/lib/postgresql/{version}/main/log/postgresql-*.log

# macOS Homebrew
tail -f /usr/local/var/postgres/log/postgresql-*.log
```

Create custom OTel collector configuration

#### Create custom OTel collector configuration[​](#custom-otel "Direct link to 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. The custom configuration is merged with the base configuration managed by HyperDX via OpAMP.

Create a file named `postgres-logs-monitoring.yaml` with the following configuration:

```
receivers:
  filelog/postgres:
    include:
      - /var/lib/postgresql/*/main/log/postgresql-*.csv # Adjust to match your PostgreSQL installation
    start_at: end
    multiline:
      line_start_pattern: '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'
    operators:
      - type: csv_parser
        parse_from: body
        parse_to: attributes
        header: 'log_time,user_name,database_name,process_id,connection_from,session_id,session_line_num,command_tag,session_start_time,virtual_transaction_id,transaction_id,error_severity,sql_state_code,message,detail,hint,internal_query,internal_query_pos,context,query,query_pos,location,application_name,backend_type,leader_pid,query_id'
        lazy_quotes: true
        
      - type: time_parser
        parse_from: attributes.log_time
        layout: '%Y-%m-%d %H:%M:%S.%L %Z'
      
      - type: add
        field: attributes.source
        value: "postgresql"
      
      - type: add
        field: resource["service.name"]
        value: "postgresql-production"

service:
  pipelines:
    logs/postgres:
      receivers: [filelog/postgres]
      processors:
        - memory_limiter
        - transform
        - batch
      exporters:
        - clickhouse
```

This configuration:

- Reads PostgreSQL CSV logs from their standard location
- Handles multi-line log entries (errors often span multiple lines)
- Parses CSV format with all standard PostgreSQL log fields
- Extracts timestamps to preserve original log timing
- Adds `source: postgresql` attribute for filtering in HyperDX
- Routes logs to the ClickHouse exporter via a dedicated pipeline

Note

- You only define new receivers and pipelines in the custom config
- The processors (`memory_limiter`, `transform`, `batch`) and exporters (`clickhouse`) are already defined in the base ClickStack configuration - you just reference them by name
- The `csv_parser` operator extracts all standard PostgreSQL CSV log fields into structured attributes
- This configuration uses `start_at: end` to avoid re-ingesting logs on collector restarts. For testing, change to `start_at: beginning` to see historical logs immediately.
- Adjust the `include` path to match your PostgreSQL log directory location

Configure ClickStack to load custom configuration

#### Configure ClickStack to load custom configuration[​](#load-custom "Direct link to Configure ClickStack to load custom configuration")

To enable custom collector configuration in your existing ClickStack deployment, you must:

1. Mount the custom config file at `/etc/otelcol-contrib/custom.config.yaml`
2. Set the environment variable `CUSTOM_OTELCOL_CONFIG_FILE=/etc/otelcol-contrib/custom.config.yaml`
3. Mount your PostgreSQL log directory so the collector can read them

##### Option 1: Docker Compose[​](#docker-compose "Direct link to Option 1: Docker Compose")

Update your ClickStack deployment configuration:

```
services:
  clickstack:
    # ... existing configuration ...
    environment:
      - CUSTOM_OTELCOL_CONFIG_FILE=/etc/otelcol-contrib/custom.config.yaml
      # ... other environment variables ...
    volumes:
      - ./postgres-logs-monitoring.yaml:/etc/otelcol-contrib/custom.config.yaml:ro
      - /var/lib/postgresql:/var/lib/postgresql:ro
      # ... other volumes ...
```

##### Option 2: Docker Run (All-in-One Image)[​](#all-in-one "Direct link to Option 2: Docker Run (All-in-One Image)")

If you're using the all-in-one image with docker run:

```
docker run --name clickstack \
  -p 8080:8080 -p 4317:4317 -p 4318:4318 \
  -e CUSTOM_OTELCOL_CONFIG_FILE=/etc/otelcol-contrib/custom.config.yaml \
  -v "$(pwd)/postgres-logs-monitoring.yaml:/etc/otelcol-contrib/custom.config.yaml:ro" \
  -v /var/lib/postgresql:/var/lib/postgresql:ro \
  clickhouse/clickstack-all-in-one:latest
```

Note

Ensure the ClickStack collector has appropriate permissions to read the PostgreSQL log files. In production, use read-only mounts (`:ro`) and follow the principle of least privilege.

Verifying Logs in HyperDX

#### Verifying Logs in HyperDX[​](#verifying-logs "Direct link to Verifying Logs in HyperDX")

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

1. Navigate to the search view
2. Set source to Logs
3. Filter by `source:postgresql` to see PostgreSQL-specific logs
4. You should see structured log entries with fields like `user_name`, `database_name`, `error_severity`, `message`, `query`, etc.

## Demo dataset[​](#demo-dataset "Direct link to Demo dataset")

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

Download the sample dataset

#### Download the sample dataset[​](#download-sample "Direct link to Download the sample dataset")

Download the sample log file: