ClickHouse Backup Guide

This guide covers the two backup options available for ClickHouse in Opik’s Kubernetes deployment:

SQL-based Backup - Uses ClickHouse’s native BACKUP command with S3
ClickHouse Backup Tool - Uses the dedicated clickhouse-backup tool

Overview

ClickHouse backup is essential for data protection and disaster recovery. Opik provides two different approaches to handle backups, each with its own advantages:

SQL-based Backup: Simple, uses ClickHouse’s built-in backup functionality
ClickHouse Backup Tool: More advanced, provides additional features like compression and incremental backups

Option 1: SQL-based Backup (Default)

This is the default backup method that uses ClickHouse’s native BACKUP command to create backups directly to S3-compatible storage.

Features

Uses ClickHouse’s built-in BACKUP ALL EXCEPT DATABASE system command
Direct S3 upload with timestamped backup names
Configurable schedule via CronJob
Supports both AWS S3 and S3-compatible storage (like MinIO)

Configuration

Basic Setup

With AWS S3 Credentials

Create a Kubernetes secret with your S3 credentials:

$ kubectl create secret generic clickhouse-backup-secret \
>   --from-literal=access_key_id=YOUR_ACCESS_KEY \
>   --from-literal=access_key_secret=YOUR_SECRET_KEY

Then configure the backup:

1 clickhouse:
2   backup:
3     enabled: true
4     bucketURL: "https://your-bucket.s3.region.amazonaws.com"
5     secretName: "clickhouse-backup-secret"
6     schedule: "0 0 * * *"

With IAM Role (AWS EKS)

For AWS EKS clusters, you can use IAM roles instead of access keys:

1 clickhouse:
2   serviceAccount:
3     create: true
4     name: "opik-clickhouse"
5     annotations:
6       eks.amazonaws.com/role-arn: "arn:aws:iam::ACCOUNT:role/clickhouse-backup-role"
7   backup:
8     enabled: true
9     bucketURL: "https://your-bucket.s3.region.amazonaws.com"
10     schedule: "0 0 * * *"

Required IAM Policy:

1 {
2   "Version": "2012-10-17",
3   "Statement": [
4     {
5       "Effect": "Allow",
6       "Action": "s3:*",
7       "Resource": ["arn:aws:s3:::your-bucket", "arn:aws:s3:::your-bucket/*"]
8     }
9   ]
10 }

Trust Relationship Policy:

1 {
2   "Version": "2012-10-17",
3   "Statement": [
4     {
5       "Effect": "Allow",
6       "Principal": {
7         "Federated": "arn:aws:iam::ACCOUNT:oidc-provider/oidc.eks.REGION.amazonaws.com/id/OIDCPROVIDERID"
8       },
9       "Action": "sts:AssumeRoleWithWebIdentity",
10       "Condition": {
11         "StringEquals": {
12           "oidc.eks.REGION.amazonaws.com/id/OIDCPROVIDERID:sub": "system:serviceaccount:YOUR_NAMESPACE:opik-clickhouse",
13           "oidc.eks.REGION.amazonaws.com/id/OIDCPROVIDERID:aud": "sts.amazonaws.com"
14         }
15       }
16     }
17   ]
18 }

Custom Backup Command

You can customize the backup command if needed:

1 clickhouse:
2   backup:
3     enabled: true
4     bucketURL: "https://your-bucket.s3.region.amazonaws.com"
5     command:
6       - /bin/bash
7       - "-cx"
8       - |-
9         export backupname=backup$(date +'%Y%m%d%H%M')
10         echo "BACKUP ALL EXCEPT DATABASE system TO S3('${CLICKHOUSE_BACKUP_BUCKET}/${backupname}/', '$ACCESS_KEY', '$SECRET_KEY');" > /tmp/backQuery.sql
11         clickhouse-client -h clickhouse-opik-clickhouse --send_timeout 600000 --receive_timeout 600000 --port 9000 --queries-file=/tmp/backQuery.sql

Backup Process

The SQL-based backup:

Creates a timestamped backup name (format: backupYYYYMMDDHHMM)
Executes BACKUP ALL EXCEPT DATABASE system TO S3(...) command
Uploads all databases except the system database to S3
Uses ClickHouse’s native backup format

Restore Process

To restore from a SQL-based backup:

