Monitoring PostgreSQL logs with ClickHouse Observability

Collect PostgreSQL CSV logs with an OpenTelemetry collector and send them to ClickHouse Observability. Use the demo dataset to try the integration without installing PostgreSQL.

Prerequisites

A ClickHouse Observability service
Docker to run the collector examples
For your own logs, PostgreSQL 14 or newer and access to its configuration and log files
For the demo, curl to download the sample

Connection details

Fully managed
Self-managed

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.For the OTLP exporter examples below, set these variables in the terminal where you will run the collector:

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/logs suffix; the exporter appends the signal path.

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 for setup guidance.

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

Configure PostgreSQL logging

Find your configuration file by running this SQL as a database administrator:

SHOW config_file;

Add or update these settings in postgresql.conf:

logging_collector = on
log_destination = 'csvlog'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0640
log_timezone = 'UTC'
log_connections = on
log_disconnections = on
# Optional: log statements taking more than one second
# log_min_duration_statement = 1000

Create the log directory with ownership and permissions that let PostgreSQL write and the collector read it. For containerized PostgreSQL, mount that directory so the collector can access the same files. CSV output uses a .csv extension even though log_filename ends in .log.Restart PostgreSQL using your deployment process, for example:

sudo systemctl restart postgresql
# Or, for Docker:
docker restart <postgres-container>

Check the active CSV file:

SELECT pg_current_logfile('csvlog');

The parser below expects the 26-column PostgreSQL CSV format. Earlier PostgreSQL versions and other log formats require a different parser.

Configure the collector

Save this as postgres-logs-monitoring.yaml:

receivers:
  filelog/postgres:
    include:
      - /var/log/postgresql/postgresql-*.csv
    start_at: end
    force_flush_period: 1s
    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'
        location: UTC

      - type: add
        field: attributes.source
        value: "postgresql"

      - type: add
        field: resource["service.name"]
        value: "postgresql-service"

processors:
  memory_limiter:
    check_interval: 1s
    limit_mib: 400
    spike_limit_mib: 100
  transform/postgres:
    error_mode: ignore
    log_statements:
      - context: log
        statements:
          - set(log.severity_text, "info")
          - set(log.severity_number, SEVERITY_NUMBER_INFO)
          - set(log.severity_text, "debug") where IsMatch(log.attributes["error_severity"], "^DEBUG")
          - set(log.severity_number, SEVERITY_NUMBER_DEBUG) where log.severity_text == "debug"
          - set(log.severity_text, "warn") where log.attributes["error_severity"] == "WARNING"
          - set(log.severity_number, SEVERITY_NUMBER_WARN) where log.severity_text == "warn"
          - set(log.severity_text, "error") where log.attributes["error_severity"] == "ERROR"
          - set(log.severity_number, SEVERITY_NUMBER_ERROR) where log.severity_text == "error"
          - set(log.severity_text, "fatal") where log.attributes["error_severity"] == "FATAL" or log.attributes["error_severity"] == "PANIC"
          - set(log.severity_number, SEVERITY_NUMBER_FATAL) where log.severity_text == "fatal"
  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:
    logs/postgres:
      receivers: [filelog/postgres]
      processors: [memory_limiter, transform/postgres, batch]
      exporters: [otlp_http/observability]

The receiver parses multiline CSV entries, preserves their timestamps and bodies, and adds service.name as postgresql-service. Fields such as database_name, error_severity, message, and query remain available as log attributes.

Run the collector

From the directory containing the configuration, run the following command. Adjust the host log directory if needed. With group-readable log files, add --group-add LOG_GROUP_ID, using the numeric group ID shown by ls -ln /var/log/postgresql.

docker run --rm --name postgres-logs-collector \
  --add-host=host.docker.internal:host-gateway \
  --memory=512m \
  -e OTEL_EXPORTER_OTLP_ENDPOINT \
  -e OTEL_EXPORTER_OTLP_AUTHORIZATION \
  -v "$(pwd)/postgres-logs-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro" \
  -v /var/log/postgresql:/var/log/postgresql:ro \
  otel/opentelemetry-collector-contrib:0.161.0 \
  --config=/etc/otelcol-contrib/config.yaml

Leave the terminal open. The receiver uses start_at: end, so create a new database connection or execute a query that produces an error after starting the collector:

SELECT 1 / 0;

Verify logs

Open Search, select Logs, filter for ServiceName:postgresql-service, and select Last 15 minutes. Expand a record to inspect its PostgreSQL attributes and severity.

Demo dataset

The sample contains 21,325 PostgreSQL CSV records covering connection activity, queries, and server errors.

Download the sample dataset

curl -fLO https://datasets-documentation.s3.eu-west-3.amazonaws.com/clickstack-integrations/postgres/postgresql.log

Configure the demo collector

Save this as postgres-logs-demo.yaml:

receivers:
  filelog/postgres:
    include:
      - /var/log/postgresql/postgresql.log
    start_at: beginning
    force_flush_period: 1s
    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'
        location: UTC

      - type: add
        field: attributes.source
        value: "postgresql-demo"

      - type: add
        field: resource["service.name"]
        value: "postgresql-demo"

