---
alias: operation-guide-clickhouse-backupandrestoreoverview
tags:
- database
- ClickHouse
- backup
- restore
description: "Back up and restore ClickHouse databases within Critical Manufacturing MES using API requests and automated installation processes"
---

# ClickHouse Database Backup and Restore

This guide describes how to back up and restore ClickHouse databases in Critical Manufacturing MES.

!!! info "Version Compatibility"
    This procedure applies to MES version 12.x and later. For earlier versions, see the [MES v11.0.0 Infrastructure Migration Guide](https://developer.criticalmanufacturing.com/explore/guides/migration/version-specific/1100/infrastructure/).

## Overview

The MES provides tools to create backup deployment packages of existing ClickHouse databases. You place these packages in the MES custom installation packages folder, and the MES installation then restores the databases from the backup instead of setting up clean databases.

A backup package holds both the control data of the database and the data itself. Control data covers the users, roles, grants, row policies, quotas, and settings profiles of the system. The restore process creates them again on the target system, so you do not need to grant permissions manually after a restore.

The [copy environment](https://developer.criticalmanufacturing.com/explore/guides/deployment/copy_environment/) process migrates existing environments to new infrastructure. It also copies production data into staging environments for acceptance testing, troubleshooting, or training.

!!! note
    If the SQL Server backups exist, but the ClickHouse backups do not, and you can no longer create matching backups, follow the procedure in [[operation-guide-clickhouse-recreateclickhousebackups]].

## Database Structure

{% include-markdown 'includes/tech/mes-clickhouse-databases.md' %}

## Package Contents

Each backup package holds three parts:

- The database backup, as a `.zip` file.
- A `<database_name>RowPolicies.sql` script, which creates the row policies of the database again.
- A `manifest.xml` file, which describes the steps that the installer runs to restore the package.

The backup generates the row policies script from the row policies that exist when the backup runs. The script uses a token in place of the name of the source system, so you can restore the same package into a system with a different name.

!!! info
    Row policies restrict the rows that a Data Platform query returns. For more information, see [[operation-guide-data-set-data-access-security|Data Set Data Access Security]].

## Backup Procedure

### Prerequisites

- An MES user account with the `SysAdministrators` role.
- Access to the MES Web API.
- A tool for HTTP requests, such as cURL or Postman.
- Access to the SQL Server backup process for the same environment.
- Access to the System Monitoring page, to verify the `Work Queue Lag` and `ClickHouse Work Queue Lag` counters in `System/Database/Kafka`.

### Backup Sequencing

Back up SQL Server first and ClickHouse after it, so that both backups hold the same data:

1. Make sure that the `Work Queue Lag` counter in `System/Database/Kafka` shows `0 Message(s)`. For more information on System Monitoring, see [[user-guide-system-monitoring]].
2. After an upgrade to version 12.0.0, wait until the `ClickHouse Work Queue Lag` counter shows `0 Message(s)`. While it is above 0, HouseKeeper is still rebuilding ClickHouse tables, and a table can hold only part of its rows. It shows `N/A Message(s)` when there is no migration message to handle. If it shows `N/A Message(s)` soon after an upgrade, check the HouseKeeper logs before you back up.
3. Run the SQL Server backups.
4. After the SQL Server backups complete, back up ClickHouse as described in the following steps.

Even with this sequencing, some messages may still be in flight between the SQL Server backup and the ClickHouse backup. SQL Server may already mark these messages as processed before they reach ClickHouse. To reconcile them after a restore, run [[operation-guide-data-processing]] for a time range that covers the backup window.

!!! warning
    For custom IoT Event Definitions, the events that are still in flight in Kafka when the ClickHouse backup runs are lost, and Data Processing cannot recover them later.

### Step 1: Authenticate API Requests

Authenticate your requests with a Bearer token, or with Basic Authentication and a Personal Access Token (PAT). For instructions on how to get credentials and build the `Authorization` header, see [MES services through HTTP API](https://developer.criticalmanufacturing.com/explore/guides/customizations/business/invokemeshttpapi/).

In the following examples, replace `{authentication-header}` with the authentication scheme and credentials that apply to your environment.

### Step 2: Create Database Backup

Make a `POST` request to the ClickHouse API to start the backup. The operation is asynchronous.

**Endpoint:** `POST {environment-url}/environment/clickhouse/backup/{database-name}`

Replace `{environment-url}` with the base URL of your MES instance, for example, `https://mesintegration.dev/`. Replace `{database-name}` with the name of the database to back up. For a system named `MES`, the database names are `MES`, `MESCDM`, `MESODS`, and `MESDWH`. For more information, see [Database Structure](#database-structure).

Each request backs up one database. For a full backup, make one request for each of the four databases, and repeat steps 3 and 4 for each backup ID.

```bash
curl --location "https://mesintegration.dev/environment/clickhouse/backup/MESCDM" \
     --request POST \
     --header "Authorization: {authentication-header}"
```

**Response:** A successful request returns a `200 OK` status and a backup ID. The backup ID is the time at which the backup started, for example, `20260817T120000Z`.

### Step 3: Check Backup Status

Use the backup ID returned in the previous step to check whether the backup is complete. Because the backup runs asynchronously, repeat this request until the backup is complete, and only then download the package.

**Endpoint:** `GET {environment-url}/environment/clickhouse/backup/{backup-id}/status`

```bash
curl --location "https://mesintegration.dev/environment/clickhouse/backup/20260817T120000Z/status" \
     --request GET \
     --header "Authorization: {authentication-header}"
```

### Step 4: Download Backup Package

After the backup status confirms that the backup is complete, use the backup ID to download the backup package.

**Endpoint:** `GET {environment-url}/environment/clickhouse/backup/{backup-id}/download`

Use `--remote-header-name` and `--remote-name` so cURL saves the package with the file name returned by the Environment Manager.

```bash
curl --location "https://mesintegration.dev/environment/clickhouse/backup/20260817T120000Z/download" \
     --remote-header-name --remote-name \
     --request GET \
     --header "Authorization: {authentication-header}"
```

**Response Codes:**

- `200 OK` - The backup is ready, and the download starts.
- `204 No Content` - The backup is still being generated. Wait and check the backup status again.
- `400 Bad Request` - The backup ID is not in a valid format.
- `404 Not Found` - The requested backup ID does not exist.
- `500 Internal Server Error` - An error occurred during the backup process.

!!! warning "Single-Use Download Link"
    The server deletes the backup file right after it returns a successful `200 OK` response. Save the returned stream at once, because you cannot download it again.

## Restore Procedures

The restore process runs these steps in order:

1. Restores the database from the backup file.
2. Creates the ClickHouse roles of the target system.
3. Runs the row policies script, with the name of the target system in place of the system name token.
4. Runs the database migrations.

You do not need to create roles or grant permissions manually.

### Automated Restore (During Installation)

The MES installer detects the backup packages and restores from them when you place them in the correct folder with the required file names.

1. **Check the file names:** The downloaded backup packages must follow a naming convention. For version 12.0.0, the file names are:

    - **MES main database** - `Cmf.ClickHouse.FullBackup.Online.12.0.0.zip`
    - **CDM database** - `Cmf.ClickHouse.FullBackup.CDM.12.0.0.zip`
    - **DWH database** - `Cmf.ClickHouse.FullBackup.DWH.12.0.0.zip`
    - **ODS database** - `Cmf.ClickHouse.FullBackup.ODS.12.0.0.zip`

    To restore different environments from the same installation packages folder, add the system name to the start of the file name. The installer searches for backup files in this order:

    1. System-specific - `<SystemName>.Cmf.ClickHouse.FullBackup.<type>.<version>.zip`
    2. Generic - `Cmf.ClickHouse.FullBackup.<type>.<version>.zip`

    For example, for a system named `MES` on version `12.0.0`, the installer first searches for `MES.Cmf.ClickHouse.FullBackup.Online.12.0.0.zip`. If it does not find this file, it looks for `Cmf.ClickHouse.FullBackup.Online.12.0.0.zip`.

    !!! warning "Do Not Rename Packages"
        Apart from the system name prefix, change only `12.0.0` to the version of your MES. Do not rename the packages in any other way.

2. **Place the packages in the correct folder:** Place all four database backup packages in the MES custom installation packages folder.

    ```text
    <MES Customization Packages Folder>
    ├── Cmf.ClickHouse.FullBackup.Online.12.0.0.zip
    ├── Cmf.ClickHouse.FullBackup.CDM.12.0.0.zip
    ├── Cmf.ClickHouse.FullBackup.DWH.12.0.0.zip
    └── Cmf.ClickHouse.FullBackup.ODS.12.0.0.zip
    ```

3. Run the MES installation process. The installer detects the backup packages and restores from them.

When you restore a backup of a version earlier than 12.0.0 on version 12.0.0, HouseKeeper rebuilds the restored ClickHouse tables in the background after the installation. For more information, see [ClickHouse Migration Process](https://developer.criticalmanufacturing.com/explore/guides/migration/version-specific/1200/data-platform/clickhouse_migration_process/).

### Manual Restore

To restore a backup outside the installation process, follow these steps:

1. **Extract the backup package:**

    ```bash
    unzip backup-package.zip -d backup-contents/
    ```

2. **Connect to ClickHouse:**

    ```bash
    clickhouse-client --host {clickhouse-host} --port 9000
    ```

3. **Run the restore commands:**

    Follow the ClickHouse backup restore procedures in the [official ClickHouse documentation](https://clickhouse.com/docs/concepts/features/backup-restore/overview).

4. **Create the roles and row policies again:**

    Before you run the `<database_name>RowPolicies.sql` script extracted from the package, replace the `$(Product.SystemName)` token with the name of the target system. Then run the script.

    !!! warning "Row Policies After a Manual Restore"
        If you skip this step, the database has no row policies, and every Data Platform query returns every row. For more information, see [[operation-guide-data-set-data-access-security|Data Set Data Access Security]].

## Backup and Restore With a Replicated Cluster

A backup holds the data and the schema of the ClickHouse databases. It does not hold the server configuration, so the cluster definition and the ClickHouse Keeper settings are not part of the backup. Set up the cluster on the target server before you restore.

Access objects such as users, roles, and named collections are part of the backup when they are created through SQL. Objects defined in the XML server configuration are not.

### Restoring a Single-Instance Backup Into a Cluster

You can restore a backup taken from a single instance into a replicated cluster. A manually restored backup keeps the table engines of its source. To enable High Availability on the new environment, the conversion to the cluster must run. For more information, see [[operation-guide-clickhouse-highavailability#how-the-migration-runs|ClickHouse High Availability]].

The MES runs this automatically. After the installation restores the backup packages, it runs the ClickHouse database packages again, and they convert the restored databases and tables to the cluster. A backup and restore lands on a replicated cluster even when you do not change the version.

### Restoring Into a Cluster That Already Holds Data

The restore first creates the databases, then drops the existing tables, and only then creates the tables again and restores the data.

!!! warning "Warning"
    Restoring with `restore_schema_on_cluster` set drops tables across the whole cluster. Use it only on an empty cluster.

### Storage Policies

The MES uses the `default` ClickHouse storage policy in every supported deployment, on a single instance and on a cluster. A restore into an environment with a different cluster configuration works as long as both environments keep that policy.

If you define a custom storage policy on either environment, the restore can fail. Match the storage policies on the source and the target before you restore.

### Moving From ClickHouse Cloud to On-Premises

Back up the Cloud databases to object storage, then restore from that object storage on the target servers. When a backup is not available, extract the data from ClickHouse Cloud and load it into the on-premises servers.

## Best Practices

- **Regular Backups** - Schedule regular backups of all four databases.
- **Backup Sequencing** - Start the SQL Server backups only after the `Work Queue Lag` counter in `System/Database/Kafka` reaches `0 Message(s)`. After an upgrade to version 12.0.0, also wait until the `ClickHouse Work Queue Lag` counter reaches `0 Message(s)`. After the SQL Server backups complete, back up ClickHouse.
- **Verify Backups** - Test the restore procedures in a non-production environment.
- **Verify Row Policies** - After a restore, list the row policies of each database to confirm that the restore created them. For more information, see [[operation-guide-data-set-data-access-security|Data Set Data Access Security]].
- **Post-Restore Reconciliation** - After you restore the SQL Server and ClickHouse backups, run [[operation-guide-data-processing]] for a time range that covers the backup window. This reconciles the messages that were still in flight when the backups ran.
- **Storage Management** - Monitor the storage used by backups and set retention policies.
- **Security** - Protect backup files and limit access to authorized personnel.
- **Documentation** - Keep records of backup schedules and restore procedures.

## Troubleshooting

| Issue | Solution |
|-------|----------|
| Backup request fails | Check that the user has the `SysAdministrators` role and that the token is valid. |
| Status or download returns `204 No Content` | The backup is still being generated. Wait and check the backup status again. |
| Download returns `400 Bad Request` | Check that the backup ID is correct and in the correct format. |
| Download returns `500 Internal Server Error` | Check the status and the logs of the MES `EnvironmentManager` and ClickHouse services for errors. |
| Download times out | Increase the timeout of the download client. Large databases take longer to download. |
| Restore fails | Check the ClickHouse logs and the integrity of the backup file. |
| `ClickHouse Work Queue Lag` is above `0 Message(s)` after an upgrade to version 12.0.0 | HouseKeeper is still rebuilding the ClickHouse tables. Wait until the counter reaches `0 Message(s)` before you back up. For more information, see [ClickHouse Migration Process](https://developer.criticalmanufacturing.com/explore/guides/migration/version-specific/1200/data-platform/clickhouse_migration_process/). |
| `ClickHouse Work Queue Lag` shows `N/A Message(s)` soon after an upgrade to version 12.0.0 | HouseKeeper may not be handling the migration messages. Check the HouseKeeper logs for migration errors, and do not back up until the counter reaches `0 Message(s)`. |

Table: Backup and restore issues and solutions.

## Related Documentation

- [ClickHouse Official Backup Documentation](https://clickhouse.com/docs/concepts/features/backup-restore/overview)
- [MES Copy Environment Guide](https://developer.criticalmanufacturing.com/explore/guides/deployment/copy_environment/)
- [MES Web API Guide](https://developer.criticalmanufacturing.com/explore/guides/customizations/business/invokemeshttpapi/)