---
title: ClickHouse Cloud and Managed Postgres
---

This guide runs Loom on Kubernetes over two managed services: ClickHouse Cloud for
projections, vectors, history and analytics, and ClickHouse Managed Postgres as the memory
authority. It covers the services, the chart's `postgres` values, the checks after the
install, rotating the Postgres password and the CA, and the SQL surface user. The chart
itself is in [Kubernetes](/pt-BR/operations/kubernetes), the authority in
[Postgres memory authority](/pt-BR/operations/postgres).

The overlay `deploy/helm/loom/values-clickhouse-cloud.yaml` layers on the EKS one. It is for
a deployment on an empty ClickHouse: the authority is chosen at a deployment's first boot,
and on a ClickHouse that already holds memory the pods refuse to start, as no migration
moves memory from the ClickHouse authority to Postgres.

## The services

- **ClickHouse Cloud**: a service with a database named `loom` and a user for Loom, its IP
  access list allowing the cluster's NAT addresses ([Kubernetes, On EKS](/pt-BR/operations/kubernetes#on-eks)).
- **ClickHouse Managed Postgres**: a service with **two HA standbys**, a database and a role
  that owns it, and the **direct** endpoint on port 5432
  ([Postgres, ClickHouse Managed Postgres](/pt-BR/operations/postgres#clickhouse-managed-postgres)). Grant
  the role `pg_read_all_stats`, so Loom can see which standbys are attached:

  ```sql
  GRANT pg_read_all_stats TO loom;
  ```

## Secrets

The overlay reads two Secrets beside `loom-secrets`: `loom-postgres`, key `password`, with
the role's password, and `loom-postgres-ca`, key `ca.crt`, with the CA bundle. Make them the
same way as `loom-secrets`, each with an ExternalSecret of its own.

`ca.crt` holds every root certificate the service's CA currently publishes, concatenated as
PEM; the driver loads the file as a bundle and trusts nothing else. Take the roots from the
CA's published list (the "CA Issuers" URL in the server certificate names the CA);
`openssl s_client -starttls postgres -showcerts` usually prints the chain without its
root. Convert a root fetched as DER first:

```bash
openssl x509 -inform DER -in root.der -out root.pem
```

Concatenate the `root.pem` files into `ca.crt`, the file the Secret holds. Check it against
the chain the server sends, trusting nothing but the bundle, as the driver does: `leaf.pem`
is the first certificate `s_client -showcerts` prints and `chain.pem` the rest
(`-no-CAstore` needs version 3 of `openssl`):

```bash
openssl verify -no-CApath -no-CAstore -CAfile ca.crt -untrusted chain.pem \
  -verify_hostname <postgres host> leaf.pem
```

A certificate the bundle does not cover fails closed: a pod that boots with it does not
start, and its log shows `SSLCertVerificationError`; in a running pod, every new
connection fails. When the CA announces a change to its hierarchy, update the bundle under
a new Secret name (below).

## Install

```bash
helm install loom deploy/helm/loom -n loom \
  -f deploy/helm/loom/values-eks.yaml -f deploy/helm/loom/values-clickhouse-cloud.yaml \
  --set publicBaseUrl=https://loom.example.com \
  --set clickhouse.host=<service>.<region>.aws.clickhouse.cloud \
  --set postgres.host=<postgres direct host>
```

With `postgres.host` set, the chart gives every pod that runs Loom (web, workers, the hook
Jobs and the analytics export):

- `LOOM_STORAGE_AUTHORITY=postgres`, `LOOM_PG_DURABILITY` and `LOOM_PG_POOL_MAX_SIZE` from
  `postgres.durability` and `postgres.poolMaxSize`;
- `LOOM_PG_DSN` with no password,
  `postgres://<user>@<host>:<port>/<database>?sslmode=verify-full&sslrootcert=/etc/loom/pg-ca/<key>`,
  so the password never sits in the pod spec;
- `PGPASSWORD` from `postgres.passwordSecret`, which the driver reads when the DSN has none;
- `postgres.caSecret` mounted read-only at `/etc/loom/pg-ca`;
- with `networkPolicy.enabled`, egress to `postgres.port`.

The chart refuses to render a `postgres` block it cannot pass on as written, and any of
the names it renders set a second time in `env`, `extraEnv`, `secrets.extra` or
`settingsFile`; each refusal names the value to fix, and the block's comments in
`deploy/helm/loom/values.yaml` state the rules. Its values win over the same names in
`existingSecret` and `envFrom`.

### Connections

Each Loom process holds up to `postgres.poolMaxSize` connections; a call that finds them
all busy waits up to `LOOM_PG_STATEMENT_TIMEOUT_MS` for one, then answers 503. A rollout
runs the old and the new pods side by side, so size the service for twice the steady count:

```text
poolMaxSize × (webWorkers × web pods + worker processes) × 2
  + booting processes + running erasures + CLI runs  ≤  max_connections − reserved
```

Web pods are `autoscaling.maxReplicas` with autoscaling on, else `replicaCount`; worker
processes are `workers.replicaCount` with `workers.enabled`, else one, which a web pod
runs. Each running erasure holds one more connection for its Postgres step. The overlay
sets `webWorkers` to 2, what the image picks for the chart's 2-CPU limit, and
`poolMaxSize` to 5: with three web pods and one workers pod that is 70 connections, which
fits a service whose `max_connections` leaves 100 for Loom. Raising `webWorkers` needs
`resources.limits.cpu` and memory (1 GiB per process) to match.
Replicas added past the chart's count by anything else are outside the sum.

## After the install

- Loom refuses to start when the service's `synchronous_standby_names` is empty. It
  starts with fewer standbys attached than it names, logging the WARNING
  `Postgres durability: fewer synchronous standbys are attached than required`. A commit
  then waits for them up to `LOOM_PG_STATEMENT_TIMEOUT_MS` plus one second, and answers
  503 `POSTGRES_UNAVAILABLE` with `outcome_unknown`
  ([what the boot checks](/pt-BR/operations/postgres#what-the-boot-checks)).
- `GET /v1/health` reports `storage_authority: postgres` and `pg_ok`.
  `GET /v1/health?include=postgres` adds a `postgres` block with `standby: synchronous`;
  the block is missing whenever `pg_ok` is false, when the check's `SELECT 1` fails for
  any reason or takes longer than 2 s.
- A role without `pg_read_all_stats` still starts, with the boot WARNING
  `Postgres durability: cannot see pg_stat_replication; standbys are unverified`, and
  `standby` reads `null`. The WARNING is the reliable sign; `standby` is also `null` when
  its read fails or takes longer than 2 s.

## Rotating the password and the CA

The password, in this order, back to back:

1. Update the password in the secret store and confirm `loom-postgres` has synced.
2. Run `ALTER ROLE loom PASSWORD '…'` on the service.
3. Run `kubectl rollout restart` on the web and workers Deployments together.

Connections already open keep working. Between steps 2 and 3, a call whose pod must open a
new connection (the pool closes connections idle for 300 s) answers 500, and the server
log shows `InvalidPasswordError` (SQLSTATE 28P01); retrying does not help until the pod
restarts. A pod that starts between steps 1 and 2 (a scale-up, an eviction, the analytics
export or a hook Job) fails to boot until step 2, and boots on its next restart.

The CA: create the new bundle under a new Secret name and `helm upgrade` with
`postgres.caSecret.name` set to it. The pod spec changes, so the pods roll. Delete the old
Secret only once the rollout has finished and no revision you might roll back to names it.

## The SQL surface user

An org reads its agents' memory as SQL through a ClickHouse user that
`loom sql-access grant` makes ([SQL surface](/pt-BR/reference/sql-surface)). Run it in a web
pod with a ClickHouse admin credential that may create users, passing the admin password
from a file, never typed at an echoing terminal:

```bash
web=$(kubectl get deploy -n loom -o name \
  -l app.kubernetes.io/instance=loom,app.kubernetes.io/component=web)
kubectl exec -i -n loom "$web" -- sh -c \
  'read -r LOOM_CH_PASSWORD; export LOOM_CH_PASSWORD LOOM_CH_USER=default;
   exec loom sql-access grant --user acme_sql --org acme --namespaces support,research' < admin.pass
```

The command prints the SQL user's password once, to the terminal only: an exec stream is not
a container log. `--namespaces '*'` also covers namespaces made later; never give it on an
org that sign-ups share ([access](/pt-BR/reference/sql-surface#access)). `--all` instead of
`--org … --namespaces …` fits a deployment whose data belongs to one org. Revoke the user
before uninstalling: `loom sql-access revoke --user acme_sql`, run the same way.

## Restoring a service

A restore of either service is followed by `loom reconcile`, run in a Job made from the
web pod's spec while the release is stopped ([restoring one
store](/pt-BR/operations/backup-and-recovery#restoring-one-store)).

## Related

[Kubernetes](/pt-BR/operations/kubernetes) · [Postgres memory authority](/pt-BR/operations/postgres) ·
[Deployment](/pt-BR/operations/deployment) · [SQL surface](/pt-BR/reference/sql-surface) ·
[Query Loom from Postgres](/pt-BR/guides/query-from-postgres)