processors:
  memory_limiter:
    check_interval: 1s
    limit_mib: 400
    spike_limit_mib: 100
  transform/postgres:
    error_mode: ignore
    log_statements:
      - context: log
        statements:
          - set(log.severity_text, "info")
          - set(log.severity_number, SEVERITY_NUMBER_INFO)
          - set(log.severity_text, "debug") where IsMatch(log.attributes["error_severity"], "^DEBUG")
          - set(log.severity_number, SEVERITY_NUMBER_DEBUG) where log.severity_text == "debug"
          - set(log.severity_text, "warn") where log.attributes["error_severity"] == "WARNING"
          - set(log.severity_number, SEVERITY_NUMBER_WARN) where log.severity_text == "warn"
          - set(log.severity_text, "error") where log.attributes["error_severity"] == "ERROR"
          - set(log.severity_number, SEVERITY_NUMBER_ERROR) where log.severity_text == "error"
          - set(log.severity_text, "fatal") where log.attributes["error_severity"] == "FATAL" or log.attributes["error_severity"] == "PANIC"
          - set(log.severity_number, SEVERITY_NUMBER_FATAL) where log.severity_text == "fatal"
  transform/demo_timestamps:
    error_mode: propagate
    log_statements:
      - context: log
        statements:
          - set(log.time, Now() - Duration("2m") - (Time("2025-11-10T23:59:47Z", "%Y-%m-%dT%H:%M:%SZ") - log.time))
  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:
    logs/postgres:
      receivers: [filelog/postgres]
      processors: [memory_limiter, transform/postgres, transform/demo_timestamps, batch]
      exporters: [otlp_http/observability]

The demo-only timestamp processor shifts the sample into the past 24 hours, ending two minutes before ingestion. Original timestamp attributes and log bodies stay unchanged. Omit this processor for your own PostgreSQL logs.

Run the demo collector

From the directory containing the configuration and downloaded log file, run:

docker run --rm --name postgres-demo-collector \
  --add-host=host.docker.internal:host-gateway \
  --memory=512m \
  -e OTEL_EXPORTER_OTLP_ENDPOINT \
  -e OTEL_EXPORTER_OTLP_AUTHORIZATION \
  -v "$(pwd)/postgres-logs-demo.yaml:/etc/otelcol-contrib/config.yaml:ro" \
  -v "$(pwd)/postgresql.log:/var/log/postgresql/postgresql.log:ro" \
  otel/opentelemetry-collector-contrib:0.161.0 \
  --config=/etc/otelcol-contrib/config.yaml

Leave the terminal open while verifying ingestion. Stop the collector with Ctrl+C when finished. Running the demo again re-ingests the sample with refreshed timestamps.

Verify the demo logs

Open Search, select Logs, filter for ServiceName:postgresql-demo, and select Last 2 days. Expand a record to inspect fields such as database_name, error_severity, message, and query.

Dashboards and visualization

Import the dashboard

Open Dashboards and click Import in the upper-right corner.
Upload postgresql-logs-dashboard.json.
Map the charts to your Logs source and click Finish Import.

View the dashboard

Select Last 2 days for the sample and optionally filter for ServiceName:postgresql-demo. For your own logs, filter for ServiceName:postgresql-service and choose a time range covering your records.

Troubleshooting

Collector fails to start

Check the collector output for configuration errors. Verify the configuration mount and connection variables in the terminal where Docker runs.

No logs appear

Check for authentication or export errors. Confirm the CSV path with pg_current_logfile, ensure the files match the receiver’s include pattern, and check that the collector can read them. With start_at: end, only new entries are collected. For the demo, include the timestamp processor and select Last 2 days.

Permission denied

PostgreSQL normally creates owner-only log files. The configuration above uses log_file_mode = 0640 to allow group reads; add the file’s group to the collector container with --group-add. Existing files keep their previous permissions. Ensure the directory also permits group access.

CSV parsing errors

The parser expects 26 columns from PostgreSQL 14 or newer. Confirm log_destination = 'csvlog' and log_timezone = 'UTC'. Adjust the header for older versions or custom formats; stderr and JSON logs cannot use this CSV parser.

Next steps

Set up alerts for errors and connection failures.
Correlate logs with PostgreSQL metrics.

Introduction

Features

Administration

Guides

Integration partners

Self-managed

Monitoring PostgreSQL logs with ClickHouse Observability

Prerequisites

Connection details

Integration with existing PostgreSQL

Demo dataset

Dashboards and visualization

Troubleshooting

Collector fails to start

No logs appear

Permission denied

CSV parsing errors

Next steps

​Prerequisites

​Connection details

​Integration with existing PostgreSQL

​Demo dataset

​Dashboards and visualization

​Troubleshooting

​Collector fails to start

​No logs appear

​Permission denied

​CSV parsing errors

​Next steps

Prerequisites

Connection details

Integration with existing PostgreSQL

Demo dataset

Dashboards and visualization

Troubleshooting

Collector fails to start

No logs appear

Permission denied

CSV parsing errors

Next steps