ClickHouse DB::Exception: Cannot restore table

The "DB::Exception: Cannot restore table" error is raised when ClickHouse is unable to restore a specific table from a backup. The CANNOT_RESTORE_TABLE error code appears when there is a conflict between the table in the backup and the state of the target server -- such as the table already existing with a different schema, an incompatible engine, or missing dependencies.

Impact

The affected table is not restored, while other tables in the same backup may restore successfully. This creates an incomplete recovery where some data is available and some is not. In production disaster recovery, a failed table restore can break applications that depend on that table's data.

Common Causes

1. Table already exists with a different schema -- the target server has a table with the same name but different columns, types, or engine settings.
2. Table already exists and is not empty -- ClickHouse protects against overwriting existing data.
3. Missing dependent objects -- the table references a dictionary, user-defined function, or another table (for materialized views) that does not exist on the target server.
4. Engine not available -- the backup contains a table using an engine (e.g., MaterializedMySQL, Kafka) that is not enabled or installed on the target server.
5. ZooKeeper path conflict for replicated tables -- the ReplicatedMergeTree path is already occupied by a different table.
6. Insufficient disk space -- the target server does not have enough storage to accommodate the table's data.

Troubleshooting and Resolution Steps

1. Read the full error message. ClickHouse specifies which table and why:

   DB::Exception: Cannot restore table my_db.my_table because it already exists
   

2. If the table already exists, decide how to handle it:

   -- Option A: Drop the existing table
   DROP TABLE IF EXISTS my_db.my_table;
   RESTORE TABLE my_db.my_table FROM Disk('backups', 'my_backup');
   
   -- Option B: Restore under a different name
   RESTORE TABLE my_db.my_table AS my_db.my_table_restored FROM Disk('backups', 'my_backup');
   
   -- Option C: Allow overwriting
   RESTORE TABLE my_db.my_table FROM Disk('backups', 'my_backup')
   SETTINGS allow_non_empty_tables = 1;
   

3. Check for schema differences:

   -- Current table schema
   SHOW CREATE TABLE my_db.my_table;
   -- Compare with the schema in the backup
   

   cat /var/lib/clickhouse/backups/my_backup/metadata/my_db/my_table.sql
   

4. Resolve missing dependencies. Create required dictionaries, functions, or source tables before restoring:

   -- Example: create a missing dictionary first
   CREATE DICTIONARY my_db.my_dict (...) ...;
   -- Then retry the restore
   RESTORE TABLE my_db.my_table FROM Disk('backups', 'my_backup');
   

5. For replicated table path conflicts, check ZooKeeper and clean up stale entries:

   SELECT * FROM system.zookeeper
   WHERE path = '/clickhouse/tables/my_db/my_table';
   

   SYSTEM DROP REPLICA 'old_replica' FROM TABLE my_db.my_table;
   

6. Check available disk space:

   SELECT name, free_space, total_space FROM system.disks;
   

Best Practices

• Before a bulk restore, compare the backup's table schemas against existing tables on the target server.
• Restore to a staging database first (using AS renamed tables) to validate before overwriting production data.
• Ensure all dependent objects (dictionaries, materialized view source tables) exist before restoring.
• Keep target servers configured with the same engines and features as the source to avoid compatibility issues.
• Monitor disk space and set alerts so you do not discover space shortages during a critical restore.

Frequently Asked Questions

Q: Can I restore just one table from a multi-table backup?
A: Yes. Use RESTORE TABLE my_db.my_table FROM Disk('backups', 'my_backup') to restore a single table. Other tables in the backup are ignored.

Q: What if the table in the backup uses ReplicatedMergeTree but I want to restore as plain MergeTree?
A: ClickHouse does not allow changing the engine during restore. You would need to restore the table as-is, then create a new MergeTree table and insert the data into it using INSERT INTO ... SELECT FROM.

Q: Does allow_non_empty_tables merge data or replace it?
A: It inserts the backup data alongside existing data. It does not delete existing rows. This can lead to duplicates if the same data exists in both the table and the backup.

Q: Why does my restore fail with a schema mismatch even though the table names match?
A: The table schemas must be compatible, not just the names. If columns were added, removed, or their types changed between when the backup was taken and the current table definition, the restore will fail. Drop the existing table or align the schema before restoring.