feat(cli): ClickHouse Cloud host metrics scrape

Nikolay Samokhvalovrequested to merge
feature/clickhouse-host-metrics into feature/clickhouse-managed-postgres

Stacked on !434 (closed).

What

ClickHouse Managed Postgres host metrics (CPU by mode including iowait and steal, memory, disk, I/O, network, connections) come from the ClickHouse Cloud Prometheus endpoint. No agent to install.

  • postgresai mon targets add <conn> <name> recognises a *.pg.clickhouse.cloud host. With CLICKHOUSE_ORG_ID, CLICKHOUSE_KEY_ID and CLICKHOUSE_KEY_SECRET in the environment (never in argv), it finds the service by hostname through the Cloud API and writes host-metrics/clickhouse-<name>.yml. That file is a VictoriaMetrics scrape job for /v1/organizations/{org}/postgres/{id}/prometheus with basic auth from a password_file, scraped every 60 s and labelled cluster / node_name like the Postgres target. Only PostgresServiceInfo|PostgresServer_* is kept. The secret goes into clickhouse-<name>.secret with mode 0600. Then it sends SIGHUP to sink-prometheus.
  • Specific failures, each exit 1 with nothing written. The Postgres target is still added, and the command says so:
    • ClickHouse Cloud rejected the API key (401). Check the key id and secret.
    • The API key cannot read Postgres services in organization <org> (403). Give it read access to this organization.
    • No ClickHouse Managed Postgres service in organization <org> has hostname <host>.
    • ClickHouse Managed Postgres service is <state>, not running. Start it in the ClickHouse Cloud console, then retry.
  • Without the env vars, it prints how to enable host metrics and adds the Postgres target as before.
  • mon targets remove deletes both files and reloads.
  • Stack: prometheus.yml gets scrape_config_files: ['/etc/pgai/host-metrics/*.yml'], and sink-prometheus mounts ./host-metrics read-only. The CLI creates the directory before compose runs, so Docker never creates it root-owned. An empty glob is harmless; we checked that against VictoriaMetrics v1.140.0.

Tests

  • clickhouse.test.ts: service lookup against a stubbed Cloud API (the only fake), exact error texts, the scrape-config golden, and rejection of ids and names that could inject YAML or a path.
  • clickhouse-targets.test.ts: add/remove on a temp project dir. It checks file modes, that the secret is never in the config or in output, and that nothing is written on any error.
  • clickhouse-stack.test.ts: the prometheus.yml and compose contract.
  • clickhouse-scrape.integration.test.ts and the CI job cli:clickhouse-scrape:tests run a real VictoriaMetrics v1.140.0 binary. It scrapes a stub serving fixtures/clickhouse-prometheus.txt, which lists every series in the ClickHouse metrics reference. Assertions: 8 CPU-mode series with our labels, up=1 for the right key, up=0 and no data for a wrong key, and a non-kept metric dropped.
  • clickhouse-targets-retry.test.ts: mon targets add is safe to re-run. Missing credentials, a 401, or a stopped service can be fixed and retried. A different URL under the same name still fails. postgres:// is accepted. channel_binding is dropped from the URL given to pgwatch, with a note on stderr. Host-metrics labels follow the target's existing cluster/node_name. collector-conn-str / local-install-collector tests cover the same normalisation in mon local-install.
  • clickhouse.cloud.test.ts: the real-cloud run, skipped without keys. Passed on 2026-09-28: r6gd.medium PG17 us-east-1, created and deleted in about 7 min. The live endpoint serves exactly the documented series (fixtures/clickhouse-prometheus.txt).
  • Full-stack acceptance on real ClickHouse Cloud, 2026-09-28. We ran the monitoring stack locally from this branch against one r6gd.medium PG17 service (07:52–08:16 UTC). The service was deleted afterwards: DELETE 200, then GET 404, and the org list is empty.
    • 16 min of collection. All 21 PostgresServer_* families arrived labelled cluster=default, node_name=ch-e2e, with 8 CPU modes including iowait/steal and up=1 on all 15 scrapes. pgwatch stored 213 metric names; wait events come from pg_stat_activity.
    • The reporter built 25 checks from monitoring data with no errors. D004 reports kcache unavailable without error, and K001–K008 and N001 have data.
    • The run found two bugs, fixed here red/green: mon targets add rejected postgres://, and pgwatch failed with FATAL: unrecognized configuration parameter "channel_binding".
    • Dashboards 01–14: of 242 targets, 8 return nothing for every provider (stale relkind 105/114, an old max_connections metric name, a PG17-incompatible bgwriter target). None needs pg_stat_kcache or pg_wait_sampling. These are a separate topic.
  • Red first, then green, for each change: 29fdd01/69b1d96 → 56138aa; retry 0233d96 → c252554; scheme 01ad007 → 0c333c1; channel_binding 5ee443a → 323bd00, d66ba3d → e4989bc; labels/stderr bfa1d36 → 1239893.

Not in this MR

  • A host_* recording layer and Dashboard 01 host panels. These are shared with Supabase and RDS host stats (lane H), so the ClickHouse series are stored as-is for those rules to map.
  • Console onboarding (paste connection string and API key, encrypted key storage, target registration through the job channel).

Security note

We have not yet confirmed whether a read-only ClickHouse Cloud key can read the Prometheus endpoint (open question 1 in the plan). A key with a broader role can manage services in the org. It lives only in a 0600 file on that box, read by VictoriaMetrics through password_file. It is never in argv, logs, instances.yml or the scrape config.

Relates to https://gitlab.com/postgres-ai/internal/-/issues/354

Round 2 (2026-09-30)

  • Merged !434 (closed) (c7addfb), which brings in the table_describe search_path hardening. There is no other code change on this branch.
  • The review defects listed for this MR are fixed red/green in the stacked !440 (closed): mon local-install --db-url now sets up host metrics, the key directory is 0700, the read-only mkdir no longer throws, the key role is documented (Basic Service API Reader, verified against the real API), and remove and a failed add now behave correctly on a stopped stack. !440 (closed) is rebuilt on this head and merges cleanly.
  • Real ClickHouse Cloud run through mon local-install on 2026-09-30: see !440 (closed).

What we did not check

  • A dashboard panel for PostgresServer_*. The series are stored with unchanged names and labels, and another change owns the host dashboard row.

🤖 Generated with Claude Code

Round 3 (review of 2026-09-29, round 2)

  • Deleted cli/test/local-install-collector.test.ts (it never ran local-install; collector-conn-str.test.ts covers it) and the two clickhouse-stack.test.ts cases that restated config strings.
  • defaultTargetName replaces the three copies of the connection URL regex.
  • removeHostMetrics returns whether files existed, instead of the caller repeating its extension list, and is a no-op for a name no host metrics file can have (an independent security review found that targets remove prod.db failed after deleting the target).
  • splitChannelBinding (from !434 (closed)) replaces collectorConnStr.
  • Red 3f822a8, green 122d51b; the rest are refactors under the existing tests. ClickHouse CLI tests: 122 pass (real VictoriaMetrics test included when PGAI_TEST_VM_BIN is set).
  • Own delta: code +237/−54 (was +246/−43), tests +670 (was +709).

🤖 Generated with Claude Code

https://claude.ai/code/session_01GRNbWXUQJebE3vqWjAgTFh

Edited by Nikolay Samokhvalov

Merge request reports