Troubleshooting

​

Extension won’t load

WARNING:  pg_stat_ch must be loaded via shared_preload_libraries

shared_preload_libraries = 'pg_stat_ch'

sudo systemctl restart postgresql

​

Background worker not running

SELECT pid, application_name, state, wait_event
FROM pg_stat_activity
WHERE application_name = 'pg_stat_ch exporter';

• Verify shared_preload_libraries includes pg_stat_ch
• Check PostgreSQL logs for startup errors
• The worker restarts automatically after 10 seconds if it crashes — check logs for repeated crash/restart cycles

​

Events not appearing in ClickHouse

SHOW pg_stat_ch.enabled;

ALTER SYSTEM SET pg_stat_ch.enabled = on;
SELECT pg_reload_conf();

SELECT
    enqueued_events,
    exported_events,
    dropped_events,
    send_failures,
    last_error_text,
    last_error_ts
FROM pg_stat_ch_stats();

SHOW pg_stat_ch.clickhouse_host;
SHOW pg_stat_ch.clickhouse_port;
SHOW pg_stat_ch.clickhouse_database;

clickhouse-client -h <host> -p <port> -q "SELECT 1"

-- In ClickHouse
SHOW TABLES FROM pg_stat_ch;

clickhouse-client < docker/init/00-schema.sql

grep -i "pg_stat_ch" /var/log/postgresql/postgresql-*.log | tail -20

​

High queue usage

1. Decrease flush interval — export more frequently:
   ALTER SYSTEM SET pg_stat_ch.flush_interval_ms = 100;
   SELECT pg_reload_conf();
   
2. Increase batch size — drain more events per cycle:
   ALTER SYSTEM SET pg_stat_ch.batch_max = 500000;
   SELECT pg_reload_conf();
   
3. Check ClickHouse health — slow inserts cause backpressure:
   -- In ClickHouse: check for merge backlog
   SELECT table, count() AS parts
   FROM system.parts
   WHERE database = 'pg_stat_ch' AND active
   GROUP BY table;
   
4. Increase queue capacity (requires restart):
   pg_stat_ch.queue_capacity = 262144  -- must be power of 2
   

​

Dropped events

SELECT dropped_events FROM pg_stat_ch_stats();

• ClickHouse is down or unreachable (events queue up with nowhere to go)
• A burst of queries exceeded the queue capacity
• The flush interval is too long for the workload

SELECT pg_stat_ch_reset();

​

DSA String Area Full

SELECT dsa_oom_count FROM pg_stat_ch_stats();

​

Missing I/O timing data

SHOW track_io_timing;

track_io_timing = on

​

Missing query_id values

SHOW compute_query_id;

compute_query_id = on

​

TLS connection errors

1. Verify pg_stat_ch.clickhouse_use_tls = on is set
2. For self-signed certificates in testing, set pg_stat_ch.clickhouse_skip_tls_verify = on
3. In production, ensure the ClickHouse server certificate is trusted by the system CA store

​

Extension health check query

SELECT
    pg_stat_ch_version() AS version,
    s.enqueued_events,
    s.exported_events,
    s.dropped_events,
    s.send_failures,
    round(s.queue_usage_pct, 1) AS queue_pct,
    s.last_error_text,
    CASE
        WHEN s.send_failures > 0 THEN 'EXPORT ERRORS'
        WHEN s.dropped_events > 0 THEN 'DROPS DETECTED'
        WHEN s.queue_usage_pct > 80 THEN 'QUEUE PRESSURE'
        WHEN s.enqueued_events = 0 THEN 'NO EVENTS'
        ELSE 'OK'
    END AS status
FROM pg_stat_ch_stats() s;