ClickHouse backup.

BACKUP

BACKUP TABLE events TO Disk('backups', 'events-2026-01-15.zip');
BACKUP DATABASE myapp TO Disk('backups', 'myapp-2026-01-15');
BACKUP TABLE events TO S3('https://bucket.s3.../', 'access_key', 'secret_key');

RESTORE

RESTORE TABLE events FROM Disk('backups', 'events-2026-01-15.zip');
RESTORE DATABASE myapp FROM Disk('backups', 'myapp-2026-01-15');

Incremental backup

BACKUP TABLE events TO Disk('backups', 'inc-2026-01-15')
SETTINGS base_backup = Disk('backups', 'full-2026-01-14');

FREEZE

ALTER TABLE events FREEZE;

Creates hard links in /var/lib/clickhouse/shadow/. Snapshot.

Backup script

#!/bin/bash
date=$(date +%F)
clickhouse-client --query "BACKUP DATABASE myapp TO Disk('backups', 'myapp-$date')"
aws s3 cp /var/lib/clickhouse/backups/myapp-$date s3://bucket/clickhouse/ --recursive

clickhouse-backup tool

Third-party tool:

clickhouse-backup create
clickhouse-backup upload backup-name
clickhouse-backup restore backup-name

Manages full + incremental.

Replication ≠ backup

Replica is not a backup. Bug deletes data → all replicas affected.

Common mistakes

  • Backup never restored as test.
  • Backup on same disk as data.
  • No PITR (oplog equivalent).
  • Forgetting to back up schema separately if backup tool doesn’t include.

Read this next

If you want my backup setup, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .