How to BACKUP in ClickHouse

How do I back up tables or databases with the ClickHouse BACKUP command?

The BACKUP statement saves ClickHouse tables, databases, or the entire cluster to a local or remote storage so you can restore data later.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the BACKUP command in ClickHouse?

The BACKUP statement stores table or database data and metadata in a snapshot that can be copied to local disks, S3 buckets, or HDFS. You can later RESTORE from this snapshot.

When should I use BACKUP?

Use BACKUP before major schema changes, upgrades, or periodically to protect against data loss.Automate it with cron or orchestration tools.

How do I back up a single table?

Run BACKUP TABLE Orders TO Disk('local_backup') to copy both data and metadata for the Orders table to the local_backup disk.

How do I back up multiple tables atomically?

Wrap tables in parentheses: BACKUP TABLE (Orders, OrderItems) TO S3('s3_conn', 'ecom_backup'). All listed tables are copied in one snapshot.

How do I back up an entire database?

Use BACKUP DATABASE ecommerce TO S3('s3_conn', 'ecom_backup').All tables inside ecommerce are included.

Cluster-wide backup?

Append ON CLUSTER my_cluster to back up data across all shards: BACKUP DATABASE ecommerce ON CLUSTER my_cluster TO ....

How do I name backups?

Add AS 'backup_name' for easy lookup: BACKUP TABLE Orders AS 'orders_2024_04_20' TO Disk('local').

How do I check backup status?

Query system.backups for progress and errors: SELECT * FROM system.backups ORDER BY start_time DESC LIMIT 5.

Best practices

1. Store at least one off-site copy.
2. Automate with retries.
3. Monitor system.backups for failures.
4.Version your backups using timestamped names.
5. Test RESTORE regularly.

Example: nightly table backup

BACKUP TABLE Orders TO S3('s3_conn','orders_daily/{date}'). Replace {date} with a runtime variable in your scheduler.

Common questions

Why How to BACKUP in ClickHouse is important

How to BACKUP in ClickHouse Example Usage


-- Back up customer and order data to S3 with a timestamped folder
BACKUP TABLE (Customers, Orders)
       AS 'cust_order_2024_04_20'
       TO S3('s3_conn', 'backup/2024/04/20');

How to BACKUP in ClickHouse Syntax


BACKUP [TABLE | DATABASE] [db_name.]table_list
       [ON CLUSTER <cluster>]
       [AS '<backup_name>']
       TO Disk('<disk_name>' [, '<path>'])
       | S3('<conn_name>', '<path>')

-- Examples
-- 1. Single table to local disk
BACKUP TABLE Orders TO Disk('local_backup');

-- 2. Multiple tables to S3
BACKUP TABLE (Customers, Orders, OrderItems)
       TO S3('s3_conn', 'ecom_backup');

-- 3. Full database on a cluster
BACKUP DATABASE ecommerce ON CLUSTER prod_cluster
       TO Disk('nfs_backup', '/snapshots/ecommerce');

Common Mistakes

Mistake: Omitting ON CLUSTER when tables are sharded. The backup will only run on the local replica, leaving other shards unprotected. Fix: always add ON CLUSTER when data is distributed.
Mistake: Storing all backups on the same server as ClickHouse. A hardware failure can wipe both primary and backup data. Fix: write at least one copy to remote storage such as S3.