Backups are one of those things teams know they need but often defer until something goes wrong. With ClickHouse® databases handling billions of rows, getting backups right is critical.
Why ClickHouse® backups are different
ClickHouse® stores data in parts that are continuously merged in the background. A naive filesystem snapshot can capture an inconsistent state mid-merge. The BACKUP command, introduced in ClickHouse® 22.8, handles this correctly by creating a consistent point-in-time snapshot.
Full vs incremental backups
Full backups copy everything. Simple, reliable, but slow and storage-heavy for large tables. Good for weekly or monthly snapshots.
Incremental backups only copy parts that changed since the last backup. Much faster and smaller, but require a valid base backup to restore from. Good for daily backups between full snapshots.
A practical schedule: full backup weekly, incremental backups daily, with a 30-day retention policy.
Choosing a storage target
ClickHouse® supports backup to local disk, S3, Google Cloud Storage, and Azure Blob Storage. For production, always use cloud storage:
- S3 is the most common target. Use
S3('https://bucket.s3.amazonaws.com/backups/', 'key', 'secret') - Google Cloud Storage works through the S3-compatible API
- Azure Blob Storage is supported natively since ClickHouse® 23.3
Retention policies
Without retention policies, backup storage grows indefinitely. Define rules like:
- Keep daily backups for 7 days
- Keep weekly backups for 4 weeks
- Keep monthly backups for 12 months
Automate cleanup to delete backups older than the retention window.
How CHOps helps
CHOps provides a visual backup management interface that replaces shell scripts and cron jobs. In the Community edition, you get guided BACKUP and RESTORE with S3, GCS, and Azure support. CHOps Pro adds scheduled backups with configurable intervals and retention policies that run automatically.
The backup history view shows every backup with its size, duration, and status. When you need to restore, you select the backup and target table from the interface instead of writing SQL.
Testing restores
A backup you have never restored from is not a backup. Schedule regular restore tests to a staging cluster. Verify row counts, recent data, and schema integrity. This catches corruption issues before they matter.
