DEV Community

Pratham Chauhan
Pratham Chauhan

Posted on

Self Hosting ClickHouse on a Shared EC2 Box: Private Access via Cloudflare Tunnel + Automated S3 Backups

#aws #database #infrastructure #tutorial

I recently needed an analytics database for a side project. ClickHouse was the obvious pick, it’s stupidly fast for the append heavy, aggregate later workload I had. What I didn’t want was a managed cloud bill, and I already had an EC2 instance running a few other services. So the plan became: run ClickHouse on that same box, keep it completely off the public internet, let a separate web app read from it, and never lose the data.

This is the full walkthrough install, tuning it to co exist with other services on a small box, locking down users, exposing it to a remote app through a Cloudflare Tunnel (zero open ports), and a nightly S3 backup. I’ve folded in every wall I hit along the way as callouts, because those were the parts that actually cost me time.

💡 The shape of it. ClickHouse listens on localhost only. A cloudflared daemon on the same box makes an outbound connection to Cloudflare; a remote app reaches the DB through that tunnel, gated by a Cloudflare Access service token. Backups go to S3 via an EC2 IAM role. No inbound ports for the database, ever.

My box for reference: 2 vCPU / 8 GB RAM, Ubuntu, shared with a few other services.

1. Install ClickHouse

Straight from the official deb repo:

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 -o /usr/share/keyrings/clickhouse-keyring.gpg
ARCH=$(dpkg --print-architecture)
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
Enter fullscreen mode Exit fullscreen mode

The installer prompts you to set a password for the default (admin) user. Set one and save it — it lands in /etc/clickhouse-server/users.d/default-password.xml.

⚠️ Gotcha one command per line. Don’t paste that GPG line and the ARCH= line together as one line. If they get concatenated, gpg tries to open ARCH=amd64 as a file and fails with a cryptic “No such file or directory”. Same with the curl ... | sudo pipe if a paste wraps it across lines, sudo runs with no command. Run each as its own line.

By default ClickHouse binds only to 127.0.0.1/::1 leave it that way. Everything else in this guide relies on it being loopback only.

2. Add swap (cheap OOM insurance)

Small instances usually have no swap, and ClickHouse merges can spike memory. A swapfile keeps the OOM killer from murdering the process (or worse, a neighbour service):

sudo fallocate -l 4G /swapfile && sudo chmod 600 /swapfile
sudo mkswap /swapfile && sudo swapon /swapfile
echo '/swapfile none swap sw 0 0' | sudo tee -a /etc/fstab
Enter fullscreen mode Exit fullscreen mode

3. Tune memory for a shared box

ClickHouse’s defaults assume it owns a big server: a 5 GiB mark cache, generous background pools. On a shared 8 GB box that will starve everything else. I capped it with an absolute memory limit (not a RAM ratio, a ratio would grab 80% of total RAM and choke my other services), plus trimmed caches and noisy system logs.

Best practice is to leave config.xml untouched and drop overrides into config.d/:

sudo tee /etc/clickhouse-server/config.d/z-low-memory.xml >/dev/null <<'XML'
<clickhouse>
    <max_server_memory_usage>2684354560</max_server_memory_usage>   <!-- 2.5 GiB hard cap -->
    <mark_cache_size>268435456</mark_cache_size>                    <!-- 256 MiB, was 5 GiB -->
    <max_concurrent_queries>10</max_concurrent_queries>
    <mlock_executable>false</mlock_executable>
    <metric_log remove="1"/>
    <asynchronous_metric_log remove="1"/>
    <trace_log remove="1"/>
    <text_log remove="1"/>
</clickhouse>
XML
sudo systemctl restart clickhouse-server
Enter fullscreen mode Exit fullscreen mode

With the hard cap, ClickHouse throws a clean “memory limit exceeded” at 2.5 GiB instead of getting OOM killed and taking neighbours down with it.

⚠️ Gotcha don’t lower background_pool_size. My first instinct was to also set <background_pool_size>2</background_pool_size> to save resources. The server then refused to boot:

