Set up docs MCPPerform manual backups and restores of ClickHouse data using SQL commands and S3 storage. For full syntax details, see the upstream ClickHouse backup documentation.
For API-driven backup operations, see Manage Backups via API.
Prerequisites
- An S3 bucket in the same region as your cluster
- The IAM role tied to the Kubernetes service account must have read, write, and list access to the bucket
S3 Path Format
All backup commands use the following S3 path:
https://s3.$REGION.amazonaws.com/$S3_BUCKET/$CLUSTER_S3_PREFIX/$BACKUP_ID| Variable | Description |
|---|---|
$REGION |
S3 region (e.g., us-west-2) |
$S3_BUCKET |
Name of the S3 bucket for backups |
$CLUSTER_S3_PREFIX |
S3 key prefix for the cluster, found in the clickhousecluster resource under spec.s3.keyPrefix (e.g., ch-s3-{uuid}) |
$BACKUP_ID |
A unique identifier (UUID) for this backup |
Perform a Full Backup
From one of the server pods, run:
BACKUP TABLE system.users, TABLE system.roles, TABLE system.settings_profiles, TABLE system.row_policies, TABLE system.quotas, TABLE system.functions, ALL EXCEPT DATABASES INFORMATION_SCHEMA,information_schema, system TO S3('https://s3.$REGION.amazonaws.com/$S3_BUCKET/$CLUSTER_S3_PREFIX/$BACKUP_ID') SETTINGS id='$BACKUP_ID' ASYNC;The backup runs asynchronously. Check progress via the system.backups table:
SELECT * FROM system.backups WHERE id = '$BACKUP_ID';Incremental Backups
See Take an incremental backup in the upstream docs.
Restore from a Backup
Run on the cluster you want to restore into:
RESTORE ALL FROM S3('https://s3.$REGION.amazonaws.com/$S3_BUCKET/$CLUSTER_S3_PREFIX/$BACKUP_ID') SETTINGS id='$RESTORE_ID', allow_different_database_def=true;$RESTORE_ID is any unique identifier you assign to the restore operation.