ClickHouse Database Backup and Restore#

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

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 ↗ process migrates existing environments to new infrastructure. It also copies production data into staging environments for acceptance testing, troubleshooting, or training.

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:

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.

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 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 Data Processing for a time range that covers the backup window.

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 ↗.

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.

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.

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

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.

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.

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.

   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.

   <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 ↗.

Manual Restore#

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

1. Extract the backup package:

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

2. Connect to ClickHouse:

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

3. Run the restore commands:

   Follow the ClickHouse backup restore procedures in the official ClickHouse documentation ↗.

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.

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

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 Data Set Data Access Security.
• Post-Restore Reconciliation - After you restore the SQL Server and ClickHouse backups, run 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#

Table: Backup and restore issues and solutions.

Related Documentation#

• ClickHouse Official Backup Documentation ↗
• MES Copy Environment Guide ↗
• MES Web API Guide ↗