DB::Exception: The value of 'number_of_free_entries_in_pool_to_execute_mutation' setting (20)
is greater than 'background_pool_size' * 'background_merges_mutations_concurrency_ratio' (4).
... mutations cannot work with these settings. (BAD_ARGUMENTS)

ClickHouse has a startup sanity check: background_pool_size × concurrency_ratio (2) must exceed the mutation free entry threshold (default 20). With a pool of 2, that’s 2 × 2 = 4 < 20, so it hard fails. Those pool threads are mostly idle anyway and cost almost no memory the real lever is max_server_memory_usage. I just removed the override and let it default. If you genuinely must shrink it, you also have to lower three <merge_tree> free entry settings, which isn’t worth it. Whenever a config change won’t boot, check /var/log/clickhouse-server/clickhouse-server.err.log the exception names the exact setting.

Verify the cap is live:

clickhouse-client --password -q "SELECT value FROM system.server_settings WHERE name='max_server_memory_usage'"
# -> 2684354560
Enter fullscreen mode Exit fullscreen mode

4. Create users the file based way (one read write, one read only)

I wanted two dedicated users instead of throwing default around: app_rw for the service that writes, and app_ro for the web app that only reads. ClickHouse supports declarative XML users in users.d/ passwords as SHA 256 hashes, per user settings profiles, quotas, and scoped grants.

First, hash each password:

echo -n 'your-write-password'    | sha256sum | awk '{print $1}'   # -> WRITE_HASH
echo -n 'your-readonly-password' | sha256sum | awk '{print $1}'   # -> RO_HASH
Enter fullscreen mode Exit fullscreen mode

Then define both users. readonly=1 blocks all writes/DDL at the query level; the <grants> block scopes each user to just the metrics database:

sudo tee /etc/clickhouse-server/users.d/z-app-users.xml >/dev/null <<'XML'
<clickhouse>
    <profiles>
        <rw_profile>
            <max_memory_usage>1500000000</max_memory_usage>
            <max_threads>2</max_threads>
            <max_bytes_before_external_group_by>1000000000</max_bytes_before_external_group_by>
            <max_bytes_before_external_sort>1000000000</max_bytes_before_external_sort>
        </rw_profile>
        <ro_profile>
            <readonly>1</readonly>
            <max_memory_usage>1500000000</max_memory_usage>
            <max_threads>2</max_threads>
        </ro_profile>
    </profiles>

    <users>
        <!-- writer: the service running on this same box -->
        <app_rw>
            <password_sha256_hex>WRITE_HASH</password_sha256_hex>
            <networks><ip>::1</ip><ip>127.0.0.1</ip></networks>
            <profile>rw_profile</profile>
            <quota>default</quota>
            <grants>
                <query>GRANT CREATE DATABASE ON metrics.*</query>
                <query>GRANT ALL ON metrics.*</query>
            </grants>
        </app_rw>

        <!-- reader: the remote web app, via the tunnel -->
        <app_ro>
            <password_sha256_hex>RO_HASH</password_sha256_hex>
            <networks><ip>::1</ip><ip>127.0.0.1</ip></networks>
            <profile>ro_profile</profile>
            <quota>ro_quota</quota>
            <grants>
                <query>GRANT SELECT ON metrics.*</query>
            </grants>
        </app_ro>
    </users>

    <quotas>
        <ro_quota>
            <interval>
                <duration>3600</duration>
                <queries>2000</queries>
                <result_rows>100000000</result_rows>
            </interval>
        </ro_quota>
    </quotas>
</clickhouse>
XML
sudo systemctl restart clickhouse-server
Enter fullscreen mode Exit fullscreen mode

💡 Why networks is localhost for both users even the remote reader. The tunnel daemon (next section) opens a fresh connection to localhost:8123, so ClickHouse sees every request as coming from 127.0.0.1, remote or not. Locking the users to loopback is both correct and the tightest setting.

