ClickHouse Database Backup and Restore#

This guide provides procedures for backing up and restoring ClickHouse databases in Critical Manufacturing MES.

Overview#

Critical Manufacturing MES provides tools to create backup deployment packages of existing ClickHouse databases. These packages are placed in a designated MES custom installation packages folder, which triggers the MES installation process to restore from the backup instead of setting up a clean database.

Database Structure#

As of MES version 11.0.0, the platform uses four ClickHouse databases (assuming a system name of MES):

Backup Procedure#

This section describes how to back up a ClickHouse database schema and download the resulting package using the MES Web API.

Prerequisites#

• An MES user account with the SysAdministrators role.
• Access to the MES Web API.
• A tool for making HTTP requests (e.g., curl, Postman, or PowerShell).

Step 1: Obtain Authentication Credentials#

You can authenticate your API requests using a Bearer Token or Basic Authentication with a Personal Access Token (PAT).

Option A: Bearer Token (Session-based)#

This method is convenient for manual, one-off tasks. After logging in to the MES GUI, you can retrieve the Bearer token:

1. Open your browser's developer tools (F12).
2. Navigate to the Network tab.
3. Make any request in the MES GUI and copy the Authorization header value, which will be in the format Bearer <token>.

Option B: Basic Authentication (For Automation)#

This method is ideal for automation scripts. You will need a Personal Access Token (PAT) created from your user account profile page (Administration > Security > Your Account Profile).

1. Format your string as username:your_pat_token.

2. Encode that string in Base64.

   PowerShellBash

   [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("username:your_pat_token"))
   

   echo -n "username:your_pat_token" | base64
   

The output will be a long base64-encoded string.

Step 2: Create Database Backup#

Initiate the backup process by sending a POST request. This operation is asynchronous; the server will acknowledge the request immediately while the backup runs in the background.

Endpoint#

POST {environment-url}/environment/clickhouse/backup/{database-name}

Request#

You may use PowerShell to invoke the backup API as follows:

curl --location "https://mesintegration.dev/environment/clickhouse/backup/MESCDM" `
     --request POST `
     --header "Authorization: Bearer {your-token-here}"

curl --location "https://mesintegration.dev/environment/clickhouse/backup/MESCDM" `
     --request POST `
     --header "Authorization: Basic {your-base64-encoded-credentials}"

Response#

The API may return one of the following responses:

Step 3: Download Backup Package#

Use the GUID to check the status and download the package. Because the process is asynchronous, you may need to "poll" (request repeatedly) until the file is ready.

Endpoint#

GET {environment-url}/environment/clickhouse/backup/{guid}

Request#

You may use PowerShell to invoke the download backup API as follows:

curl.exe --location "https://mesintegration.dev/environment/clickhouse/backup/{backup-guid}" `
     --remote-header-name --remote-name `
     --request GET `
     --header "Authorization: Bearer {your-token-here}"

curl.exe --location "https://mesintegration.dev/environment/clickhouse/backup/{backup-guid}" `
     --remote-header-name --remote-name `
     --request GET `
     --header "Authorization: Basic {your-base64-encoded-credentials}"

Response#

The API may return one of the following responses:

Restore Procedures#

Automated Restore (During Installation)#

The MES installer can automatically detect and restore from backup packages if they are placed in the correct location with the required file names.

1. Ensure correct naming: The downloaded backup packages need to follow a naming convention. For MES v11.2.0, the filenames are:

   • MES main database: Cmf.ClickHouse.FullBackup.Online.11.2.0.zip
   • CDM database: Cmf.ClickHouse.FullBackup.CDM.11.2.0.zip
   • DWH database: Cmf.ClickHouse.FullBackup.DWH.11.2.0.zip
   • ODS database: Cmf.ClickHouse.FullBackup.ODS.11.2.0.zip

   Full Backups with the System Name

   To support restoring different environments from the same installation packages directory, the installation logic now prioritizes backups that include the system name in their filename.

   The installer will search for backup files in the following 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 running version 11.2.0, the installer will first search for MES.Cmf.ClickHouse.FullBackup.Online.11.2.0.zip. If this file is not found, it will then look for the generic filename: Cmf.ClickHouse.FullBackup.Online.11.2.0.zip.

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

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

3. Run the MES installation process. The installer will automatically detect and restore from the backup packages.

Manual Restore#

For manual restoration outside of the installation process, follow the steps below:

1. Extract the backup package:

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

2. Connect to ClickHouse:

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

3. Execute restore commands:

   Follow the ClickHouse backup restoration procedures as documented in the official ClickHouse documentation ⧉.

Best Practices#

• Regular Backups: Schedule regular backups of all four databases.
• Verify Backups: Test restore procedures in a non-production environment.
• Storage Management: Monitor backup storage usage and implement retention policies.
• Security: Protect backup files and limit access to authorized personnel.
• Documentation: Maintain records of backup schedules and restore procedures.

Troubleshooting#

Related Documentation#

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