How to Restore Backups in ClickHouse

How do I restore a backup in ClickHouse?

RESTORE brings tables, databases, or the whole cluster back from a BACKUP archive stored on local or remote storage.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does RESTORE solve?

RESTORE reverses data loss or corruption by rebuilding objects exactly as they were at BACKUP time, including schema, data, and TTL rules.

Which objects can I restore?

ClickHouse lets you restore a single table, an entire database, or the full cluster. Pick the smallest scope that fixes the issue to finish faster.

How do I choose the backup source?

Specify a disk alias or S3 path used during BACKUP. RESTORE verifies the manifest before writing any data, preventing mismatched versions.

What is the basic RESTORE syntax?

Use RESTORE TABLE, DATABASE, or ALL. Optional SETTINGS fine-tune concurrency and throttling. See syntax section below.

Example: Restore lost OrderItems table

The query in the Example section recreates OrderItems from an S3 backup taken last night. The table becomes immediately queryable.

How do I avoid name clashes?

Add the AS new_name clause to load data into a differently named table for verification before swapping.

Best practices for production restores?

Run RESTORE on a replica first, validate row counts, then fail over. Always match ClickHouse versions between backup and restore nodes.

What are common mistakes?

Restoring to the wrong path or skipping the --host mapping in distributed setups are typical errors. See the mistakes section.

Why How to Restore Backups in ClickHouse is important

How to Restore Backups in ClickHouse Example Usage


-- Rebuild the OrderItems table that was accidentally dropped yesterday
RESTORE TABLE ecommerce.OrderItems
FROM S3('s3://ck-backups/2024-10-05/', 'AWS_KEY', 'AWS_SECRET')
SETTINGS allow_non_empty_tables = 0, max_download_threads = 8;

How to Restore Backups in ClickHouse Syntax


-- Restore a single table
RESTORE TABLE [db.]OrderItems
FROM Disk('daily_backups')
[AS OrderItems_tmp]
SETTINGS
    allow_non_empty_tables = 0,      -- fail if target not empty
    structure_only = 0,              -- restore schema + data
    max_download_threads = 4;

-- Restore an entire database
RESTORE DATABASE ecommerce
FROM S3('s3://ck-backups/2024-10-05/', 'AWS_KEY', 'AWS_SECRET')
SETTINGS
    drop_existing_database = 1;

-- Restore everything on the cluster
RESTORE ALL
FROM Disk('weekly_full');

Common Mistakes

Restoring into a non-empty table: ClickHouse will error if the target contains data and <code>allow_non_empty_tables</code> is 0. Empty or drop the table first, or set the flag to 1 if overwrite is safe.
Mismatching ClickHouse versions between backup and restore nodes: schema differences may break the process. Always restore on a server that matches the version used to create the backup.

Frequently Asked Questions (FAQs)

Can I restore to a new table name?

Yes. Use AS new_table after the source to rename during restore, allowing side-by-side validation.

Will RESTORE lock the table?

RESTORE acquires metadata locks but streams data in the background. Non-conflicting queries can run concurrently, yet expect slower reads until completion.