⚠️ Gotcha GRANT ALL ON db.* does not include CREATE DATABASE. When my writer first tried to create the database, it got:

app_rw: Not enough privileges. To execute this query, it's necessary to have the grant CREATE DATABASE ON metrics.*

Database level ALL covers everything inside the database (tables, inserts, selects, alters) but not the privilege to create the database itself that’s a separate grant. Hence the explicit GRANT CREATE DATABASE ON metrics.* line above. It’s scoped to the name metrics, so the user can only ever create that one database.

Sanity check the read only user really is read only:

clickhouse-client -u app_ro --password -d metrics -q "CREATE TABLE t (x Int8) ENGINE=Memory"
# -> "Cannot execute query in readonly mode"  ✅
Enter fullscreen mode Exit fullscreen mode

5. Create the database and tables

Generic schema an events table with a monthly partition and a 12 month TTL, which is a sensible default for time series analytics:

clickhouse-client -u app_rw --password -q "CREATE DATABASE IF NOT EXISTS metrics"

clickhouse-client -u app_rw --password -d metrics --multiquery <<'SQL'
CREATE TABLE IF NOT EXISTS events
(
    event_time  DateTime64(3, 'UTC'),
    event_type  LowCardinality(String),
    user_id     String  DEFAULT '',
    payload     String  DEFAULT '{}',
    duration_ms UInt32  CODEC(T64, LZ4)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time)
TTL toDateTime(event_time) + INTERVAL 12 MONTH;
SQL
Enter fullscreen mode Exit fullscreen mode

LowCardinality(String) for low distinct columns and the T64 codec on the duration give you strong compression for free.

6. Expose it to a remote app with a Cloudflare Tunnel

This is the part that keeps the database private. Instead of opening a port and firewalling it, cloudflared makes an outbound connection to Cloudflare; requests to a hostname get pushed back down that connection to the daemon, which forwards them to localhost:8123 (ClickHouse’s HTTP interface). Zero inbound ports.

I’d already created a named tunnel and installed the service. The routing is defined in a config file with ingress rules:

sudo mkdir -p /etc/cloudflared
sudo tee /etc/cloudflared/config.yml >/dev/null <<'YAML'
tunnel: <your-tunnel-uuid>
credentials-file: /home/ubuntu/.cloudflared/<your-tunnel-uuid>.json

ingress:
  - hostname: db.example.com
    service: http://localhost:8123
  - service: http_status:404          # catch-all, must be last
YAML

cloudflared tunnel ingress validate    # should print OK
Enter fullscreen mode Exit fullscreen mode

Point the systemd service at that config. Edit the unit:

sudo systemctl edit --full cloudflared
Enter fullscreen mode Exit fullscreen mode

Set ExecStart to:

ExecStart=/usr/local/bin/cloudflared tunnel --config /etc/cloudflared/config.yml run
Enter fullscreen mode Exit fullscreen mode

Then reload and restart:

sudo systemctl daemon-reload
sudo systemctl restart cloudflared
journalctl -u cloudflared -n 20 --no-pager   # look for "Registered tunnel connection"
Enter fullscreen mode Exit fullscreen mode

⚠️ Gotcha --url silently ignores your ingress rules. My service was originally started as cloudflared tunnel run --url http://localhost:8000 <name>. That --url flag is single service “quick” mode: it routes every hostname on the tunnel to that one URL and completely ignores config.yml. So my database hostname was quietly being sent to port 8000 and returning 502 Bad Gateway. The fix is exactly what’s above drop --url entirely and use --config ... run so ingress rules take over.

⚠️ Gotcha the run subcommand is mandatory. cloudflared tunnel --config <file> without a trailing run just prints help and exits, and systemd restart loops it. The flag goes before run: cloudflared tunnel --config <file> run.

Confirm the origin is healthy from the box itself:

curl -s localhost:8123   # -> "Ok."
Enter fullscreen mode Exit fullscreen mode

