ClickHouse DB::Exception: Backup engine not found

The "DB::Exception: Backup engine not found" error in ClickHouse occurs when a BACKUP or RESTORE command references a destination type that ClickHouse does not recognize. The BACKUP_ENGINE_NOT_FOUND error code means the specified backup engine name (such as Disk, S3, or File) is either misspelled, not supported in your ClickHouse version, or the required disk is not configured.

Impact

The backup or restore operation fails immediately without transferring any data. Your existing data and any previous backups remain unaffected, but you cannot proceed with the intended backup workflow until the destination is properly configured.

Common Causes

1. The backup destination type is misspelled (for example, Disc instead of Disk)
2. The named disk referenced in Disk('my_disk', ...) is not defined in the ClickHouse storage configuration
3. Using a backup engine that is not available in your ClickHouse version or build
4. The S3 backup destination is used but the ClickHouse build does not include S3 support
5. Missing or incorrect configuration in storage_configuration for the disk used as a backup target

Troubleshooting and Resolution Steps

1. Verify the correct syntax for the backup command. The supported destination types are Disk, S3, and File:

   BACKUP TABLE my_table TO Disk('backups', 'my_backup/');
   

2. Check that the named disk exists in your storage configuration:

   SELECT name, path, type FROM system.disks;
   

   If the disk named backups does not appear, you need to define it.

3. Add a backup disk to your storage configuration (typically in config.xml or a file in config.d/):

   <clickhouse>
       <storage_configuration>
           <disks>
               <backups>
                   <type>local</type>
                   <path>/var/lib/clickhouse/backups/</path>
               </backups>
           </disks>
       </storage_configuration>
       <backups>
           <allowed_disk>backups</allowed_disk>
           <allowed_path>/var/lib/clickhouse/backups/</allowed_path>
       </backups>
   </clickhouse>
   

4. For S3 backup destinations, ensure S3 is configured as a disk:

   <disks>
       <s3_backups>
           <type>s3</type>
           <endpoint>https://my-bucket.s3.amazonaws.com/backups/</endpoint>
           <access_key_id>KEY</access_key_id>
           <secret_access_key>SECRET</secret_access_key>
       </s3_backups>
   </disks>
   

5. Restart ClickHouse after adding disk configuration, then retry:

   sudo systemctl restart clickhouse-server
   

6. Verify your ClickHouse version supports the backup engine you want to use:

   SELECT version();
   

   The BACKUP/RESTORE functionality is available from ClickHouse 22.3 onward.

Best Practices

• Define a dedicated backup disk in your storage configuration and add it to the allowed_disk list.
• Use descriptive disk names like backups or s3_backups to make backup commands self-documenting.
• Test your backup configuration in a non-production environment before relying on it for production data.
• Keep your ClickHouse version up to date to benefit from the latest backup engine improvements and bug fixes.

Frequently Asked Questions

Q: What backup destination types does ClickHouse support?
A: ClickHouse supports Disk (for local or remote disks defined in storage configuration), S3 (direct S3 syntax), and File (for local file paths, if allowed). The most common approach is to use Disk with a named disk.

Q: Can I back up directly to S3 without defining a disk?
A: In some ClickHouse versions, you can use BACKUP ... TO S3('https://bucket/path', 'key', 'secret'). However, defining an S3 disk in the configuration is more maintainable and keeps credentials out of SQL statements.

Q: Why does my backup work on one node but fail on another?
A: Each node needs the same disk configuration. If the backup disk is only defined on one node, other nodes will report BACKUP_ENGINE_NOT_FOUND. Ensure all nodes in the cluster share the same storage configuration.