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
clickhousegroup; 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 officialclickhouse/clickhouse-serverDocker image (agent images for 25.8, 26.3 and 26.8). - The HTTP interface on (port
8123by default). Rowsafe uses it on127.0.0.1. - The
clickhouseprogram (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 -- --accountThe 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 backup | ClickHouse's own BACKUP of every non-system database. Weekly by default. |
| Differential backup | Only the parts written since the last full backup: small and quick. Hourly by default. |
| Encryption | ClickHouse 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 change | Each new part, copied as it appears (checked every 15 seconds), encrypted, with its time. Table and database changes too. |
| Marks | A differential backup taken on the spot. Restoring to a Mark is exact. |
| Restore | To any second or a Mark: the newest backup before it, plus every part made up to it. |
| Proof | Weekly: 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. |
| Rewind | A 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 database | Puts 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. |
| Pulse | Queries, 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. |
| Queries | The query log, grouped with normalizeQuery (values replaced by ?). |
| Recommendations | Large 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. |
| Logs | Warnings and errors with the failing query, values removed on your server. |
| Security | Where 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. |
| Tuning | Memory, 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. |
| Restart | Restart in the dashboard (or rowsafe restart), after you confirm. |
| Updates | New 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 pooling | chproxy on the HTTP interface: limits queries per user and queues the rest. |
| Find the moment | Every 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. |
| Standby | A 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. |
| Fork | A new database from any second or Mark, in an empty ClickHouse on a server you pick. |
| Move in | A one-time copy from ClickHouse Cloud or any ClickHouse. |
| Databases & users | Create 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. |
| Guard | Preview a migration on a copy, and masked safe copies. |
| Files | Your app's folders (uploads), restored to the same moment. |
| Second copy | Weekly 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,BACKUPandS3(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,DROPandALTER 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):
| Permission | Lets Rowsafe |
|---|---|
restart | restart ClickHouse (Restart) |
updates | install ClickHouse's updates and major upgrades (needs restart) |
security-updates, reboot | install the server's security updates, and reboot it |
tuning | write the settings you change under Tuning into Rowsafe's own files (config.d/rowsafe-tuning.xml, users.d/rowsafe-tuning.xml) |
pooler | install and manage chproxy |
firewall | limit 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 itClickHouse 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 eventsclickhouse 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-agentName 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/restoreThe 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/momentLimits
- 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.