7. Lock the tunnel with Cloudflare Access

Routing to 8123 alone would leave the database open to anyone who knows the URL. The gate is Cloudflare Access: create an Access application on db.example.com with a Service Auth policy and a service token. Every request must then carry CF-Access-Client-Id and CF-Access-Client-Secret headers Cloudflare checks them at its edge and returns 403 to anything without them, before the request ever reaches your box.

Your remote app connects like this (env vars):

DB_HOST=db.example.com
DB_PORT=443
DB_SECURE=true
CF_ACCESS_CLIENT_ID=<service-token-id>
CF_ACCESS_CLIENT_SECRET=<service-token-secret>
DB_USER=app_ro
DB_PASSWORD=<readonly-password>
Enter fullscreen mode Exit fullscreen mode

End to end test from your laptop CF token and read only DB creds:

curl https://db.example.com/ \
  -H "CF-Access-Client-Id: <id>" -H "CF-Access-Client-Secret: <secret>" \
  -H "X-ClickHouse-User: app_ro" -H "X-ClickHouse-Key: <readonly-password>" \
  --data-binary "SELECT count() FROM metrics.events"
Enter fullscreen mode Exit fullscreen mode

Three outcomes confirm everything’s wired: missing CF headers → Cloudflare 403; valid headers + creds → a number; a write attempt with app_ro → ClickHouse “readonly mode” error.

💡 Use X-ClickHouse-User / X-ClickHouse-Key headers, not -u basic auth. Basic auth sets the Authorization header; keeping ClickHouse creds in their own headers avoids any collision with Cloudflare’s CF-Access-* auth.

⚠️ Gotcha the IP allowlist can’t gate the tunnel. Because cloudflared connects to ClickHouse from localhost, ClickHouse sees 127.0.0.1 for all tunnel traffic. Its <networks> allowlist therefore can’t distinguish a remote request from a local one Cloudflare Access is your access control, not the IP list. So: keep the Service Auth policy with no “allow everyone” bypass, give every ClickHouse user a strong password, and hand the remote app only the read only creds.

8. Automated nightly backups to S3

The last piece: I never want to lose this data. I used clickhouse-backup with the EC2 instance’s IAM role for S3 auth, so there are no AWS keys sitting on disk.

8a. Create an IAM role and attach it

In the AWS console: IAM → Policies → Create policy → JSON:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": ["s3:PutObject","s3:GetObject","s3:DeleteObject","s3:ListBucket","s3:AbortMultipartUpload"],
    "Resource": [
      "arn:aws:s3:::my-clickhouse-backups",
      "arn:aws:s3:::my-clickhouse-backups/*"
    ]
  }]
}
Enter fullscreen mode Exit fullscreen mode

Name it, then IAM → Roles → Create role → AWS service → EC2, attach that policy, name the role. Finally EC2 → your instance → Actions → Security → Modify IAM role and attach it. No reboot needed.

8b. Install and configure clickhouse backup 

cd /tmp
curl -fL https://github.com/Altinity/clickhouse-backup/releases/latest/download/clickhouse-backup-linux-amd64.tar.gz -o chb.tar.gz
mkdir -p chb && tar -xzf chb.tar.gz -C chb
sudo mv "$(find chb -name clickhouse-backup -type f | head -1)" /usr/local/bin/
sudo chmod +x /usr/local/bin/clickhouse-backup
clickhouse-backup --version
Enter fullscreen mode Exit fullscreen mode

Config — leaving access_key/secret_key out makes it fall back to the IAM role automatically:

printf 'general:\n  remote_storage: s3\n  backups_to_keep_local: 1\n  backups_to_keep_remote: 14\nclickhouse:\n  host: localhost\n  port: 9000\n  username: default\n  password: "your-default-password"\ns3:\n  bucket: my-clickhouse-backups\n  region: us-east-1\n  path: clickhouse/prod\n' | sudo tee /etc/clickhouse-backup/config.yml >/dev/null
sudo chmod 600 /etc/clickhouse-backup/config.yml
sudo clickhouse-backup print-config    # confirms it parses
Enter fullscreen mode Exit fullscreen mode

