ClickHouseintermediate

ClickHouse Backup and Restore

BACKUP and RESTORE commands, partition freezing, and what a consistent backup means across a sharded cluster.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

ClickHouse has a native BACKUP command that writes to a local directory or to object storage.

BACKUP and RESTORE

-- Full backup of a database to S3.
BACKUP DATABASE analytics
TO S3('https://backups.example.com/clickhouse/2026-07-31', 'ACCESS_KEY', 'SECRET_KEY');
 
-- Incremental, based on a previous backup.
BACKUP DATABASE analytics
TO S3('https://backups.example.com/clickhouse/2026-08-01', 'ACCESS_KEY', 'SECRET_KEY')
SETTINGS base_backup = S3('https://backups.example.com/clickhouse/2026-07-31', 'ACCESS_KEY', 'SECRET_KEY');
 
-- One table, one partition.
BACKUP TABLE analytics.events PARTITION '202607'
TO Disk('backups', 'events-202607.zip');

RESTORE DATABASE analytics
FROM S3('https://backups.example.com/clickhouse/2026-07-31', 'ACCESS_KEY', 'SECRET_KEY');
 
-- Restore into a different name to verify without touching production.
RESTORE DATABASE analytics AS analytics_verify
FROM S3('…') SETTINGS allow_non_empty_tables = 0;

-- Progress and history.
SELECT id, name, status, error, total_size, num_files
FROM system.backups ORDER BY start_time DESC LIMIT 10;

FREEZE for filesystem-level snapshots

ALTER TABLE ... FREEZE creates hard links to the current parts under shadow/, which is a consistent, instantaneous snapshot that costs no extra space until parts change:

ALTER TABLE events FREEZE PARTITION '202607';
-- Copy /var/lib/clickhouse/shadow/<N>/ to durable storage, then:
SYSTEM UNFREEZE WITH NAME '<N>';

The hard links keep the underlying files alive, so a frozen snapshot that is never released prevents disk space from being reclaimed after merges. Always unfreeze after copying.

Consistency across a cluster

What else to back up

Schema for the whole cluster, including ON CLUSTER DDL, dictionaries and materialized view definitions.
Server configuration, including remote_servers, macros and storage policies. A restored data directory is unusable without matching macros.
Users, roles, row policies and quotas, if they are defined in SQL rather than in XML.

Verification

-- After restoring into a verification database.
SELECT table, sum(rows) AS rows FROM system.parts
WHERE active AND database = 'analytics_verify' GROUP BY table;

Compare row counts per table and per partition against the source, and run a representative query. Schedule this; see Restore Testing.