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!

Oops! Something went wrong while submitting the form.

Description

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.