> ## 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 logs with ClickHouse Observability

> Monitoring PostgreSQL logs with ClickHouse Observability setup and configuration for ClickHouse Observability and self-managed deployments.

Collect PostgreSQL CSV logs with an OpenTelemetry collector 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 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

> [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/logs` 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 PostgreSQL logging**

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

```sql theme={null}
SHOW config_file;
```

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

```conf theme={null}
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:

```bash theme={null}
sudo systemctl restart postgresql
# Or, for Docker:
docker restart <postgres-container>
```

Check the active CSV file:

```sql theme={null}
SELECT pg_current_logfile('csvlog');
```

The parser below expects the [26-column PostgreSQL CSV format](https://www.postgresql.org/docs/14/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG). Earlier PostgreSQL versions and other log formats require a different parser.

**2. Configure the collector**

Save this as `postgres-logs-monitoring.yaml`:

```yaml theme={null}
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.

**3. 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`.

```bash theme={null}
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:

```sql theme={null}
SELECT 1 / 0;
```

**4. 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.

**1. Download the sample dataset**

```bash theme={null}
curl -fLO https://datasets-documentation.s3.eu-west-3.amazonaws.com/clickstack-integrations/postgres/postgresql.log
```

**2. Configure the demo collector**

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

```yaml theme={null}
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.

**3. Run the demo collector**

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

```bash theme={null}
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.

**4. 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`.

![PostgreSQL demo logs in ClickHouse Observability Search](https://mintcdn.com/private-7c7dfe99/dW6bcEakZdn3a5oP/images/clickstack/observability-postgres-demo-logs.png?fit=max\&auto=format\&n=dW6bcEakZdn3a5oP\&q=85\&s=d8a3a1eb1aff81f664e2fa4692d85712)

## Dashboards and visualization

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

**2. Import the dashboard**

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

![Dashboards page with the Import button highlighted](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**

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.

![PostgreSQL logs dashboard showing log volume, slow queries, databases, errors, authentication failures, and query types](https://mintcdn.com/private-7c7dfe99/dW6bcEakZdn3a5oP/images/clickstack/observability-postgres-logs-dashboard.png?fit=max\&auto=format\&n=dW6bcEakZdn3a5oP\&q=85\&s=4fb1d3bece42561fc313c162f3da5981)

## 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](https://clickhouse.com/docs/clickstack/observability/features/alerts.md) for errors and connection failures.
* Correlate logs with [PostgreSQL metrics](https://clickhouse.com/docs/clickstack/observability/integration-examples/postgres-metrics.md).