--- alias: operation-guide-clickhouse-scheduledbackups tags: - database - ClickHouse - backup - restore - kubernetes - openshift description: "Automate ClickHouse backups and restores in MES environments on Kubernetes or OpenShift using the Altinity clickhouse-backup tool and S3 storage" --- # Scheduled ClickHouse Backups for Disaster Recovery This guide describes how to implement scheduled, infrastructure-level backups of the ClickHouse databases used by Critical Manufacturing MES when ClickHouse runs on Kubernetes or OpenShift. These backups protect production environments against data loss and are restored as part of a disaster recovery process - they are not installable MES packages. The solution uses the open-source [Altinity clickhouse-backup](https://github.com/Altinity/clickhouse-backup) tool deployed as a sidecar container, Kubernetes CronJobs for scheduling, and S3-compatible object storage for off-site retention. !!! info "Scope" This procedure applies to self-managed ClickHouse deployments running inside a Kubernetes or OpenShift cluster, as described in the [ClickHouse Connection Setup](../../../installationguide/planning-and-installation-guides/additional-components/clickhouse.md) guide. If you use a fully managed service such as ClickHouse Cloud, use the backup and recovery capabilities provided by the service instead. ## When to Use This Procedure Critical Manufacturing MES already provides an application-level backup mechanism through the MES Web API, described in [[operation-guide-clickhouse-backupandrestoreoverview]]. The two approaches are complementary: | Scenario | Recommended Procedure | | --- | --- | | Environment copy, migration to new infrastructure, or restore during an MES installation | MES Web API backup packages - see [[operation-guide-clickhouse-backupandrestoreoverview]] | | Scheduled, automated disaster recovery backups of production environments | This guide | | Large datasets where full daily backups are impractical (incremental backups required) | This guide | | Off-site backup retention on S3-compatible storage | This guide | Table: Choosing between MES Web API backups and infrastructure-level backups For the overall disaster recovery strategy, including backup priorities, restore order, and consistency expectations, see [[operation-guide-disaster-recovery]]. ## Database Structure {% include-markdown 'includes/tech/mes-clickhouse-databases.md' %} By default, the solution described in this guide backs up all databases on the ClickHouse instance (excluding system tables), which covers all four MES databases in a single backup. ## Architecture The backup architecture consists of three components: ```mermaid graph LR subgraph pod ["ClickHouse Pod (StatefulSet)"] CH[ClickHouse Server] CB[clickhouse-backup sidecar] PV[("Shared data volume
/var/lib/clickhouse")] CH --- PV CB --- PV end CJ["CronJobs
Full and incremental backups"] -- "REST API (7171)" --> CB CB <--> S3[("S3-Compatible Storage
MinIO, AWS S3, or other")] classDef mermaid_businessdata color:#000, fill:#65CDE8, stroke:#65CDE8, stroke-width:0px, font-size:100%; classDef mermaid_nonbusinessdata color:#000, fill:#B7DEE8, stroke:#B7DEE8, stroke-width:0px, font-size:100%; class CH,CB mermaid_businessdata class PV,CJ,S3 mermaid_nonbusinessdata ``` * **clickhouse-backup sidecar** - Runs in the same pod as the ClickHouse server, in API server mode (port 7171). It mounts the same data volume as ClickHouse and performs all file-level operations. * **CronJobs** - Lightweight scheduled jobs that trigger backup operations through the sidecar's REST API. They never touch ClickHouse data files directly. * **S3-compatible storage** - Remote target for backup retention. Any S3-compatible backend works (MinIO, AWS S3, Azure Blob via S3 gateway, or another provider). !!! note "The Need for a Sidecar Container" The clickhouse-backup tool requires direct access to the ClickHouse data directory. Because the ClickHouse data volume is typically a `ReadWriteOnce` persistent volume claim, the backup container must run in the same pod as the ClickHouse server. Running it as a sidecar also guarantees both containers share the same filesystem permissions. ## Prerequisites Before starting, ensure you have: * A self-managed ClickHouse deployment (StatefulSet) running in Kubernetes or OpenShift. * An S3-compatible object storage bucket reachable from the cluster. * `kubectl` (or `oc`) access with permissions to edit the ClickHouse StatefulSet and create Secrets, Services, and CronJobs in the ClickHouse namespace. * A ClickHouse user with access to all MES databases. For details on ClickHouse accounts, see [[installation-guide-accountsandsecurity]]. !!! warning "Backup Storage Location" For production environments, do not store backups only on storage provisioned inside the same cluster. Use an external S3 endpoint, or replicate the backup bucket to an off-site location, so that backups survive a full cluster or storage failure. An in-cluster MinIO instance is acceptable for development and test environments. ## Step 1: Prepare the S3 Bucket Create a dedicated bucket for ClickHouse backups. For MinIO, you can use the web console or the `mc` client: ```bash mc alias set backup https:// mc mb backup/clickhouse-backup ``` Recommendations: * Use a dedicated bucket (or bucket prefix) per environment, so backups from different systems are never mixed. * Create dedicated credentials scoped to this bucket only. Do not reuse administrator credentials. * If your provider supports it, enable bucket versioning, encryption at rest, and lifecycle policies aligned with your retention requirements. ## Step 2: Create the Credentials Secret Store the S3 and ClickHouse credentials in a Kubernetes Secret in the ClickHouse namespace: ```yaml apiVersion: v1 kind: Secret metadata: name: clickhouse-backup-credentials namespace: type: Opaque stringData: S3_ACCESS_KEY: "" S3_SECRET_KEY: "" CLICKHOUSE_USERNAME: "" CLICKHOUSE_PASSWORD: "" ``` Apply it: ```bash kubectl apply -f clickhouse-backup-credentials.yaml ``` ## Step 3: Add the clickhouse-backup Sidecar Edit the ClickHouse StatefulSet and add the clickhouse-backup container next to the existing ClickHouse server container: ```yaml # Add under spec.template.spec.containers of the ClickHouse StatefulSet - name: clickhouse-backup image: altinity/clickhouse-backup: command: ["/bin/clickhouse-backup", "server"] ports: - name: backup-api containerPort: 7171 env: - name: LOG_LEVEL value: "info" - name: API_LISTEN value: "0.0.0.0:7171" - name: ALLOW_EMPTY_BACKUPS value: "true" - name: BACKUPS_TO_KEEP_LOCAL value: "2" - name: BACKUPS_TO_KEEP_REMOTE value: "14" - name: REMOTE_STORAGE value: "s3" - name: S3_BUCKET value: "clickhouse-backup" - name: S3_PATH value: "backups/" - name: S3_ENDPOINT value: "https://" - name: S3_REGION value: "us-east-1" - name: S3_FORCE_PATH_STYLE value: "true" - name: CLICKHOUSE_HOST value: "localhost" - name: CLICKHOUSE_PORT value: "9000" - name: CLICKHOUSE_SKIP_TABLES value: "system.*,INFORMATION_SCHEMA.*,information_schema.*" - name: COMPRESSION_FORMAT value: "tar" - name: COMPRESSION_LEVEL value: "1" envFrom: - secretRef: name: clickhouse-backup-credentials volumeMounts: - name: clickhouse-data mountPath: /var/lib/clickhouse resources: requests: cpu: 100m memory: 256Mi limits: memory: 1Gi ``` Configuration notes: * **Image version** - Pin `altinity/clickhouse-backup` to a specific release compatible with your ClickHouse version. Check the [clickhouse-backup releases page](https://github.com/Altinity/clickhouse-backup/releases). If your cluster cannot pull from public registries, mirror the image to your internal registry first. * **`CLICKHOUSE_HOST: localhost`** - Because the sidecar runs in the same pod, it connects to ClickHouse over the pod's loopback interface. If your ClickHouse server only accepts TLS connections, set `CLICKHOUSE_PORT: "9440"` and add `CLICKHOUSE_SECURE: "true"` instead. * **`volumeMounts`** - The mount must reference the same volume and mount path (`/var/lib/clickhouse`) as the ClickHouse server container. Adjust the volume name (`clickhouse-data` in the example) to match your StatefulSet. * **`S3_FORCE_PATH_STYLE`** - Required for MinIO. For AWS S3, remove it together with `S3_ENDPOINT` and set the correct `S3_REGION`. * **`BACKUPS_TO_KEEP_LOCAL` / `BACKUPS_TO_KEEP_REMOTE`** - Enable automatic retention cleanup, so no separate cleanup job is required. See [Backup Strategy and Retention](#backup-strategy-and-retention) below for sizing guidance. * **`CLICKHOUSE_SKIP_TABLES`** - Excludes ClickHouse system tables from backups. The four MES databases are included automatically. After updating the StatefulSet, the ClickHouse pods restart with the sidecar. Verify the API is up: ```bash kubectl exec -n -c clickhouse-backup -- curl -s http://localhost:7171/backup/status ``` ### OpenShift Considerations * Under the default `restricted-v2` Security Context Constraint (SCC), OpenShift assigns an arbitrary non-root UID to all containers in the pod. Because the sidecar and the ClickHouse server run with the same UID and supplemental group, the sidecar can read the shared data volume without additional configuration. * Do not hardcode `runAsUser` or `fsGroup` values (such as `101`) outside the namespace's allowed UID range. If your ClickHouse deployment template pins a specific UID, either remove it or ask your cluster administrator to grant an appropriate SCC (for example, `nonroot-v2`) to the ClickHouse service account. * Do not create an OpenShift Route for port 7171. The backup API must remain internal to the cluster. ## Step 4: Expose the Backup API Inside the Cluster Create a ClusterIP Service so the CronJobs can reach the sidecar API: ```yaml apiVersion: v1 kind: Service metadata: name: clickhouse-backup namespace: spec: selector: : # Must match the ClickHouse pod labels ports: - name: backup-api port: 7171 targetPort: 7171 ``` !!! warning "Do Not Expose the API Externally" The clickhouse-backup API has no authentication by default and allows destructive operations such as restore and delete. Keep it as a ClusterIP Service, never expose it through an Ingress or Route, and consider: * Enabling basic authentication on the API by setting the `API_USERNAME` and `API_PASSWORD` environment variables on the sidecar. * Restricting access to port 7171 with a NetworkPolicy that only allows traffic from the backup CronJob pods. ## Step 5: Schedule Backups with CronJobs ### Backup Strategy and Retention The recommended strategy follows a weekly full plus daily incremental pattern: | Schedule | Task | Description | | --- | --- | --- | | `0 2 * * 0` | Full backup | Weekly full backup on Sundays at 2 AM | | `0 3 * * 1-6` | Incremental backup | Daily incremental backups Monday to Saturday at 3 AM | Table: Recommended backup schedule * **Full backups** capture the complete database state and serve as the base for incremental backups. * **Incremental backups** upload only the data parts added since the base backup, significantly reducing storage and backup time for large databases. Each incremental backup is created with `diff-from-remote` against the latest full backup. * **Retention** is handled by the sidecar: `BACKUPS_TO_KEEP_REMOTE=14` keeps two full weekly cycles (2 full + 12 incremental backups); `BACKUPS_TO_KEEP_LOCAL=2` keeps only the most recent local copies for fast recovery. Ensure your `clickhouse-data` persistent volume has enough free space for these local backups, as hard links will grow in size as original parts are merged or deleted over time. !!! warning "Incremental Chains and Retention" An incremental backup can only be restored while its base full backup still exists in remote storage. Always size `BACKUPS_TO_KEEP_REMOTE` to retain at least one complete cycle (full backup plus all its incrementals) beyond your recovery point requirements. ### Full Backup CronJob ```yaml apiVersion: batch/v1 kind: CronJob metadata: name: clickhouse-backup-full namespace: spec: schedule: "0 2 * * 0" concurrencyPolicy: Forbid successfulJobsHistoryLimit: 3 failedJobsHistoryLimit: 3 jobTemplate: spec: backoffLimit: 1 activeDeadlineSeconds: 21600 template: spec: restartPolicy: Never containers: - name: full-backup image: # For example, a mirrored curl or UBI-minimal image command: ["/bin/sh", "-ec"] args: - | API="http://clickhouse-backup:7171" NAME="full-$(date +%Y-%m-%d-%H-%M-%S)" wait_for_command() { while curl -sf "${API}/backup/status" | grep -q '"in progress"'; do sleep 30 done curl -sf "${API}/backup/status" | grep -q '"success"' } echo "Creating full backup ${NAME}" curl -sf -X POST "${API}/backup/create?name=${NAME}" wait_for_command echo "Uploading ${NAME} to remote storage" curl -sf -X POST "${API}/backup/upload/${NAME}" wait_for_command echo "Full backup ${NAME} completed" ``` ### Incremental Backup CronJob The incremental job determines the most recent full backup in remote storage and uses it as the base: ```yaml apiVersion: batch/v1 kind: CronJob metadata: name: clickhouse-backup-incremental namespace: spec: schedule: "0 3 * * 1-6" concurrencyPolicy: Forbid successfulJobsHistoryLimit: 3 failedJobsHistoryLimit: 3 jobTemplate: spec: backoffLimit: 1 activeDeadlineSeconds: 21600 template: spec: restartPolicy: Never containers: - name: incremental-backup image: command: ["/bin/sh", "-ec"] args: - | API="http://clickhouse-backup:7171" NAME="incremental-$(date +%Y-%m-%d-%H-%M-%S)" wait_for_command() { while curl -sf "${API}/backup/status" | grep -q '"in progress"'; do sleep 30 done curl -sf "${API}/backup/status" | grep -q '"success"' } BASE=$(curl -sf "${API}/backup/list/remote" \ | sed -n 's/.*"name":"\(full-[^"]*\)".*/\1/p' | tail -n 1) if [ -z "${BASE}" ]; then echo "No full backup found in remote storage; aborting" >&2 exit 1 fi echo "Creating incremental backup ${NAME} (base: ${BASE})" curl -sf -X POST "${API}/backup/create?name=${NAME}&diff-from-remote=${BASE}" wait_for_command echo "Uploading ${NAME} to remote storage" curl -sf -X POST "${API}/backup/upload/${NAME}" wait_for_command echo "Incremental backup ${NAME} completed" ``` !!! tip "SQL-Driven Automation Alternative" As an alternative to calling the REST API with `curl`, you can set `API_CREATE_INTEGRATION_TABLES: "true"` on the sidecar. The tool then creates the `system.backup_actions` and `system.backup_list` tables in ClickHouse, allowing CronJobs to trigger and monitor backups with `clickhouse-client` SQL statements. This is the pattern used in the official [clickhouse-backup Kubernetes examples](https://github.com/Altinity/clickhouse-backup/blob/master/Examples.md). ## Backup Sequencing with SQL Server ClickHouse backups are part of the wider MES Data Platform backup set, together with the SQL Server databases and SMB shares. For backups that you intend to restore together as a consistent set: * Start the SQL Server backups only after the `Work Queue Lag` counter in `System/Database/Kafka` reaches `0 Message(s)`. After the SQL Server backups complete, take the ClickHouse backups. For details, see [[operation-guide-clickhouse-backupandrestoreoverview]]. * For unattended nightly backups, exact alignment between SQL Server and ClickHouse backups is usually not achievable - and it is not required. Schedule the ClickHouse backups shortly after the SQL Server backup window and reconcile any gap after a restore by running [[operation-guide-data-processing]] for a time range that covers the backup window. For the complete consistency expectations and recommended restore order, see [[operation-guide-disaster-recovery]]. ## Restore Procedures All restore operations go through the same sidecar API. Before restoring into a production environment, follow the incident preparation steps in [[operation-guide-disaster-recovery]] (communicate the incident, freeze inbound changes, and restore SQL Server and SMB shares first). ### List Available Backups ```bash kubectl exec -n -c clickhouse-backup -- \ curl -s http://localhost:7171/backup/list/remote ``` Each line describes one remote backup, including its name, creation time, and size. ### Download and Restore a Backup 1. **Download the backup from remote storage:** ```bash curl -s -X POST "http://localhost:7171/backup/download/" ``` When you download an incremental backup, the required base backup parts are downloaded automatically. 2. **Restore the backup:** ```bash # Restore all databases (schema and data), replacing existing tables curl -s -X POST "http://localhost:7171/backup/restore/?rm=1" ``` 3. **Monitor progress** until the operation reports `success`: ```bash curl -s "http://localhost:7171/backup/status" ``` 4. **Clean up** the downloaded local copy after a successful restore: ```bash curl -s -X POST "http://localhost:7171/backup/delete/local/" ``` ### Granular Restore Options The restore endpoint supports query parameters for partial restores: ```bash # Restore a single database (for example, the ODS database of a system named MES) curl -s -X POST "http://localhost:7171/backup/restore/?table=MESODS.*&rm=1" # Restore a single table curl -s -X POST "http://localhost:7171/backup/restore/?table=MESODS.&rm=1" # Restore schema only (no data) curl -s -X POST "http://localhost:7171/backup/restore/?schema=1" ``` ### Post-Restore Steps for MES After restoring the ClickHouse databases: 1. Run [[operation-guide-data-processing]] for a time range covering the backup window, to reconcile messages that were still in flight when the backup was taken. 2. Ensure Message Bus notifications are flowing so Data Manager invalidates cached OData models, or restart Data Manager if needed. 3. Validate the recovery following the post-recovery validation checks in [[operation-guide-disaster-recovery]]. !!! note For custom IoT Event Definitions, events still in flight in Kafka when the ClickHouse backup was taken cannot be recovered through Data Processing. ## Monitoring and Health Checks * **Prometheus metrics** - The sidecar exposes metrics on `http://:7171/metrics`, including the status of the last create and upload operations and the number of local and remote backups. If your environment scrapes Prometheus metrics (see [[operation-guide-observability-monitoring]]), add this endpoint and alert on failed or missing backups. * **Backup freshness** - Alert when the newest remote backup is older than your backup interval (for example, older than 26 hours with a daily schedule). * **CronJob health** - Alert on failed Jobs, and review job logs: ```bash kubectl get jobs -n kubectl logs -n job/ ``` * **Sidecar logs:** ```bash kubectl logs -n -c clickhouse-backup ``` * **Restore tests** - Periodically test a full restore into a non-production environment. A backup that has never been restored is not a verified backup. ## Troubleshooting | Issue | Solution | | --- | --- | | Backup API not responding | Check the sidecar container status and logs; confirm the Service selector matches the ClickHouse pod labels. | | `can't connect to clickhouse` errors in the sidecar | Verify `CLICKHOUSE_USERNAME`/`CLICKHOUSE_PASSWORD` in the Secret, and the `CLICKHOUSE_PORT`/`CLICKHOUSE_SECURE` settings if TLS is enforced. | | S3 upload failures | Verify the endpoint, credentials, and bucket name; for MinIO, confirm `S3_FORCE_PATH_STYLE` is `true`; check network policies and egress rules. | | Sidecar cannot read data files (permission denied) | Confirm the sidecar mounts the same volume as ClickHouse and runs with the same UID/GID; on OpenShift, review the SCC assigned to the service account. | | ClickHouse data volume filling up | Reduce `BACKUPS_TO_KEEP_LOCAL`, and confirm uploads are succeeding so local copies are eligible for cleanup. | | Incremental backup fails with a missing base | The base full backup was removed from remote storage; run a full backup and review the retention settings. | | Restore fails on existing tables | Repeat the restore with `rm=1` to drop existing tables first, after confirming they can be replaced. | Table: Common issues and solutions ## Security Considerations * Store all credentials in Kubernetes Secrets (or an external secret manager); never in ConfigMaps or container images. * Use a dedicated ClickHouse backup user and dedicated S3 credentials scoped to the backup bucket. * Keep the backup API internal to the cluster, enable API basic authentication, and restrict access with NetworkPolicies. * Use TLS for the S3 endpoint and enable encryption at rest on the backup bucket. * Limit access to backups: they contain complete copies of production manufacturing data. ## Related Documentation * [[operation-guide-clickhouse-backupandrestoreoverview]] - Installable ClickHouse backup packages created with the MES Web API, for environment copies, migrations, and restores during installation. * [[operation-guide-disaster-recovery]] - Backup priorities, restore order, and recovery workflow for the MES Data Platform. * [[operation-guide-data-processing]] - Repopulate ClickHouse from SQL Server and reconcile in-flight data after a restore. * [Altinity clickhouse-backup Repository](https://github.com/Altinity/clickhouse-backup) - Source code, releases, configuration reference, and examples for the clickhouse-backup tool. * [ClickHouse Official Backup Documentation](https://clickhouse.com/docs/operations/backup) - Native ClickHouse backup and restore commands, storage targets, and limitations.