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 ⧉ tool deployed as a sidecar container, Kubernetes CronJobs for scheduling, and S3-compatible object storage for off-site retention.

When to Use This Procedure#

Critical Manufacturing MES already provides an application-level backup mechanism through the MES Web API, described in ClickHouse Database Backup and Restore. The two approaches are complementary:

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 Disaster Recovery.

Database Structure#

As of MES version 11.0.0, the platform uses four ClickHouse databases. Their names are derived from the MES System Name — the example below assumes a system named MES:

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:

graph LR
    subgraph pod ["ClickHouse Pod (StatefulSet)"]
        CH[ClickHouse Server]
        CB[clickhouse-backup sidecar]
        PV[("Shared data volume<br>/var/lib/clickhouse")]
        CH --- PV
        CB --- PV
    end
    CJ["CronJobs<br>Full and incremental backups"] -- "REST API (7171)" --> CB
    CB <--> S3[("S3-Compatible Storage<br>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).

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 Accounts and Security.

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:

mc alias set backup https://<s3-endpoint> <access-key> <secret-key>
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:

apiVersion: v1
kind: Secret
metadata:
  name: clickhouse-backup-credentials
  namespace: <clickhouse-namespace>
type: Opaque
stringData:
  S3_ACCESS_KEY: "<access-key>"
  S3_SECRET_KEY: "<secret-key>"
  CLICKHOUSE_USERNAME: "<clickhouse-user>"
  CLICKHOUSE_PASSWORD: "<clickhouse-password>"

Apply it:

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:

# Add under spec.template.spec.containers of the ClickHouse StatefulSet
- name: clickhouse-backup
  image: altinity/clickhouse-backup:<version>
  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://<s3-endpoint>"
    - 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 ⧉. 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 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:

kubectl exec -n <clickhouse-namespace> <clickhouse-pod> -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:

apiVersion: v1
kind: Service
metadata:
  name: clickhouse-backup
  namespace: <clickhouse-namespace>
spec:
  selector:
    <label-key>: <label-value>   # Must match the ClickHouse pod labels
  ports:
    - name: backup-api
      port: 7171
      targetPort: 7171

Step 5: Schedule Backups with CronJobs#

Backup Strategy and Retention#

The recommended strategy follows a weekly full plus daily incremental pattern:

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.

Full Backup CronJob#

apiVersion: batch/v1
kind: CronJob
metadata:
  name: clickhouse-backup-full
  namespace: <clickhouse-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: <image-with-curl>   # 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:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: clickhouse-backup-incremental
  namespace: <clickhouse-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: <image-with-curl>
              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"

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 ClickHouse Database Backup and Restore.
• 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 Data Processing for a time range that covers the backup window.

For the complete consistency expectations and recommended restore order, see 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 Disaster Recovery (communicate the incident, freeze inbound changes, and restore SQL Server and SMB shares first).

List Available Backups#

kubectl exec -n <clickhouse-namespace> <clickhouse-pod> -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:

   curl -s -X POST "http://localhost:7171/backup/download/<backup-name>"
   

   When you download an incremental backup, the required base backup parts are downloaded automatically.

2. Restore the backup:

   # Restore all databases (schema and data), replacing existing tables
   curl -s -X POST "http://localhost:7171/backup/restore/<backup-name>?rm=1"
   

3. Monitor progress until the operation reports success:

   curl -s "http://localhost:7171/backup/status"
   

4. Clean up the downloaded local copy after a successful restore:

   curl -s -X POST "http://localhost:7171/backup/delete/local/<backup-name>"
   

Granular Restore Options#

The restore endpoint supports query parameters for partial restores:

# Restore a single database (for example, the ODS database of a system named MES)
curl -s -X POST "http://localhost:7171/backup/restore/<backup-name>?table=MESODS.*&rm=1"

# Restore a single table
curl -s -X POST "http://localhost:7171/backup/restore/<backup-name>?table=MESODS.<table-name>&rm=1"

# Restore schema only (no data)
curl -s -X POST "http://localhost:7171/backup/restore/<backup-name>?schema=1"

Post-Restore Steps for MES#

After restoring the ClickHouse databases:

1. Run 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 Disaster Recovery.

Monitoring and Health Checks#

• Prometheus metrics - The sidecar exposes metrics on http://<pod>: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 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:

  kubectl get jobs -n <clickhouse-namespace>
  kubectl logs -n <clickhouse-namespace> job/<job-name>
  

• Sidecar logs:

  kubectl logs -n <clickhouse-namespace> <clickhouse-pod> -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#

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#

• ClickHouse Database Backup and Restore - Installable ClickHouse backup packages created with the MES Web API, for environment copies, migrations, and restores during installation.
• Disaster Recovery - Backup priorities, restore order, and recovery workflow for the MES Data Platform.
• Data Processing - Repopulate ClickHouse from SQL Server and reconcile in-flight data after a restore.
• Altinity clickhouse-backup Repository ⧉ - Source code, releases, configuration reference, and examples for the clickhouse-backup tool.
• ClickHouse Official Backup Documentation ⧉ - Native ClickHouse backup and restore commands, storage targets, and limitations.