$ # Connect to ClickHouse
$ kubectl exec -it deployment/clickhouse-opik-clickhouse -- clickhouse-client
$ 
$ # Restore from S3 backup
$ RESTORE ALL FROM S3('https://your-bucket.s3.region.amazonaws.com/backup202401011200/', 'ACCESS_KEY', 'SECRET_KEY');

Option 2: ClickHouse Backup Tool

The ClickHouse Backup Tool provides more advanced backup features including compression, incremental backups, and better restore capabilities.

Features

Advanced backup management with compression
Incremental backup support
REST API for backup operations
Better restore capabilities
Backup metadata and validation

Configuration

Enable Backup Server

1 clickhouse:
2   backupServer:
3     enabled: true
4     image: "altinity/clickhouse-backup:2.6.23"
5     port: 7171
6     env:
7       LOG_LEVEL: "info"
8       ALLOW_EMPTY_BACKUPS: true
9       API_LISTEN: "0.0.0.0:7171"
10       API_CREATE_INTEGRATION_TABLES: true

Configure S3 Storage

Set up S3 configuration for the backup tool:

1 clickhouse:
2   backupServer:
3     enabled: true
4     env:
5       S3_BUCKET: "your-backup-bucket"
6       S3_ACCESS_KEY: "your-access-key" # can be ignored when use role
7       S3_SECRET_KEY: "your-secret-key"
8       S3_REGION: "us-west-2"
9       S3_ENDPOINT: "https://s3.us-west-2.amazonaws.com" # Optional: for S3-compatible storage

With Kubernetes Secrets

Use Kubernetes secrets for sensitive data:

(can be ignored when using IAM roles)

$ kubectl create secret generic clickhouse-backup-tool-secret \
>   --from-literal=S3_ACCESS_KEY=YOUR_ACCESS_KEY \
>   --from-literal=S3_SECRET_KEY=YOUR_SECRET_KEY

1 clickhouse:
2   backupServer:
3     enabled: true
4     env:
5       S3_BUCKET: "your-backup-bucket"
6       S3_REGION: "us-west-2"
7     envFrom:
8       - secretRef:
9           name: "clickhouse-backup-tool-secret"

Using the Backup Tool

Create Backup

$ # Port-forward to access the backup server
$ kubectl port-forward svc/chi-opik-clickhouse-cluster-0-0 7171:7171
$ 
$ # Create a backup
$ curl -X POST "http://localhost:7171/backup/create?name=backup-$(date +%Y%m%d-%H%M%S)"
$ 
$ # List available backups
$ curl "http://localhost:7171/backup/list"

Upload Backup to S3

$ # Upload backup to S3
$ curl -X POST "http://localhost:7171/backup/upload/backup-20240101-120000"

Download and Restore

$ # Download backup from S3
$ curl -X POST "http://localhost:7171/backup/download/backup-20240101-120000"
$ 
$ # Restore backup
$ curl -X POST "http://localhost:7171/backup/restore/backup-20240101-120000"

Automated Backup with CronJob

You can create a custom CronJob to automate the backup tool:

1 apiVersion: batch/v1
2 kind: CronJob
3 metadata:
4   name: clickhouse-backup-tool-job
5 spec:
6   schedule: "0 2 * * *" # Daily at 2 AM
7   jobTemplate:
8     spec:
9       template:
10         spec:
11           containers:
12             - name: backup-tool
13               image: altinity/clickhouse-backup:2.6.23
14               command:
15                 - /bin/bash
16                 - -c
17                 - |
18                   BACKUP_NAME="backup-$(date +%Y%m%d-%H%M%S)"
19                   curl -X POST "http://clickhouse-opik-clickhouse:7171/backup/create?name=$BACKUP_NAME"
20                   sleep 30
21                   curl -X POST "http://clickhouse-opik-clickhouse:7171/backup/upload/$BACKUP_NAME"
22           restartPolicy: OnFailure

Automated Restore with Kubernetes Job

The Opik helm chart ships a Kubernetes Job that runs a complete restore: it picks restore or restore_remote depending on whether the backup is already on local disk, starts it through the backup server API, and polls until it finishes.

The backup server must be enabled before the restore, and the backup must already exist and have been created by the backup server — not by the SQL-based backup in Option 1.

The restore replaces data. clickhouse-backup drops and recreates every table contained in the backup (ON CLUSTER when RESTORE_SCHEMA_ON_CLUSTER is set) before restoring it, so you do not need to drop databases or tables yourself. Tables that are not part of the backup are left untouched.

