Skip to content
Rowsafe
Docs

Protect ClickHouse

Backups, restore to any second, Proof, Rewind and Pulse for ClickHouse 24.8 and newer.

Rowsafe protects ClickHouse like PostgreSQL: encrypted backups in your bucket, restore to any second, Marks, a weekly Proof, Rewind and Pulse. See Limits.

How restores to any second work

ClickHouse never changes data in place. Inserts, merges, updates and deletes each write new parts (folders of files), and ClickHouse records when, to the microsecond (system.part_log).

  • The agent copies each new part to your bucket as it appears, encrypted, with its time and every table definition change.
  • It only reads ClickHouse's data folder (on a server, via the clickhouse group; in Docker, a read-only mount).
  • A restore takes the newest backup before your moment and adds every part made up to it: your server exactly as it was.

Before you start

  • ClickHouse 24.8 or newer on Debian 12/13 or Ubuntu 22.04/24.04 (ClickHouse's own packages, clickhouse-server.service), or the official clickhouse/clickhouse-server Docker image (agent images for 25.8, 26.3 and 26.8).
  • The HTTP interface on (port 8123 by default). Rowsafe uses it on 127.0.0.1.
  • The clickhouse program (in the server package). Proof and Rewind copies need it; backups don't.
  • A bucket and an encryption passphrase. See Adopt an existing database.

Turn on backups

Run this on the server, then approve the server in your browser when it prints the link:

curl -fsSL https://rowsafe.sh | sudo sh -s -- --account

The installer finds ClickHouse. Nothing restarts.

Rowsafe's ClickHouse user. The installer adds user rowsafe in /etc/clickhouse-server/users.d/rowsafe.xml (root-owned, readable by ClickHouse only, holding only a password hash). It connects from this server only. ClickHouse loads it within seconds, no restart. The password is readable by the agent alone (/var/lib/rowsafe/engines/clickhouse/logins). Its rights are below.

If that isn't possible (users come from elsewhere), an admin signs in once and Rowsafe creates the user with SQL. That password is never saved.

What Rowsafe may do on the server (restarts, updates, tuning, pooling, firewall), each only when someone clicks and confirms.

The plan. You review it and say yes. ClickHouse's settings don't change. Rowsafe checks a backup reaches your bucket and opens with your key, then takes the first full backup.

On a server without PostgreSQL, the agent runs as its own system user, rowsafe.

Unattended installs: --protect NAME works too. When the users file can't be used, set ROWSAFE_CLICKHOUSE_ADMIN_USER and ROWSAFE_CLICKHOUSE_ADMIN_PASSWORD (used once, never saved).

What Rowsafe does

How
Full backupClickHouse's own BACKUP of every non-system database. Weekly by default.
Differential backupOnly the parts written since the last full backup: small and quick. Hourly by default.
EncryptionClickHouse writes its backup to a short-lived S3-compatible service inside the agent, which encrypts every file and file name. Your bucket never shows database or table names. ClickHouse never sees your bucket keys or passphrase.
Every changeEach new part, copied as it appears (checked every 15 seconds), encrypted, with its time. Table and database changes too.
MarksA differential backup taken on the spot. Restoring to a Mark is exact.
RestoreTo any second or a Mark: the newest backup before it, plus every part made up to it.
ProofWeekly: restore the latest moment into a temporary ClickHouse on the same server (127.0.0.1 only, own data folder), check every database and table reads in full, then delete it.
RewindA copy at any second or Mark, next to production. Compare a table and bring missing rows back. With no unique key, rows are compared on all columns: a row is missing when production has fewer copies of it.
Rewind the whole databasePuts every database back in place. The backup is restored under other names (rowsafe_rewind_<tag>_<db>) while ClickHouse runs, then swapped in partition by partition (REPLACE PARTITION, hard links, no extra disk), so views and dictionaries keep working. Dropped tables come back; new tables are emptied. The old data stays in rowsafe_before_<tag>_* for 7 days (never backed up): Undo rewind or Delete the old data now. Pause inserts while it runs.
PulseQueries, inserts, merges, memory, disk, parts per partition, mutations, replication, broken parts, running queries. Stop query (KILL QUERY, after checking it's the same query). Cancel mutation (KILL MUTATION) for a stuck ALTER ... UPDATE/DELETE; parts already changed stay changed.
QueriesThe query log, grouped with normalizeQuery (values replaced by ?).
RecommendationsLarge tables without a sorting key (ORDER BY tuple()), too many partitions, capacity checks, and skipping indexes and projections tested on a restored copy, with Add buttons.
LogsWarnings and errors with the failing query, values removed on your server.
SecurityWhere ClickHouse listens (also tested from the internet), TLS, passwordless users and admins open to every network, old plaintext passwords, with exact steps, and the firewall.
TuningMemory, spilling to disk, concurrency, threads and merges, explained, with Tune for this server. Written to Rowsafe's own files in config.d and users.d; most apply within seconds.
RestartRestart in the dashboard (or rowsafe restart), after you confirm.
UpdatesNew releases of your version (25.8.x) in one click. A newer version (25.3 to 25.8) is a major upgrade, rehearsed on a copy first, undo for 7 days.
Connection poolingchproxy on the HTTP interface: limits queries per user and queues the rest.
Find the momentEvery DELETE, UPDATE (rows "up to": ClickHouse doesn't count them), TRUNCATE, DROP PARTITION and dropped table, with rows removed. "Just before" is a microsecond earlier. Only kinds, table names, times and counts reach Rowsafe.
StandbyA second server following this one through your bucket (no Keeper, no direct connection), read-only, usually under a minute behind. Promote it or move to a new server.
ForkA new database from any second or Mark, in an empty ClickHouse on a server you pick.
Move inA one-time copy from ClickHouse Cloud or any ClickHouse.
Databases & usersCreate databases and users (read-only, read-and-write or full access per database), new passwords (SHA-256), remove them (a Mark first). Users from config files (like default) are listed, never changed.
GuardPreview a migration on a copy, and masked safe copies.
FilesYour app's folders (uploads), restored to the same moment.
Second copyWeekly full backups also go to a second bucket with its own passphrase; Proof alternates.

What Rowsafe may do

Rowsafe's ClickHouse user (rowsafe, from this server only) may, only when someone asks:

  • SELECT, BACKUP and S3 (to the agent's encrypting gateway): backups, health and compare;
  • INSERT: bring rows back from a copy;
  • KILL QUERY, ALTER UPDATE, ALTER DELETE: stop a query or cancel a stuck mutation;
  • access management and creating databases and tables, with GRANT OPTION: Databases & users;
  • CREATE, DROP and ALTER TABLE: add indexes you pick, and rewind the whole server.

On an empty server, --clickhouse-clones also lets it create and drop databases, for a fork or a move in.

Root decides what Rowsafe may do on the server itself (permissions, sudo rowsafe-allow):

PermissionLets Rowsafe
restartrestart ClickHouse (Restart)
updatesinstall ClickHouse's updates and major upgrades (needs restart)
security-updates, rebootinstall the server's security updates, and reboot it
tuningwrite the settings you change under Tuning into Rowsafe's own files (config.d/rowsafe-tuning.xml, users.d/rowsafe-tuning.xml)
poolerinstall and manage chproxy
firewalllimit who can reach ClickHouse's ports

Rewinding the whole database needs no root permission: it moves data inside ClickHouse.

Docker

Run the ClickHouse agent image next to the official clickhouse/clickhouse-server image. It's built on that image, so Proof and Rewind copies use your version's own clickhouse. Tags: ghcr.io/rowsafe/agent:clickhouse26.8, clickhouse26.3 and clickhouse25.8, tracking the newest Rowsafe release. To pin one, use an exact tag like <version>-clickhouse26.8.

services:
  clickhouse:
    image: clickhouse/clickhouse-server:26.8
    environment:
      CLICKHOUSE_USER: admin
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: "1"   # admin may create Rowsafe's user
    env_file: clickhouse.env                    # CLICKHOUSE_PASSWORD=...
    volumes:
      - chdata:/var/lib/clickhouse

  rowsafe-agent:
    image: ghcr.io/rowsafe/agent:clickhouse26.8
    hostname: db-1
    environment:
      ROWSAFE_CLICKHOUSE_URL: http://clickhouse:8123
      ROWSAFE_CLICKHOUSE_GATEWAY_URL: http://rowsafe-agent:9010
    env_file: rowsafe-agent.env
    volumes:
      - rowsafe-state:/var/lib/rowsafe
      - chdata:/var/lib/clickhouse:ro             # read only: the agent copies new parts from it

ClickHouse sends backups to the agent on port 9010 inside the compose network. Don't publish that port. Then, once:

docker compose exec rowsafe-agent rowsafe-agent clickhouse login --port 8123 --admin-user admin
rowsafe adopt events --host db-1 --engine clickhouse --port 8123
rowsafe apply events

clickhouse login asks for the admin password once and never saves it. Full example: deploy/docker/compose.clickhouse.example.yml.

Update the agent

The agent never updates itself in Docker. Pull the newest image and recreate only the agent (ClickHouse keeps running):

docker compose pull rowsafe-agent && docker compose up -d rowsafe-agent

Name the service: a plain pull and up -d can also upgrade and restart ClickHouse. The server's page says when a new version is out.

For Update now in the dashboard, add the container control service. It holds the Docker socket so the agent never does:

services:
  rowsafe-docker-control:
    image: ghcr.io/rowsafe/docker-control:latest
    restart: unless-stopped
    depends_on: [clickhouse]
    environment:
      ROWSAFE_CONTROL_SERVICE: clickhouse          # your ClickHouse service's name
      ROWSAFE_CONTROL_ALLOW_UIDS: "101"            # the agent image's clickhouse user
      ROWSAFE_CONTROL_UPDATE_ONLY: "1"             # only update the agent: never stop or restart ClickHouse
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - rowsafe-control:/run/rowsafe-control
    network_mode: none
    read_only: true
    cap_drop: [ALL]
    security_opt: ["no-new-privileges:true"]

  rowsafe-agent:
    volumes:
      - rowsafe-state:/var/lib/rowsafe
      - rowsafe-control:/run/rowsafe-control       # added

volumes:
  rowsafe-control:

When an owner or admin clicks Update now and confirms, the control service verifies the release's signed image list, pulls the agent at that digest and recreates only the agent's container. If the new agent doesn't report in within five minutes, the old one starts again and the dashboard says why. ClickHouse keeps running. A running backup or Proof is reported as failed and runs again.

With ROWSAFE_CONTROL_UPDATE_ONLY, it refuses to stop, start or restart ClickHouse, whoever asks. Details: Update the agent from the dashboard and Threat model.

Restore without Rowsafe

Decrypted, your backups are ClickHouse's own backup format. The open-source agent downloads and decrypts them on any Linux server with your bucket settings (the ROWSAFE_REPO_* lines of /etc/rowsafe/agent.env, or set by hand with your saved passphrase):

set -a; . /etc/rowsafe/agent.env; set +a
# --stanza is the database's "Bucket folder" (on its Settings page in the dashboard).
rowsafe-agent clickhouse download-backup --stanza analytics
rowsafe-agent clickhouse download-backup --stanza analytics --label 20261002-010000F_20261002-140000D --to /var/lib/clickhouse/backups/restore
chown -R clickhouse:clickhouse /var/lib/clickhouse/backups/restore

The first command lists backups and Marks. The second decrypts one (plus the full backup it builds on) and prints the RESTORE statement. With /var/lib/clickhouse/backups/ in backups.allowed_path, run it in clickhouse-client:

RESTORE ALL FROM File('/var/lib/clickhouse/backups/restore/20261002-010000F_20261002-140000D/')
  SETTINGS base_backup = File('/var/lib/clickhouse/backups/restore/20261002-010000F/')

For any second, add --at. The agent builds an ordinary backup folder for that moment and prints its RESTORE statement:

rowsafe-agent clickhouse download-backup --stanza analytics --at 2026-10-02T14:31:07Z --to /var/lib/clickhouse/backups/moment

Limits

  • Tables without parts (Log, TinyLog, StripeLog, Memory and similar) and tables on object storage (S3 disks) restore as of the newest backup before your moment, not the second. On a standby, as of the backup it started from (empty again if their definition changes).
  • Short-lived tables created and dropped within a few seconds may be missing from restores to moments in between.
  • When the agent stops for longer than ClickHouse keeps replaced parts (about 8 minutes, old_parts_lifetime), a moment that can't be rebuilt goes back to the newest backup before it. The task says so.
  • Standby servers follow through the bucket (no streaming), so no automatic failover. They need the agent installed natively next to an empty ClickHouse (not in Docker). After a promotion, restores are exact from the new primary's first backup (within the hour).
  • Clusters: only the server where the agent runs is protected. Multi-shard clusters aren't backed up as one. Replicated tables are backed up from this server's replica.
  • Bringing rows back isn't one transaction. If it stops half-way, written rows stay; run it again to finish without duplicates.
  • No bringing rows back into SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree or VersionedCollapsingMergeTree (adding rows changes totals or cancels rows). Restore a copy and copy what you need yourself.
  • Tables that read from another system (Kafka, MySQL, PostgreSQL, S3, URL...) and views fed by them are backed up but left out of Proof and Rewind copies, so tests never touch that system.
  • Replicated tables in a backup make Proof and Rewind copies start their own ClickHouse Keeper, whose internal port listens on all addresses while the copy exists (ClickHouse can't limit it); the task log says so.
  • Rewinding the whole database needs disk room for the backup. Replicated tables and tables whose columns or engine changed can't be rewound in place; tables reading from another system are left as they are.
  • Forks can't hold Replicated* tables and hold real data (use a safe copy for masked data). Users and grants aren't in backups.
  • Connection pooling covers the HTTP interface (chproxy), not the native protocol (port 9000).
  • In Docker, restart the container yourself (Rowsafe notices). Tuning and pooling need the agent on the server. Update by pulling the newest image of the same version.
  • Older installs: a user created before Databases & users, index advice or rewind in place can list but not change. Run the install command again once.
Edit on GitHub