ClickHouse DB::Exception: Cannot parse backup settings

The "DB::Exception: Cannot parse backup settings" error in ClickHouse is raised when a BACKUP or RESTORE command includes settings that ClickHouse cannot interpret. The CANNOT_PARSE_BACKUP_SETTINGS error code means the SETTINGS clause of the backup or restore statement contains an unrecognized setting name, an invalid value, or a syntax error that prevents the command from being parsed.

Impact

This error prevents the backup or restore operation from starting:

• No backup is created and no data is restored.
• Scheduled backup jobs will fail, potentially leaving your disaster recovery plan with gaps.
• If the error is in a restore command, recovery from a backup is blocked until the syntax is corrected.
• The underlying data is not affected -- this is a command-parsing error, not a data corruption issue.

Common Causes

1. Misspelled setting name -- a typo in a backup setting like base_backup or password (e.g., base_bakcup or pasword).
2. Invalid setting value -- providing a string where a number is expected, or a value that is out of the accepted range.
3. Using query-level settings in the backup clause -- backup settings are a separate namespace from general query settings. Putting max_threads or similar in the BACKUP SETTINGS clause will fail.
4. Syntax error in the SETTINGS clause -- missing equals sign, extra comma, or incorrect quoting.
5. Version mismatch -- using a backup setting that was introduced in a newer ClickHouse version than the one running.
6. Incorrect backup destination syntax -- the backup path or S3 URL in the TO clause is malformed.

Troubleshooting and Resolution Steps

1. Check the exact error message. ClickHouse usually reports which setting or token it could not parse.

2. Verify the setting names. The common backup settings include:

   BACKUP TABLE my_table TO Disk('backups', 'my_backup')
   SETTINGS
       base_backup = Disk('backups', 'base_backup'),
       password = 'my_secret',
       structure_only = 0,
       async = 0;
   

   Confirm each setting name is spelled correctly.

3. Check available backup settings in the documentation or by examining the error. Key backup settings include:

   • base_backup -- reference to a base backup for incremental backups
   • password -- encryption password for the backup
   • structure_only -- backup only table structures without data
   • async -- run the backup asynchronously
   • id -- custom identifier for the backup operation

4. Verify the backup destination syntax. For disk-based backups:

   BACKUP TABLE my_db.my_table TO Disk('backup_disk', 'backup_name');
   

   For S3:

   BACKUP TABLE my_db.my_table TO S3('https://bucket.s3.amazonaws.com/path', 'access_key', 'secret_key');
   

5. Check your ClickHouse version. Some backup settings are only available in newer versions:

   SELECT version();
   

   Compare against the release notes for the settings you are trying to use.

6. Test the command without SETTINGS first to confirm the basic syntax is correct:

   BACKUP TABLE my_db.my_table TO Disk('backups', 'test_backup');
   

   Then add settings one by one to identify which one causes the error.

7. Check quoting. String values in SETTINGS should be enclosed in single quotes:

   -- Correct
   SETTINGS password = 'my_password'
   -- Wrong
   SETTINGS password = my_password
   

Best Practices

• Keep backup commands in version-controlled scripts so that changes are reviewed before deployment.
• Test backup and restore commands on a staging environment before using them in production.
• Use the system.backups table to monitor backup status and catch issues early:

  SELECT * FROM system.backups ORDER BY start_time DESC LIMIT 5;
  

• Pin your ClickHouse version in backup scripts and update them when upgrading, in case settings change between versions.
• Document your backup strategy including the exact commands, settings, and schedules.

Frequently Asked Questions

Q: Where can I find a list of all valid backup settings?
A: The official ClickHouse documentation lists backup settings under the BACKUP and RESTORE statements section. You can also check the system.backups table for examples of previously successful backup configurations.

Q: Can I use general query settings like max_threads in the BACKUP SETTINGS clause?
A: No. Backup settings are a separate namespace. To control parallelism or other query-level behavior during a backup, set them as session-level settings before running the BACKUP command: SET max_threads = 4; BACKUP TABLE ....

Q: What happens if I misspell a setting name?
A: ClickHouse raises the CANNOT_PARSE_BACKUP_SETTINGS error and the backup does not start. Double-check spelling against the documentation.

Q: Does this error affect the data in my tables?
A: No. This is a command-parsing error that prevents the backup or restore from starting. Your existing table data is completely unaffected.

Q: How do I set up incremental backups?
A: Use the base_backup setting to reference a previous backup:

BACKUP TABLE my_db.my_table TO Disk('backups', 'incremental_2024')
SETTINGS base_backup = Disk('backups', 'full_2024');

Make sure the base backup path is correct and accessible.