ClickHouse DB::Exception: Wrong backup settings

The "DB::Exception: Wrong backup settings" error is raised when a BACKUP or RESTORE command includes settings that ClickHouse does not recognize or that have invalid values. The WRONG_BACKUP_SETTINGS error code tells you that the command syntax is correct but the settings block contains a problem -- a misspelled setting name, an unsupported value, or a combination of settings that conflicts.

Impact

The backup or restore command is rejected before any work begins. No data is written or read. While this is not a data loss risk, it blocks your backup pipeline until the settings are corrected. In automated environments, this can cause repeated failures if the script is not updated.

Common Causes

1. Misspelled setting name -- a typo like allow_backup_overwiting instead of allow_backup_overwriting.
2. Using a setting that does not exist in the current version -- a setting introduced in a newer ClickHouse version used on an older server.
3. Invalid value type -- passing a string where an integer is expected, or vice versa.
4. Conflicting settings -- specifying two settings that are mutually exclusive or contradictory.
5. Using query-level settings in the backup SETTINGS clause -- some ClickHouse settings apply to queries but are not valid in backup/restore context.

Troubleshooting and Resolution Steps

1. Read the error message carefully. ClickHouse reports which setting is problematic:

   DB::Exception: Wrong backup settings: Unknown setting 'allow_backup_overwiting'. Did you mean 'allow_backup_overwriting'?
   

2. Check the spelling of all settings in your command:

   BACKUP DATABASE my_db TO Disk('backups', 'my_backup')
   SETTINGS
       allow_backup_overwriting = 1,
       base_backup = Disk('backups', 'full_backup');
   

3. Verify settings are available in your ClickHouse version:

   SELECT version();
   

   Then consult the ClickHouse documentation for your specific version to confirm the setting exists.

4. Review the valid backup settings. The most commonly used settings include:

   -- Common BACKUP settings
   SETTINGS
       base_backup = Disk('backups', 'base'),            -- for incremental backups
       allow_backup_overwriting = 0,                      -- allow overwriting existing backups
       compression_method = 'lz4',                        -- zstd, lz4, none
       compression_level = -1,                            -- compression level
       password = '',                                     -- encryption password
       structure_only = 0,                                -- backup schema without data
       async = 0                                          -- run backup asynchronously
   

5. For RESTORE settings, check the valid options:

   -- Common RESTORE settings
   SETTINGS
       allow_non_empty_tables = 0,                        -- restore into tables that have data
       structure_only = 0,                                -- restore schema only
       password = '',                                     -- decryption password
       async = 0                                          -- run restore asynchronously
   

6. Remove any query-level settings that do not belong in the backup/restore context and set them separately:

   -- Wrong: mixing query settings with backup settings
   BACKUP DATABASE my_db TO Disk('backups', 'bak')
   SETTINGS max_threads = 4;
   
   -- Right: set query settings separately
   SET max_threads = 4;
   BACKUP DATABASE my_db TO Disk('backups', 'bak');
   

Best Practices

• Keep a reference of valid backup/restore settings for your ClickHouse version and use it when writing backup scripts.
• Use configuration management or templates for backup commands so settings are consistent and reviewed.
• When upgrading ClickHouse, check the changelog for new or deprecated backup settings.
• Test backup commands with new settings in a staging environment before deploying to production.
• Add validation or dry-run logic in your automation scripts to catch setting errors early.

Frequently Asked Questions

Q: Where can I find a complete list of valid backup settings?
A: The official ClickHouse documentation has a dedicated page for BACKUP and RESTORE commands that lists all supported settings. You can also check the source code for the definitive list if you are running a custom build.

Q: Can I use server-level settings to change backup behavior?
A: Some backup behavior is controlled by server-level configuration in config.xml (such as backup storage definitions), while operational settings are specified in the SETTINGS clause of the BACKUP or RESTORE command. The two are separate.

Q: Does the async setting work for all backup destinations?
A: Yes. The async = 1 setting tells ClickHouse to run the backup in the background regardless of the destination type. You can monitor progress via system.backups.

Q: Why does my setting work on one server but not another?
A: The servers are likely running different ClickHouse versions. A setting introduced in version 23.8 will not be recognized on a server running 23.3. Check SELECT version() on both servers.