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#

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

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 Scripts)#

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. Combine your username and the PAT with a colon: <useraccount>:<pat_token>.

2. Encode this value in base64. You can use a command line tool like this:

   echo "username:replace_by_your_pat" | base64
   

   The output will be a long base64-encoded string.

Step 2: Create Database Backup#

Make a POST request to the ClickHouse API to initiate the backup process. This is an asynchronous operation.

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

curl --location 2https://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: A successful request returns a 200 OK status and a GUID that represents the backup operation.

Step 3: Download Backup Package#

Use the GUID from the previous step to download the completed backup package.

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

Example:

curl --location "https://mesintegration.dev/environment/clickhouse/backup/{backup-guid}" \
     --request GET \
     --header "Authorization: Bearer {your-token-here}" \
     --output <backup_name>.zip

Response Codes:

• 200 OK: Backup is ready, and the file download begins.
• 204 No Content: Backup is still being generated or does not exist.
• 400 Bad Request: Invalid request (e.g., missing or invalid GUID).
• 500 Internal Server Error: An error occurred during the backup process.

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.1.5, the filenames are:

   • MES main database: Cmf.ClickHouse.FullBackup.11.1.5.zip
   • CDM database: Cmf.ClickHouse.CDMFullBackup.11.1.5.zip
   • DWH database: Cmf.ClickHouse.DWHFullBackup.11.1.5.zip
   • ODS database: Cmf.ClickHouse.ODSFullBackup.11.1.5.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.11.1.5.zip
   ├── Cmf.ClickHouse.CDMFullBackup.11.1.5.zip
   ├── Cmf.ClickHouse.DWHFullBackup.11.1.5.zip
   └── Cmf.ClickHouse.ODSFullBackup.11.1.5.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 ⧉