⚠️ Gotcha heredocs and YAML hate a flaky paste. My SSH client kept prepending a space to every pasted line. That broke the config two ways: heredocs never terminated (the closing delimiter got indented, so the shell waited forever at the > prompt), and even when written, the YAML failed with did not find expected key because root keys weren’t at column 0. The fix that always works: a single line printf with \n baked in (above) one logical line can’t be mis indented by a paste. Check with cat -A file root keys must be flush left with $ right after and no ^I tabs.

Test it manually before trusting the schedule:

sudo clickhouse-backup create_remote     # freeze + upload in one step, auto-timestamped
sudo clickhouse-backup list remote        # should list your backup in S3
Enter fullscreen mode Exit fullscreen mode

⚠️ Gotcha no EC2 IMDS role found. My first upload created the local backup fine, then failed on the S3 push:

failed to refresh cached credentials, no EC2 IMDS role found, ... EC2 IMDS ... StatusCode: 404

That 404 means no IAM role is attached to the instance the SDK has nowhere to get credentials. I’d forgotten step 8a. Confirm the role is live:

TOKEN=$(curl -sX PUT "http://169.254.169.254/latest/api/token" -H "X-aws-ec2-metadata-token-ttl-seconds: 60")
curl -s -H "X-aws-ec2-metadata-token:$TOKEN" http://169.254.169.254/latest/meta-data/iam/security-credentials/
# -> your-role-name  (empty = no role attached)

Attach the role, wait ~30s, re run. If you genuinely can’t use a role, you can put access_key/secret_key in the s3: block instead but the role is cleaner and rotation free.

8c. The cron a fixed local time

I wanted 07:30 in my own timezone regardless of the box’s UTC clock. Cron supports CRON_TZ, which spares you any offset math:

CRON_TZ=Asia/Kolkata
30 7 * * * root /usr/local/bin/clickhouse-backup create_remote >> /var/log/clickhouse-backup.log 2>&1
Enter fullscreen mode Exit fullscreen mode

Put that in /etc/cron.d/clickhouse-backup. create_remote with no name auto timestamps each backup, and backups_to_keep_remote: 14 prunes to the last 14 automatically no cleanup job needed.

⚠️ Gotcha no leading whitespace, and no dot in the filename. After my flaky paste, cat -A showed a leading space on both lines. A space before 30 7 ... cron tolerates, but a space before CRON_TZ means cron may ignore the timezone line and run at 07:30 UTC instead. Strip it: sudo sed -i 's/^[[:space:]]*//' /etc/cron.d/clickhouse-backup. Also: files in /etc/cron.d/ must not have a dot in the name cron silently ignores clickhouse-backup.conf. Name it clickhouse-backup.

Verify:

cat -A /etc/cron.d/clickhouse-backup      # two flush left lines
systemctl status cron --no-pager | head -3
Enter fullscreen mode Exit fullscreen mode

Where it stands

That’s the whole thing: ClickHouse running on a shared box, bound to localhost, tuned so it plays nice with its neighbours; two scoped users; a Cloudflare Tunnel + Access combo that lets a remote app read the data with zero open ports; and a nightly IAM authenticated backup to S3 with 14 day retention.

A few things I deliberately left for later (YAGNI until proven otherwise):

  • Incremental backups (-diff-from-remote) only worth it once full backup time actually hurts.
  • A “backup didn’t run” alert a second cron that pings me if no success line hits the log in ~26h. Worth adding the day the data becomes recovery critical.
  • TLS between cloudflared and ClickHouse unnecessary here since that hop is over loopback.

The recurring lesson, if there is one: read the error log. Every single failure I hit the boot refusal, the 502, the IMDS 404, the YAML parse error told me exactly what was wrong in its first line. The fixes were small; finding them was just a matter of looking.

Top comments (0)