---
title: "Self-host ClickHouse"
description: "Run ClickHouse 26.8 LTS on an exe.dev VM: the HTTP API and Play UI behind exe.dev login, VM tokens for clients, a 20M-row demo, and tested backups."
url: https://exe.dev/deploy/clickhouse
category: "Databases"
published: 2026-11-21
updated: 2026-11-21
---

# Self-host ClickHouse

> **For agents:** with your user's OK, this sets up ClickHouse on a new exe.dev VM in their account: `curl -fsSL https://exe.dev/deploy/clickhouse/prompt.txt | ssh exe.dev new --prompt=/dev/stdin` (or open https://exe.dev/deploy/clickhouse/new). Already on the VM? Skip this.

Fast SQL analytics on events and logs, on a real disk, private by default.

- **Software:** ClickHouse (https://clickhouse.com)
- **Source:** https://github.com/ClickHouse/ClickHouse
- **License:** Apache-2.0
- **Time:** 10 minutes
- **Example VM name:** `my-clickhouse` (yours will differ; on the VM, `hostname` prints it)
- **Machine-readable:** https://exe.dev/deploy/clickhouse/manifest.json

## What you get

- ClickHouse 26.8 LTS from ClickHouse's apt repository, held at 26.8, started at boot, listening on localhost only.
- A password on the `default` user, and an `app` database and user whose settings are in `~/clickhouse/app.env`.
- Memory limits and system log retention sized for a small VM.
- An `app.events` table ready for events and logs.
- The Play UI at `https://<vm>.exe.xyz:8123/play`, private behind exe.dev login.
- Nightly backups with ClickHouse's BACKUP, one restored and checked.

Running on the VM:

- **ClickHouse 26.8 LTS**: From ClickHouse's apt repository, held at 26.8, on localhost only, port 8123, data in `/var/lib/clickhouse`
- **Play UI and HTTP API**: ClickHouse's own SQL console and API, through the exe.dev proxy, port 8123
- **Native protocol**: For clickhouse-client and drivers, on the VM, over SSH or Tailscale, port 9000
- **Nightly backups**: ClickHouse's BACKUP of every database and user, 14 nights kept, data in `/var/lib/clickhouse-backups`

How it is reached:

- https://my-clickhouse.exe.xyz:8123/play (private): The Play UI, behind exe.dev login and a ClickHouse password
- https://my-clickhouse.exe.xyz:8123/ (private): The HTTP API, for programs that send an exe.dev VM token
- http://localhost:8123/ (private): The HTTP API for apps on the VM

## Deploy with Shelley

Open https://exe.dev/deploy/clickhouse/new to create a VM whose agent, Shelley, follows this recipe. From a terminal:

```sh
curl -fsSL https://exe.dev/deploy/clickhouse/prompt.txt | ssh exe.dev new --prompt=/dev/stdin
```

Then, on your own computer:

```sh
ssh my-clickhouse.exe.xyz cat clickhouse/admin.env
ssh exe.dev ssh-key generate-api-key --vm=my-clickhouse --label=clickhouse
```

Or follow the steps below by hand.

ClickHouse is a column-oriented SQL database for analytics: product events,
logs, metrics, anything you add a lot of and query in aggregate. It's one
program with no dependencies, which suits a VM of its own. Here it runs from
ClickHouse's packages with its data on the VM's NVMe disk, its HTTP API and
Play UI behind your exe.dev login, VM tokens for programs, and nightly
backups you've seen restored.

## Open the Play UI

When Shelley finishes, open `https://my-clickhouse.exe.xyz:8123/play` and
sign in to exe.dev. That's ClickHouse's own SQL console, served by
ClickHouse on port 8123; exe.dev's proxy adds HTTPS and lets only you in.

ClickHouse has its own users on top. Print the passwords on your computer:

```bash
ssh my-clickhouse.exe.xyz cat clickhouse/admin.env clickhouse/app.env
```

In the Play UI, click the key at the top right and enter user `default` and
the password from `admin.env`. `default` is the admin and can do anything;
your app uses the `app` user, which can only use the `app` database. Run
`SELECT version()` to see it work.

On the VM itself, `clickhouse-client` signs in as `default` with no
questions: on your computer, `ssh -t my-clickhouse.exe.xyz clickhouse-client`.
For a live view of the server, open
`https://my-clickhouse.exe.xyz:8123/dashboard` with the same login.

## Query 20 million rows

To see what ClickHouse is for, load the New York taxi sample from
[ClickHouse's example datasets](https://clickhouse.com/docs/getting-started/example-datasets/nyc-taxi):
20 million trips, read straight from a public S3 bucket. On the VM:

```bash
clickhouse-client --multiquery <<'SQL'
CREATE DATABASE IF NOT EXISTS nyc_taxi;
CREATE TABLE IF NOT EXISTS nyc_taxi.trips (
    trip_id             UInt32,
    pickup_datetime     DateTime,
    dropoff_datetime    DateTime,
    pickup_longitude    Nullable(Float64),
    pickup_latitude     Nullable(Float64),
    dropoff_longitude   Nullable(Float64),
    dropoff_latitude    Nullable(Float64),
    passenger_count     UInt8,
    trip_distance       Float32,
    fare_amount         Float32,
    extra               Float32,
    tip_amount          Float32,
    tolls_amount        Float32,
    total_amount        Float32,
    payment_type        Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
    pickup_ntaname      LowCardinality(String),
    dropoff_ntaname     LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (pickup_datetime, dropoff_datetime);
SQL
clickhouse-client --time -q "INSERT INTO nyc_taxi.trips SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..19}.gz', NOSIGN, 'TabSeparatedWithNames')"
```

On our 2-CPU test VM that took 52 seconds and 400 MB of memory, and the
table takes 770 MB of disk. Now ask it something in the Play UI:

```sql
SELECT toHour(pickup_datetime) AS hour,
       count() AS trips,
       round(avg(dateDiff('minute', pickup_datetime, dropoff_datetime)), 1) AS avg_minutes,
       bar(trips, 0, 1500000, 30) AS chart
FROM nyc_taxi.trips
GROUP BY hour ORDER BY hour
```

<figure>
<img src="https://exe.dev/deploy/clickhouse/play.png" alt="ClickHouse's Play UI showing taxi trips per hour of the day, with bar charts, from 20 million rows in 0.19 seconds" width="1280" height="800" loading="lazy">
<figcaption>20 million rows read in 0.19 seconds on a 2-CPU VM.</figcaption>
</figure>

Or the busiest pickup neighborhoods and what their rides cost:

```sql
SELECT pickup_ntaname AS neighborhood,
       count() AS trips,
       round(avg(total_amount), 2) AS avg_fare
FROM nyc_taxi.trips
WHERE pickup_ntaname != ''
GROUP BY neighborhood ORDER BY trips DESC LIMIT 10
```

Each reads all 20 million rows and answers in well under half a second.
That's the columnar layout at work: a query reads only the columns it names,
compressed. Drop the demo when you're done: `DROP DATABASE nyc_taxi`.

## Ask Shelley about your data

You don't have to write the SQL. Shelley, the agent on your VM at
`https://my-clickhouse.shelley.exe.xyz/`, runs `clickhouse-client` as the
admin, so it can look at your tables, write the queries, check the numbers,
and answer in plain language. With the taxi demo loaded, we asked it:

> Which taxi pickup neighborhoods tip best in my ClickHouse, and are
> late-night riders any different?

In about a minute, after several queries and a look at the dataset's
documentation, it answered with tables. Shortened:

> **Midtown–Midtown South tipped the highest share of fare (20.73%).
> Carroll Gardens–Columbia Street–Red Hook tipped the most dollars per ride
> ($3.44), excluding airports.** Late-night riders tipped a slightly smaller
> share of the fare, but only about two cents less per ride.
>
> | Pickup neighborhood | Tip share | Avg. tip per ride | Rides |
> |---|---:|---:|---:|
> | Midtown–Midtown South | 20.73% | $2.57 | 1,980,939 |
> | Turtle Bay–East Midtown | 20.61% | $2.49 | 828,364 |
> | Park Slope–Gowanus | 20.46% | $3.04 | 19,223 |
>
> *Data caveat:* The local payment labels appear reversed: `CSH` […]
> contains virtually all recorded tips; NYC TLC defines `1` as credit card
> and says cash tips aren't captured. […] The results describe recorded
> card tips, not cash tipping.

That caveat is right: the sample dataset swaps the cash and card labels,
and Shelley noticed before drawing conclusions from them. Ask about your
own data the same way: "how many signups did we get last week, and from
where?", "which events grew fastest this month?", "why is this query
slow?", "what's using the disk?". Shelley can change things as well as
read them, so it can add a table or a
[materialized view](https://clickhouse.com/docs/materialized-view) when you
ask. If you only want answers, say so.

## Send events from your app

Setup made an `app.events` table: a time, an event name, a user, and a
[JSON](https://clickhouse.com/docs/sql-reference/data-types/newjson) column
for anything else, ordered by `(name, ts)` and kept for a year. An app on
the VM sends it batches of JSON lines at `http://localhost:8123`, with the
user and password in `~/clickhouse/app.env`:

```bash
set -a; . ~/clickhouse/app.env; set +a
printf '%s\n' \
  '{"name":"signup","user_id":"u1","properties":{"plan":"free","referrer":"hn"}}' \
  '{"name":"page_view","user_id":"u1","properties":{"path":"/pricing"}}' |
  curl -sS --fail-with-body "$CLICKHOUSE_URL/" -u "$CLICKHOUSE_USER:$CLICKHOUSE_PASSWORD" \
    --url-query "database=$CLICKHOUSE_DATABASE" \
    --url-query 'query=INSERT INTO events FORMAT JSONEachRow' --data-binary @-
```

Then `SELECT name, properties.plan FROM app.events` in the Play UI shows
them. In code, use ClickHouse's
[client for your language](https://clickhouse.com/docs/integrations/language-clients)
with the same settings, and send thousands of rows per insert where you
can. Where you can't, ClickHouse 26.8's
[asynchronous inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts),
on by default, batch them on the server. For another app's database,
`~/clickhouse/create-user.sh blog` makes a `blog` database and user and
writes `~/clickhouse/blog.env`.

## Connect from your computer

Port 8123 is private: a browser gets the exe.dev login page, and a program
without credentials gets `401`. Programs get through with an exe.dev
[VM token](/docs/https-tokens-for-vms) in an `X-Exedev-Authorization`
header. The proxy checks the token and removes the header, then
ClickHouse checks its own user and password as usual. On your computer,
make a token for this VM:

```bash
ssh exe.dev ssh-key generate-api-key --vm=my-clickhouse --label=clickhouse --exp=90d
```

Save it as `EXE_TOKEN` and the `app` password as `CLICKHOUSE_PASSWORD` in
your shell, then:

```bash
curl -sS https://my-clickhouse.exe.xyz:8123/ \
  -H "X-Exedev-Authorization: Bearer $EXE_TOKEN" \
  -H 'X-ClickHouse-User: app' -H "X-ClickHouse-Key: $CLICKHOUSE_PASSWORD" \
  --data-binary 'SELECT currentUser(), version()'
```

Expect `app` and `26.8...`. ClickHouse's own clients take extra headers.
In Python, with `pip install clickhouse-connect`:

```python
import os, clickhouse_connect

client = clickhouse_connect.get_client(
    host="my-clickhouse.exe.xyz", port=8123, secure=True,
    username="app", password=os.environ["CLICKHOUSE_PASSWORD"], database="app",
    headers={"X-Exedev-Authorization": "Bearer " + os.environ["EXE_TOKEN"]},
)
print(client.query("SELECT count() FROM events").result_rows)
```

In Node.js, with `npm install @clickhouse/client`:

```js
import { createClient } from '@clickhouse/client'

const client = createClient({
  url: 'https://my-clickhouse.exe.xyz:8123',
  username: 'app',
  password: process.env.CLICKHOUSE_PASSWORD,
  database: 'app',
  http_headers: { 'X-Exedev-Authorization': `Bearer ${process.env.EXE_TOKEN}` },
})
await client.insert({ table: 'events', values: [{ name: 'signup', user_id: 'u2' }], format: 'JSONEachRow' })
```

We ran both, clickhouse-connect 1.13 and @clickhouse/client 1.25, and
sent a 22 MB batch of 300,000 events with curl in two seconds (compress it
with `-H 'Content-Encoding: gzip'` and it's faster still). Both kinds of
password travel safely together: the token in `X-Exedev-Authorization`,
ClickHouse's in its own headers or the usual `Authorization: Basic`. Add
`--ips=203.0.113.7/32` to the token command to accept it from one address
only.

### clickhouse-client and desktop tools

`clickhouse-client` and most desktop tools use the native protocol on port
9000, which doesn't go through an HTTPS proxy. Tunnel it over SSH, and leave
this running on your computer:

```bash
ssh -N -L 9000:localhost:9000 my-clickhouse.exe.xyz
```

Then connect to `localhost:9000` with the user and password, for example
`clickhouse client --user app --password` with ClickHouse installed on your
computer. DBeaver and DataGrip can also use the HTTP URL above if they let
you add a header, or open the SSH tunnel themselves.

## Connect from another VM

For an app on another exe.dev VM, the
[VM-to-VM integration](/docs/integrations-vm-to-vm) keeps the token off
that VM: exe.dev adds it at the edge. On your computer, with the app's VM
called `my-app`:

```bash
ssh exe.dev integrations add http-proxy --name clickhouse \
  --target https://my-clickhouse.exe.xyz:8123/ --peer --attach vm:my-app
```

The app then uses `https://clickhouse.int.exe.xyz` (port 443) as its
ClickHouse URL, with the `app` user and password, and no token.

For the native protocol, put both VMs on your
[Tailscale](https://tailscale.com/) network; it's preinstalled. On both VMs:

```bash
sudo systemctl enable --now tailscaled
sudo tailscale up --hostname=$(hostname)
```

Open the login URL it prints (or join without one through exe.dev's
[Tailscale integration](/docs/integrations-tailscale-wif)). Then let
ClickHouse listen on every interface, on the ClickHouse VM:

```bash
echo '<clickhouse><listen_host>::</listen_host></clickhouse>' | sudo tee /etc/clickhouse-server/config.d/30-listen.xml
sudo systemctl restart clickhouse-server
```

The other VM reaches it as `my-clickhouse:9000` (native) or
`http://my-clickhouse:8123`. Nothing outside the VM reaches it except
through exe.dev's proxy and your tailnet, and every ClickHouse user has a
password. Use [Tailscale's access rules](https://tailscale.com/kb/1018/acls)
if not every device on your tailnet should reach it.

## Dashboards with Grafana

The Play UI is for asking questions; for dashboards that refresh
themselves, use Grafana with the official
[ClickHouse data source](https://grafana.com/grafana/plugins/grafana-clickhouse-datasource/).
Run Grafana from the [Grafana page](/deploy/grafana), on this VM or another
one on your tailnet, and give it a user that can only read. On the VM:

```bash
pass=$(openssl rand -hex 24)
clickhouse-client --param_pass="$pass" --multiquery -q "
  CREATE USER grafana IDENTIFIED WITH sha256_password BY {pass:String};
  GRANT SELECT ON app.* TO grafana;"
(umask 077; echo "CLICKHOUSE_PASSWORD=$pass" > ~/clickhouse/grafana.env)
```

In Grafana, add the ClickHouse data source with server `localhost` (or
`my-clickhouse` over Tailscale), port 8123, protocol HTTP, user `grafana`,
and that password.

## Keep your data

Every night, ClickHouse's own
[BACKUP](https://clickhouse.com/docs/operations/backup/overview) writes
every database, plus the users and grants, to a zip file in
`/var/lib/clickhouse-backups`; the newest 14 stay. A backup on the same disk
won't survive losing the VM, so keep a copy somewhere else. On your
computer:

```bash
rsync -av my-clickhouse.exe.xyz:/var/lib/clickhouse-backups/ clickhouse-backups/
scp 'my-clickhouse.exe.xyz:clickhouse/*.env' clickhouse-backups/
```

The `.env` files hold the passwords your apps use; keep them private. Or
let ClickHouse send each night's backup to an S3 bucket
([Back up to S3](#back-up-to-s3)), such as your own on
[another VM](/deploy/s3-storage). exe.dev can also back up the whole VM
disk to your S3 bucket with [VM backups](/docs/vm-backups).

Something odd? On the VM,
`sudo tail -n 50 /var/log/clickhouse-server/clickhouse-server.err.log`
usually explains it, or ask Shelley to look.

## The nuts and bolts

Happy clicking Deploy? You can stop here. These are the same setup steps
Shelley follows, plus the maintenance commands for later.

<details id="create-the-vm" data-manual-setup class="dp-details">
<summary>Install by hand</summary>

### Create a VM

On your computer (pick another name if `my-clickhouse` is taken):

```bash
ssh exe.dev new --name=my-clickhouse
ssh my-clickhouse.exe.xyz
```

Planning on more than 15 GB of data? Make the disk bigger now, with
`--disk=100GB` on `new`, or later with
`ssh exe.dev resize my-clickhouse --disk=100GB`. All remaining commands run
**on the VM**, as `exedev`.

### Install ClickHouse 26.8 LTS

Add ClickHouse's apt repository on its `lts` channel, and hold the packages
at 26.8, so `apt upgrade` brings fixes but never a new major version:

```bash
sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor --yes -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=$(dpkg --print-architecture)] https://packages.clickhouse.com/deb lts main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo tee /etc/apt/preferences.d/clickhouse >/dev/null <<'EOF'
Package: clickhouse-*
Pin: version 26.8.*
Pin-Priority: 1001
EOF
sudo apt-get update
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y clickhouse-server clickhouse-client
clickhouse-client --version
```

Expect `ClickHouse client version 26.8...`. The package enables the
service but doesn't start it, and its `default` user has no password yet,
so set things up before starting it.

### Set the passwords

Give the `default` user a password, stored as a hash in ClickHouse's config
and in plain text in `~/clickhouse/admin.env`, which only you can read.
`clickhouse-client` reads it from its own config file, so it signs in
without asking:

```bash
mkdir -p -m 700 ~/clickhouse ~/.clickhouse-client
[ -e ~/clickhouse/admin.env ] || (umask 077; echo "CLICKHOUSE_PASSWORD=$(openssl rand -hex 24)" > ~/clickhouse/admin.env)
. ~/clickhouse/admin.env
hash=$(printf %s "$CLICKHOUSE_PASSWORD" | sha256sum | cut -d' ' -f1)
sudo tee /etc/clickhouse-server/users.d/default-password.xml >/dev/null <<EOF
<clickhouse>
    <users>
        <default>
            <password remove="1" />
            <password_sha256_hex>$hash</password_sha256_hex>
        </default>
    </users>
</clickhouse>
EOF
(umask 077; cat > ~/.clickhouse-client/config.xml <<EOF
<config>
    <user>default</user>
    <password>$CLICKHOUSE_PASSWORD</password>
</config>
EOF
)
```

The password matters even though ClickHouse listens on localhost only:
exe.dev's proxy delivers your browser's requests to it on localhost, so
without a password anyone you share the VM with would be an admin.

### Size it for the VM

ClickHouse uses up to 90% of RAM by default, and each of its caches up to
half. This file leaves a quarter of the memory to the rest of the VM
(the page cache ClickHouse reads through, SSH, Shelley, your app) and
keeps each cache to a quarter. It also gives ClickHouse's own logs of
queries and metrics an expiry: they're tables in the `system` database, and
without one they grow by a couple of hundred MB a day on an idle server.

```bash
sudo tee /etc/clickhouse-server/config.d/10-exe-dev.xml >/dev/null <<'EOF'
<clickhouse>
    <!-- Leave a quarter of the VM's memory for everything else: the page
         cache ClickHouse reads through, SSH, Shelley, and your apps. -->
    <max_server_memory_usage_to_ram_ratio>0.75</max_server_memory_usage_to_ram_ratio>
    <!-- Let each of ClickHouse's caches take a quarter of RAM, not half. -->
    <cache_size_to_ram_max_ratio>0.25</cache_size_to_ram_max_ratio>

    <!-- ClickHouse logs its own queries and metrics into system tables
         that otherwise grow forever. Keep queries for 30 days, metrics
         and traces for 7. -->
    <query_log><ttl>event_date + INTERVAL 30 DAY DELETE</ttl></query_log>
    <query_views_log><ttl>event_date + INTERVAL 30 DAY DELETE</ttl></query_views_log>
    <part_log><ttl>event_date + INTERVAL 30 DAY DELETE</ttl></part_log>
    <error_log><ttl>event_date + INTERVAL 30 DAY DELETE</ttl></error_log>
    <query_thread_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></query_thread_log>
    <text_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></text_log>
    <trace_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></trace_log>
    <metric_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></metric_log>
    <query_metric_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></query_metric_log>
    <asynchronous_metric_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></asynchronous_metric_log>
    <background_schedule_pool_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></background_schedule_pool_log>
</clickhouse>
EOF
```

Both limits are ratios, so they follow the VM when you resize it. Queries
that need more memory than they're allowed spill big `GROUP BY`s and sorts
to disk rather than fail; that's on by default in 26.8.

Add a disk called `backups` for the nightly backups:

```bash
sudo install -d -o clickhouse -g clickhouse -m 750 /var/lib/clickhouse-backups
sudo tee /etc/clickhouse-server/config.d/20-backups.xml >/dev/null <<'EOF'
<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/var/lib/clickhouse-backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/var/lib/clickhouse-backups/</allowed_path>
    </backups>
</clickhouse>
EOF
sudo usermod -aG clickhouse $USER
```

The last line lets you read the backups (from your next login), to copy
them off the VM.

### Start ClickHouse

```bash
sudo systemctl enable --now clickhouse-server
timeout 60 sh -c 'until curl -sf http://localhost:8123/ping >/dev/null; do sleep 1; done'
clickhouse-client -q 'SELECT version(), formatReadableSize(getServerSetting($$max_server_memory_usage$$))'
curl -s -o /dev/null -w '%{http_code}\n' http://localhost:8123/ --data-binary 'SELECT 1'
ss -ltn | grep -E ':(8123|9000) '
```

Expect the version and a memory limit of three quarters of the VM's RAM
(5.25 GiB on an 8 GB VM); then `401`, because ClickHouse refuses a request
without a password; then ports 8123 and 9000 on `127.0.0.1` and `[::1]` only.

### Create a database for your app

This script creates a database and a user with the same name. The user can
do anything in its database and nothing elsewhere. Its settings go to
`~/clickhouse/<name>.env`, and an existing one is never touched:

```bash
cat > ~/clickhouse/create-user.sh <<'EOS'
#!/usr/bin/env bash
# Usage: create-user.sh NAME
# Create a ClickHouse database and a user called NAME that can do anything
# in it (and nothing else), and write its settings to ~/clickhouse/NAME.env.
set -euo pipefail
name=${1:?usage: create-user.sh NAME}
[[ $name =~ ^[a-z_][a-z0-9_]*$ ]] || { echo "Use lowercase letters, digits, and _." >&2; exit 1; }
env="$HOME/clickhouse/$name.env"
if [ -e "$env" ]; then echo "$env already exists; leaving $name alone."; exit 0; fi
pass=$(openssl rand -hex 24)
clickhouse-client --multiquery --param_pass="$pass" <<SQL
CREATE DATABASE IF NOT EXISTS $name;
CREATE USER $name IDENTIFIED WITH sha256_password BY {pass:String};
GRANT ALL ON $name.* TO $name;
SQL
(umask 077; cat > "$env" <<EOF
CLICKHOUSE_URL=http://localhost:8123
CLICKHOUSE_USER=$name
CLICKHOUSE_PASSWORD=$pass
CLICKHOUSE_DATABASE=$name
EOF
)
echo "$env"
EOS
chmod +x ~/clickhouse/create-user.sh
~/clickhouse/create-user.sh app
```

Create the events table as the `app` user, and record one event:

```bash
set -a; . ~/clickhouse/app.env; set +a
ch() { curl -sS --fail-with-body "$CLICKHOUSE_URL/" -u "$CLICKHOUSE_USER:$CLICKHOUSE_PASSWORD" --url-query "database=$CLICKHOUSE_DATABASE" --data-binary "$1"; }
ch "CREATE TABLE IF NOT EXISTS events (
    ts         DateTime64(3) DEFAULT now64(),
    name       LowCardinality(String),
    user_id    String,
    properties JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (name, ts)
TTL toDateTime(ts) + INTERVAL 1 YEAR"
ch "INSERT INTO events (name, user_id, properties) VALUES ('installed', '$(hostname)', '{\"version\":\"26.8\"}')"
ch 'SELECT currentUser(), name, properties.version FROM events'
ch 'CREATE DATABASE other' 2>/dev/null | grep -o ACCESS_DENIED || true
stat -c '%a %n' ~/clickhouse/*.env
```

Expect `app installed 26.8`, then `ACCESS_DENIED`, because `app` can't
touch anything outside its database, then `600` for both `.env` files.

### Check the Play UI

On the VM, the Play UI and the HTTP API answer on port 8123:

```bash
curl -s -o /dev/null -w '%{http_code}\n' http://localhost:8123/play
```

Expect `200`. From outside, `https://<vm>.exe.xyz:8123/play` is the same
page behind exe.dev login; a request without login or token gets `401`.

### Set up nightly backups

This script backs up every database, and the users, roles and grants made
with SQL, into one zip file on the `backups` disk, and keeps the 14 newest.
If `~/clickhouse/s3.env` exists, it also sends a copy to S3
([Back up to S3](#back-up-to-s3)).

```bash
cat > ~/clickhouse/backup.sh <<'EOS'
#!/usr/bin/env bash
# Back up every database, plus the users, roles and grants made with SQL,
# to /var/lib/clickhouse-backups/<UTC time>.zip, and to S3 if
# ~/clickhouse/s3.env exists. Keeps the 14 newest local backups.
set -euo pipefail
keep=14
dir=/var/lib/clickhouse-backups
name=$(date -u +%Y%m%d-%H%M%S)
what="TABLE system.users, TABLE system.roles, TABLE system.settings_profiles,
  TABLE system.row_policies, TABLE system.quotas, TABLE system.functions,
  ALL EXCEPT DATABASES system, information_schema, INFORMATION_SCHEMA"
clickhouse-client -q "BACKUP $what TO Disk('backups', '$name.zip')" >/dev/null
if [ -e "$HOME/clickhouse/s3.env" ]; then
  . "$HOME/clickhouse/s3.env"
  clickhouse-client --param_url="${S3_URL%/}/$name/" --param_key="$S3_ACCESS_KEY" --param_secret="$S3_SECRET_KEY" \
    -q "BACKUP $what TO S3({url:String}, {key:String}, {secret:String})" >/dev/null
fi
sudo find "$dir" -maxdepth 1 -name '2*.zip' | sort | head -n -"$keep" | xargs -r sudo rm -f --
echo "$dir/$name.zip"
EOS
chmod +x ~/clickhouse/backup.sh
```

Schedule it for every night, then take a first backup:

```bash
sudo tee /etc/systemd/system/clickhouse-backup.service >/dev/null <<EOF
[Unit]
Description=Back up ClickHouse to /var/lib/clickhouse-backups
After=clickhouse-server.service

[Service]
Type=oneshot
User=$USER
ExecStart=$HOME/clickhouse/backup.sh
EOF
sudo tee /etc/systemd/system/clickhouse-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Back up ClickHouse every night

[Timer]
OnCalendar=*-*-* 03:30:00
RandomizedDelaySec=30m
Persistent=true

[Install]
WantedBy=timers.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now clickhouse-backup.timer
sudo systemctl start clickhouse-backup.service
systemctl list-timers clickhouse-backup.timer
sudo ls -l /var/lib/clickhouse-backups
```

Expect a next run time and one `.zip` file. For failures:
`journalctl -u clickhouse-backup.service`.

### Test a backup

Restore the newest backup's `app` database next to the real one, and
compare:

```bash
latest=$(sudo ls -1 /var/lib/clickhouse-backups | grep '^2.*\.zip$' | tail -n 1)
clickhouse-client -q "RESTORE DATABASE app AS app_restored FROM Disk('backups', '$latest')"
clickhouse-client -q "SELECT (SELECT count() FROM app.events) AS live, (SELECT count() FROM app_restored.events) AS restored"
clickhouse-client -q "DROP DATABASE app_restored SYNC"
```

Expect `RESTORED`, then `1 1`. Installation done;
continue with [Open the Play UI](#open-the-play-ui).

</details>

<details id="back-up-and-restore" class="dp-details">
<summary>Back up and restore</summary>

On the VM, `~/clickhouse/backup.sh` takes an extra backup and prints its
file. Backups are plain zip files with each table's SQL and data parts
inside; `sudo unzip -l` lists them.

### Undo a mistake

Dropped a table or deleted the wrong rows? Restore it from a backup **next
to** the live one, under a new name. On the VM:

```bash
sudo ls /var/lib/clickhouse-backups/
clickhouse-client -q "RESTORE TABLE app.events AS app.events_restored FROM Disk('backups', '20261121-033012.zip')"
```

Copy back what you need with `INSERT INTO app.events SELECT ... FROM
app.events_restored WHERE ...`, or swap the tables with `EXCHANGE TABLES
app.events AND app.events_restored`, then drop the one you don't want.
`RESTORE DATABASE app AS app_restored` works the same way for a whole
database.

### Restore everything on a new VM

On a new VM, follow [Install by hand](#create-the-vm) up to and including
"Start ClickHouse". Then copy a backup and the `.env` files over. On your
computer:

```bash
scp clickhouse-backups/20261121-033012.zip clickhouse-backups/*.env my-clickhouse.exe.xyz:
```

On the VM, put the backup on the `backups` disk and restore the users and
every database from it:

```bash
sudo install -o clickhouse -g clickhouse -m 640 ~/20261121-033012.zip /var/lib/clickhouse-backups/
clickhouse-client -q "RESTORE ALL FROM Disk('backups', '20261121-033012.zip')"
cp --update=none ~/*.env ~/clickhouse/ && chmod 600 ~/clickhouse/*.env && rm ~/*.env ~/20261121-033012.zip
clickhouse-client -q 'SHOW DATABASES'
clickhouse-client -q 'SHOW USERS'
```

Your databases and users are back, with their old passwords, so the
`.env` files work unchanged. Don't copy `admin.env`'s password over the new
one: the new VM's `default` password is in its own `admin.env`. Then
continue Install by hand from "Set up nightly backups".

### Back up to S3

A backup that leaves the VM survives losing it. ClickHouse writes backups
straight to any S3-compatible bucket: AWS S3, Cloudflare R2, Backblaze B2,
or [your own](/deploy/s3-storage) on another VM on your Tailscale network.
Make a bucket and a key that can write to it, then on the VM:

```bash
(umask 077; cat > ~/clickhouse/s3.env <<'EOF'
S3_URL=https://your-bucket.s3.us-east-1.amazonaws.com/clickhouse
S3_ACCESS_KEY=...
S3_SECRET_KEY=...
EOF
)
~/clickhouse/backup.sh
```

Each night then also writes a full backup to `<S3_URL>/<time>/`. Expire old
ones with your bucket's lifecycle rules. ClickHouse hides the secret key in
its query log. To restore from S3, use the same URL and keys:

```sql
RESTORE DATABASE app AS app_restored
FROM S3('https://your-bucket.s3.us-east-1.amazonaws.com/clickhouse/20261121-033012/', 'KEY', 'SECRET')
```

A bucket on a private exe.dev VM's HTTPS URL won't work here: ClickHouse
can't add the VM token. Use Tailscale, as the [S3
page](/deploy/s3-storage#from-another-vm) explains.

### The whole VM

[VM backups](/docs/vm-backups) copy the VM's entire disk to your own S3
bucket, incrementally. ClickHouse never rewrites a data part in place, so a
disk snapshot of a running server comes back like after a power cut. The
zip files above are still the easiest way to bring back one table.

</details>

<details id="update" class="dp-details">
<summary>Update ClickHouse</summary>

ClickHouse ships LTS releases twice a year and supports each for a year:
26.8 came out on August 30, 2026. Fixes for it arrive several times a week.

### Updates within 26.8

On the VM, back up, then upgrade the packages and restart. Queries fail for
the few seconds ClickHouse takes to come back:

```bash
~/clickhouse/backup.sh
sudo apt-get update
sudo DEBIAN_FRONTEND=noninteractive apt-get install --only-upgrade -y -o Dpkg::Options::=--force-confold \
  clickhouse-server clickhouse-client clickhouse-common-static
sudo systemctl restart clickhouse-server
timeout 60 sh -c 'until curl -sf http://localhost:8123/ping >/dev/null; do sleep 1; done'
clickhouse-client -q 'SELECT version()'
```

The packages don't restart the server themselves; until you do, the old
version keeps running.

### A new LTS

When the next LTS comes out, read the "Backward Incompatible Change"
entries in the [changelog](https://clickhouse.com/docs/whats-new/changelog)
for every release in between. Then back up, change the version in
`/etc/apt/preferences.d/clickhouse` (`Pin: version 27.3.*`, say), and run
the commands above with `apt-get install` in place of
`apt-get install --only-upgrade`. Data written by a newer version may not
be readable by an older one, so the backup is your way back.

</details>

<details id="troubleshooting" class="dp-details">
<summary>Something not working?</summary>

On the VM, ClickHouse's error log usually has the answer:

```bash
systemctl status clickhouse-server
sudo tail -n 50 /var/log/clickhouse-server/clickhouse-server.err.log
```

- **`Authentication failed` (code 516, or 194 with no password):** wrong
  user or password, or none. The `app` password is in
  `~/clickhouse/app.env`, the `default` one in `~/clickhouse/admin.env`.
  Through the proxy, a Basic auth header with the *VM token* as password is
  taken by exe.dev, and ClickHouse sees no password: send the token in
  `X-Exedev-Authorization` instead.
- **`401` from `my-clickhouse.exe.xyz:8123`:** exe.dev wants a login or a
  token. Check the header name and that the token was made with
  `--vm=my-clickhouse` and hasn't expired.
- **`MEMORY_LIMIT_EXCEEDED`:** a query wanted more than the server's
  limit. Narrow it (fewer columns, a `WHERE` on the table's `ORDER BY`
  columns), or give the VM more memory with
  `ssh exe.dev resize my-clickhouse --memory=16GB`; the limit follows.
- **`S3 access ... is not allowed to use the server's own credentials`:**
  add `NOSIGN` for a public bucket, as in the taxi demo, or pass a key and
  secret.
- **`Too many parts`:** something inserts one row at a time with
  asynchronous inserts turned off. Batch the inserts, or leave
  `async_insert` on.
- **Disk filling up:** see what takes the space with
  `clickhouse-client -q "SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC LIMIT 10"`
  and `sudo du -sh /var/lib/clickhouse-backups`. Add a `TTL` to big
  tables, lower `keep` in `~/clickhouse/backup.sh`, or grow the disk with
  `ssh exe.dev resize my-clickhouse --disk=100GB`.
- **It won't start after a config change:** the error log names the file
  and line. Fix or remove the file in `/etc/clickhouse-server/config.d/`.
- **The SSH tunnel says `Address already in use`:** something on your
  computer has port 9000. Use another, `-L 9001:localhost:9000`, and
  connect to port 9001.

</details>

## Frequently asked questions

### How much does it cost to self-host ClickHouse on exe.dev?

ClickHouse is free and open source under Apache 2.0. You pay for your [exe.dev plan](https://exe.dev/pricing), and for a bigger disk if your data outgrows the 25 GB a new VM starts with. ClickHouse compresses well: the 20 million taxi trips in the demo take 770 MB.

### How big a VM does ClickHouse need?

Less than you'd think. We loaded 20 million rows in under a minute and queried them in a fraction of a second on a 2-CPU, 8 GB VM. Memory limits here are ratios of the VM's RAM, so they follow the VM when you [resize](#size-it-for-the-vm) it. Disk is usually what you grow first.

### Can I connect to port 9000 or 8123 from the internet?

Port 8123 yes, over HTTPS through exe.dev's proxy, with your exe.dev login in a browser or a [VM token](#connect-from-your-computer) in a program. The native protocol on port 9000 isn't HTTP, so it stays on the VM: reach it through an SSH tunnel or [Tailscale](#connect-from-another-vm).

### Why not ClickHouse Cloud?

ClickHouse Cloud scales out, separates storage and compute, and runs upgrades for you. A VM is simpler and cheaper for one app's events or logs: a fixed price, data on a local NVMe disk, root access to every setting, and nothing public unless you open it.

### Can I run it in Docker instead?

Yes. The official image is `clickhouse/clickhouse-server:26.8`; mount `/var/lib/clickhouse` and `/etc/clickhouse-server/config.d` from the VM, publish `127.0.0.1:8123:8123`, and set `CLICKHOUSE_PASSWORD`. This page uses the apt packages because they come with `clickhouse-client` and a systemd service, and upgrade with `apt`.

### Does ClickHouse replace PostgreSQL?

No, it sits next to it. Keep users, orders and anything you update row by row in [PostgreSQL](/deploy/postgres). Send ClickHouse the append-only firehose: events, logs, metrics, traces. It answers questions over millions of rows in milliseconds, but updates and deletes are slow, batch operations.


## Related

- [Self-host PostgreSQL](https://exe.dev/deploy/postgres.md): Your own Postgres on a real disk, with backups you've actually restored.
- [Self-host Grafana and Prometheus](https://exe.dev/deploy/grafana.md): Dashboards and alerts for every VM you run, signed in with exe.dev.
- [Self-hosted S3 storage](https://exe.dev/deploy/s3-storage.md): Your own S3 bucket on a real disk. RustFS, Garage or SeaweedFS, tested.
- [Self-host Redis](https://exe.dev/deploy/redis.md): Your own Redis on a real disk, private by default, with backups you've restored.
- [Self-host MySQL](https://exe.dev/deploy/mysql.md): Your own MySQL on a real disk, private by default, with backups you've restored.
- [Deploy a Docker Compose app from GitHub](https://exe.dev/deploy/docker-compose.md): Your compose.yaml on a VM of its own. git push to deploy.