One exception: if restore_schema_on_cluster is set through a config file while the RESTORE_SCHEMA_ON_CLUSTER environment variable is empty, clickhouse-backup refuses to drop tables that still hold data and the restore aborts. Set it as an environment variable, as the values example below does.

The job does not retry (backoffLimit: 0) and is capped by activeDeadlineSeconds — 24 hours by default. Raise it for large restores, or Kubernetes will kill the job mid-restore.

Find the backup name

backupName is required, and must match a name the backup server knows:

$ kubectl port-forward -n <namespace> svc/chi-opik-clickhouse-cluster-0-0 7171:7171
$ 
$ # Backups in S3
$ curl -s "http://localhost:7171/backup/list/remote"
$ 
$ # Backups already on local disk
$ curl -s "http://localhost:7171/backup/list/local"

Use the name field, not the S3 prefix. With S3_PATH: shard-{shard}, a backup stored at s3://your-bucket/shard-0/2026-07-28/ has the name 2026-07-28.

The service, pod and port used throughout this section are the chart defaults, matching the RESTORE_SERVICE the Job computes. If you set nameOverride, a different shard/replica layout, or clickhouse.backupServer.service.name / .port, substitute your own — list them with kubectl get pods,svc -n <namespace> -l clickhouse.altinity.com/chi. The port is clickhouse.backupServer.service.port when set, and otherwise clickhouse.backupServer.port (7171 by default).

Render the job manifest

The backup server has to be running in the release already. The command below renders only the Job, so nothing under backupServer reaches the cluster through it — if the server is not enabled yet, roll it out with helm upgrade first:

1 # part of your release values
2 clickhouse:
3   backupServer:
4     enabled: true
5     # S3 settings the backup server needs for restore_remote (downloads from S3)
6     env:
7       REMOTE_STORAGE: s3
8       S3_BUCKET: YOURBUCKET
9       S3_PATH: YOURPATH
10       RESTORE_SCHEMA_ON_CLUSTER: cluster  # so the schema is restored on every replica

Then put the restore settings, which are only needed at render time, in their own values file:

1 # restore-values.yaml
2 clickhouse:
3   backup:
4     restore:
5       createJob: true
6       backupName: "2026-07-28"          # from step 1
7       activeDeadlineSeconds: 604800     # 7 days; default is 24h
8       image: "amazon/aws-cli:2.27.49"   # any image with bash and curl

Render only the restore job. Pass your existing values file first, so the job inherits the same service account, node selector and tolerations as your ClickHouse pods:

$ helm template opik opik/opik \
>   -f your-values.yaml -f restore-values.yaml \
>   --show-only templates/clickhouse_restore_job.yaml > clickhouse-restore-job.yaml

helm template does not write a namespace into the manifest, so either add namespace: to the job’s metadata or pass -n when you apply it.

Create the job

$ kubectl apply -f clickhouse-restore-job.yaml -n <namespace>

The job is named <opik.name>-clickhouse-restore — opik-clickhouse-restore unless you set nameOverride, and always readable as metadata.name in the manifest you just rendered. Use that name in the commands here and in the next step. It is fixed per release, so delete the previous job before restoring again:

$ kubectl delete job opik-clickhouse-restore -n <namespace>

Re-applying is safe: if this backup was already restored the Job exits without restoring it again, and if a restore is still running — for example after its pod was rescheduled — it follows that one instead of starting a second.

Watch the restore

$ kubectl get job opik-clickhouse-restore -n <namespace>
$ 
$ kubectl logs -f job/opik-clickhouse-restore -n <namespace>

The job polls every 15 minutes, so the log stays quiet between checks — a large restore_remote runs for hours. It prints the final status and fails the job if the restore failed.

Checking progress during a long restore

The job’s own log only reports in progress. For actual progress, use these — in increasing cost order.

Per-table progress, from the backup server’s log:

$ kubectl logs chi-opik-clickhouse-cluster-0-0-0 -c clickhouse-backup -n <namespace> \
>   | grep download_data | tail -5

Each line is one finished table: progress=9/44 size=457.38GiB table=opik_prod.spans. Note that 9/44 is the table’s position in the list, not a count of finished tables — count the distinct positions you have seen, or the numerator will look stuck while most tables are done.

Current status and start time:

$ kubectl exec chi-opik-clickhouse-cluster-0-0-0 -c clickhouse-backup -n <namespace> \
>   -- curl -s localhost:7171/backup/actions | tail -3

Bytes landed on disk, against the backup’s own size:

$ # how much is on the data volume now
$ kubectl exec chi-opik-clickhouse-cluster-0-0-0 -c clickhouse-backup -n <namespace> \
>   -- df -h /var/lib/clickhouse
$ 
$ # the size the finished restore should reach (data_size)
$ kubectl exec chi-opik-clickhouse-cluster-0-0-0 -c clickhouse-backup -n <namespace> \
>   -- curl -s localhost:7171/backup/list/remote

Two df readings a few minutes apart give a rough throughput and ETA, but treat that as a coarse capacity check rather than restore progress: df reports the whole volume, so merges, system tables and any other writes land in the same delta, and the total can pass the backup’s data_size before the restore is done. The per-table download_data lines above are the authoritative signal. Prefer df over du -sb on the backup directory either way: du walks every file in a multi-terabyte tree and adds significant I/O to the volume the restore is already saturating.

The /backup/actions history is kept in memory. It is empty if the backup-server container restarted since the restore began, so this is a live-progress tool only — after the fact there is no local record. If you scrape metrics, kubelet_volume_stats_used_bytes for the ClickHouse PVC is the durable equivalent and needs no exec.

Setting createJob: true in your release values works too — helm upgrade then creates the same job. If you do that, set it back to false afterwards, otherwise the next helm upgrade re-creates the job and restores again. The “already restored” check reads the backup server’s in-memory action history, so it does not survive a backup server restart.

Comparison

Feature	SQL-based Backup	ClickHouse Backup Tool
Setup Complexity	Simple	Moderate
Compression	No	Yes
Incremental Backups	No	Yes
Backup Validation	Basic	Advanced
REST API	No	Yes
Restore Flexibility	Basic	Advanced
Resource Usage	Low	Moderate
S3 Compatibility	Native	Native

Best Practices

General Recommendations

Test Restores: Regularly test backup restoration procedures
Monitor Backup Jobs: Set up monitoring for backup job failures
Retention Policy: Implement backup retention policies
Cross-Region: Consider cross-region backup replication for disaster recovery

Security

Access Control: Use IAM roles when possible instead of access keys
Encryption: Enable S3 server-side encryption for backup storage
Network Security: Use VPC endpoints for S3 access when available

Performance

Schedule: Run backups during low-traffic periods
Resource Limits: Set appropriate resource limits for backup jobs
Storage Class: Use appropriate S3 storage classes for cost optimization

Troubleshooting

Common Issues

Backup Job Fails

$ # Check backup job logs
$ kubectl logs -l app=clickhouse-backup
$ 
$ # Check CronJob status
$ kubectl get cronjobs
$ kubectl describe cronjob clickhouse-backup

S3 Access Issues

$ # Test S3 connectivity
$ kubectl exec -it deployment/clickhouse-opik-clickhouse -- \
>   clickhouse-client --query "SELECT * FROM system.disks WHERE name='s3'"

Backup Tool API Issues

$ # Check backup server logs
$ kubectl logs -l app=clickhouse-backup-server
$ 
$ # Test API connectivity
$ kubectl port-forward svc/clickhouse-opik-clickhouse 7171:7171
$ curl "http://localhost:7171/backup/list"

Monitoring

Set up monitoring for backup operations:

1 # Example Prometheus alerts
2 - alert: ClickHouseBackupFailed
3   expr: increase(kube_job_status_failed{job_name=~".*clickhouse-backup.*"}[5m]) > 0
4   for: 0m
5   labels:
6     severity: warning
7   annotations:
8     summary: "ClickHouse backup job failed"
9     description: "ClickHouse backup job {{ $labels.job_name }} has failed"

Migration Between Backup Methods

From SQL-based to ClickHouse Backup Tool

Enable the backup server:

1 clickhouse:
2   backupServer:
3     enabled: true

Create initial backup with the tool

Disable SQL-based backup:

1 clickhouse:
2   backup:
3     enabled: false

From ClickHouse Backup Tool to SQL-based

Disable backup server:

1 clickhouse:
2   backupServer:
3     enabled: false

Enable SQL-based backup:

1 clickhouse:
2   backup:
3     enabled: true

Support

For additional help with ClickHouse backups: