Backing up ClickHouse with ALTER TABLE FREEZE

Backing up ClickHouse with ALTER TABLE FREEZE: what it protects and when to use it

Teams running self-managed ClickHouse for event, log or telemetry data often assume that replication keeps them safe. It does not. A DROP TABLE or a bad ALTER statement runs on every replica, and corrupted parts are copied as faithfully as healthy ones. The ClickHouse backup documentation makes this point directly: replication alone does not protect against human error or corruption, so a separate, point-in-time copy of the data is required. ALTER TABLE ... FREEZE is one way to take that copy. It creates a local snapshot of one or more partitions by hard-linking the existing data parts into the shadow directory under the server's data path. Because the links point at the same files, the snapshot is created almost instantly and takes little extra disk space at the moment it is made.

FREEZE is usually chosen when a team wants a simple, low-dependency partition backup that it can script itself, when its partitioning scheme (often by day or month) matches its retention and recovery needs, or when it has no object storage target yet. The critical limitation is location. The frozen files stay on the same server, on the same disk, as the live data, so a disk failure, a host loss or a compromised node can take both copies at once. Copying the shadow contents to storage that is independent of the server is the team's job, and FREEZE does not do it. Confirm with your own design what the off-server destination is, how often the copy runs, and who can read and delete it, before you rely on the snapshots.

Which FREEZE scope and backup method fits your recovery needs

This table compares partition-level FREEZE, whole-table FREEZE, clickhouse-backup runs and native BACKUP and RESTORE across what they capture, where data lands, replica consistency and restore path. Read each row against your recovery point needs, and treat the table as a starting point for testing in your own environment rather than a sizing guide.

Method	What is captured and where it lands	Consistency across replicas and restore path	When it fits
FREEZE at partition scope	Hard links to the parts of one partition, created in the shadow directory on the same server and disk	Captures only that replica's local parts; other replicas hold their own copies. Restore with ATTACH PARTITION or by copying parts back into the detached folder	Targeted recovery of one partition, such as a bad day of data, where you can verify the partition locally
FREEZE of a whole table	Hard links to every active part of the table on that server, still in the shadow directory	Per-replica snapshot, so replicas can differ if they are not frozen at the same point. Restore needs the files copied off the server first	Small or single-node tables, or when you want a full local snapshot and have a process to move files off-host
clickhouse-backup driven runs	Wraps FREEZE, copies parts and metadata to local or remote storage such as S3-compatible or Azure Blob Storage, under a tool-managed layout	Depends on the tool's configuration and which replica runs the job. Confirm how it handles replicated tables and schema in your version	Scheduled off-host copies and retention management when you want less hand-built scripting
Native BACKUP and RESTORE	Logical backup of tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage, with full and incremental options	Recorded in system.backups with a status you can query. Restore uses the RESTORE statement and is tested through the same interface	Most teams starting fresh, or when you need built-in tracking, incremental chains and documented restore settings
Off-host copy of the shadow directory	Files you copy yourself from the shadow directory to another host or bucket, with checksums you record	Only as consistent as the freeze point and your copy process. Restores depend on matching table schema and part layout	Teams with strong file tooling who accept the manual steps and need to prove each copy
Schema and access definitions	Table DDL, users, roles and row policies are not carried by a partition FREEZE; confirm how each method treats them	Restoring data without the schema or grants leaves a partly working cluster, so check this in the restore test	Any method, since a recovery plan must rebuild definitions as well as data
Restore test evidence	Row counts, checksums and query results from the restored copy on a spare cluster	Compare restored results with the source at the same recorded point, noting which replica you read from	Every method. A backup that has never been restored in your environment is unproven
What to measure locally	Disk space consumed by hard links versus copied files, copy duration and restore duration per method	Time to a verified restore and the freeze point each replica used during the window	Use these measurements, not published figures, to choose the scope and schedule for your cluster

How to take, copy and verify a FREEZE backup

This sequence turns a live partition into a frozen, hard-linked snapshot, moves its parts off the server with checksums, and leaves a manifest that shows what was backed up and whether it can be checked against the source. Each step ends with a check so the backup is confirmed before anyone relies on it.

Confirm partitions and disk paths
The operator queries system.parts on the source table to list the active partitions, their row counts and the disk each part lives on, and records the table's data path from system.tables. This tells you which partitions to freeze and where the shadow directory will appear under the server's storage path. The step is done when you have a written list of partition IDs, expected row counts and the data path for each disk in use. Confirm the exact column names and the path layout for your ClickHouse version against the documentation, because these details can change between releases.
Run ALTER TABLE FREEZE per partition
An operator with the required privileges runs ALTER TABLE ... FREEZE PARTITION for each partition on the list, and adds WITH NAME when you want a stable label for the snapshot instead of the default timestamp-based name. FREEZE creates hard links to the current data parts, so it is fast and does not copy data at this point. The step is done when the command returns without error and the named or default backup directory exists under the shadow path. Confirm the exact syntax, and whether your deployment needs any additional privileges, against the backup documentation for your version.
Locate the shadow directory contents
The operator goes to the shadow directory on each server and in each disk that holds parts for the table, then lists the frozen parts under the backup name. Each part should appear with its original part name and the same files as in the live data directory. Check that the number of frozen part folders matches the number of active parts you recorded in the first step for each partition. The step is done when every expected part is present in the snapshot and the counts match. If a disk is missing from the listing, check whether the table uses a storage policy with additional disks before moving on.
Copy frozen parts off the server
The operator copies the frozen part folders to storage outside the server, such as an object store, a backup host or a mounted volume, using a tool that preserves file contents and metadata. The copy should keep the partition and part names so the restore path can find them later. Record the destination path and the transfer method alongside the copy. The step is done when the copy completes without transfer errors and the destination holds the same part folders as the shadow directory. Hard links do not survive a copy to another host, so verify the destination contains real files, not links.
Verify checksums and row counts
The operator generates checksums for each copied file at the source and at the destination, then compares them to confirm the transfer was exact. For a stronger check, the operator compares the row count and a simple aggregate for each partition in the live table with the same figures from a restored copy in a test environment. The step is done when every checksum matches and the row counts agree for each partition. Keep the checksum output as evidence. A backup that has not been compared against its source has not been verified, so do not skip this step even when the copy tool reports success.
Clear the shadow directory
Once the copy is verified, the operator removes the frozen snapshot from the shadow directory with ALTER TABLE ... UNFREEZE for the same partitions and name, or with the equivalent cleanup command your version documents. Leaving snapshots on the server keeps hard links to parts that merges would otherwise remove, which consumes disk space. The step is done when the shadow directory no longer holds the backup and disk usage falls as expected. Confirm the unfreeze syntax and whether shadow cleanup is handled automatically in your environment before scheduling it.
Record a backup manifest
The operator writes a manifest for each backup that lists the database and table, the partitions and part names, the row counts, the checksums, the source server, the destination path, the time of the freeze and the operator who ran it. The manifest also records the ClickHouse version and the table definition so a restore can be matched to the right schema. The step is done when the manifest is stored next to the copied data and can be read without access to the source server. Use the manifest as the input for scheduled restore tests, because a backup without a manifest is hard to prove later.

How to restore with ATTACH PARTITION after a FREEZE

A FREEZE backup is restored by copying the frozen parts back into the table's detached directory and then running ALTER TABLE ... ATTACH PARTITION, so the parts become active again. The restore only works if the target table has the same schema the backup was taken from: the same column names, types, ordering key, partition key and engine. Confirm this from the CREATE TABLE statement saved alongside the backup, not from memory or from a later migration. If the schema has drifted, the parts may attach incorrectly or fail, and the failure can be subtle if only the column order changed. Keep the table definition in the same backup set as the shadow copy, and record the ClickHouse server version too, since the on-disk part format is tied to the version that wrote it.

Replicated tables need extra care. On a ReplicatedMergeTree table, a part attached on one replica is not automatically a cluster-wide fix: the replica that receives the attach registers the parts, and other replicas fetch them only through the replication queue coordinated by ClickHouse Keeper. Run the restore on one replica at a time, check that the parts appear in the expected state, and confirm the other replicas converge before deleting anything else. Avoid restoring the same partition on several replicas at once with copies of different age, because that is how the cluster can diverge. Ask your operations team to confirm the restore procedure against the replication behavior of your ClickHouse version, and to decide in advance which replica is the reference.

A restore that has never been rehearsed is unproven. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, and that advice applies to FREEZE copies as much as to BACKUP output. Rehearse the path on a spare cluster or a scratch database: copy a frozen partition, attach it, compare row counts and a checksum of key columns against the source, and time the steps. Record the commands, the schema check and the results as evidence. The rehearsal will expose missing files, permission problems and the replica behavior described above while the stakes are low, and it gives the on-call team a runbook they have actually executed.

Where FREEZE falls short compared with native BACKUP and RESTORE

FREEZE gives you a consistent copy of parts on disk, but everything around those parts is left to your team. The cards below are questions to answer for your environment before you rely on partition copies as your only recovery path, since each gap has to be closed by a process, a script or a tool.

Who copies, retains and expires the files

FREEZE only creates hard links under the shadow directory. Moving those files off the server, naming them, deciding how long each set is kept and deleting expired sets are all manual steps. Ask who owns that job, how it is monitored, and what happens when a copy fails halfway through a large partition.

No built-in incremental chain

Native BACKUP supports incremental backups against a base backup, but FREEZE has no concept of a base. Every freeze is a standalone set of parts unless your tooling tracks which parts already exist in earlier copies. Ask how you will avoid copying the same data every night and how you will prove which copies a restore depends on.

Schema and access metadata left behind

A part copy contains data files, not the table definition, database objects, SQL-managed users, roles, row policies or settings profiles. Restoring the parts into an empty server means rebuilding those objects first. Ask where your CREATE statements and access definitions are stored, and whether that copy is taken at the same moment as the data.

Shadow data left on disk

Each freeze creates a shadow directory inside the table's storage path, and hard links keep those files alive even after the table is changed. If nobody runs cleanup, disk usage grows silently and can fill the volume that serves live inserts. Ask who removes shadow data after each copy and how disk alerts would reveal a backlog.

Consistency across shards

A freeze runs on one node at a time, so a cluster with several shards and replicas has no single moment at which every shard was frozen. Replicas can also differ while fetches are in flight. Ask which replica you freeze, how you record the timestamp per shard, and whether the restore plan accepts a slightly different point for each shard.

Restores that must be scripted

Restoring means copying parts back into each table's detached directory and running ATTACH PARTITION for each partition, in the right order across shards. Native RESTORE handles that sequencing for you. Ask who writes and maintains these scripts, how they are tested, and how long a full restore is expected to take in your environment.

When clickhouse-backup automates FREEZE and when to script it yourself

On a single ClickHouse server, the backup workload is small in moving parts: one host, one set of partitions, and one destination to copy to. A tool that wraps FREEZE, copies the shadow directory, and tracks which backups exist can save you from rewriting the same shell logic and from forgetting to run it. A hand-written script is still reasonable here if the team already has cron, a copy job, and a restore runbook they trust.

What a tool typically automates in this setting:

Freezing partitions: issuing ALTER TABLE ... FREEZE across the tables you select, so you do not maintain the table list by hand.
Moving the frozen parts: copying files from the shadow directory to a destination you configure, such as a local disk or mounted volume.
Naming and retention: labeling each backup and removing old ones according to a retention setting.
Restore steps: reattaching restored parts, which you still need to check against your own tables.

What it does not replace: a restore rehearsal on a spare host, verification of row counts against the source, and a decision about who can trigger a restore. Confirm the exact commands, retention options and storage targets in the tool's own documentation before you rely on them.

Securing FREEZE backups: access, encryption and off-server copies

A FREEZE backup is only as safe as the host it sits on and the path it takes off that host. The cards below cover who can read and run backups, how copies are encrypted, where credentials live, and how to keep a compromised server from taking its backups with it.

Lock down the shadow directory

FREEZE writes hard links under the table's shadow directory inside the ClickHouse data path, and those files are readable by anyone with filesystem access to that path. Confirm the ClickHouse service account owns the data tree, that ordinary operators and application accounts cannot read it, and that no backup script runs with broader rights than it needs. Check permissions after every package upgrade or volume remount, because a changed mode on the parent directory can expose frozen parts without any change to ClickHouse itself.

Restrict who can run FREEZE

ALTER TABLE FREEZE is a SQL statement, so any database user granted the right to alter a table can create a frozen copy. Limit that grant to the backup account and to named administrators, and leave application users without it. Access control in ClickHouse covers users, roles and settings profiles, and the default user starts with all permissions, so it should be restricted before any backup job is scheduled.

Separate backup credentials from database users

Use a dedicated account for scheduled freezes and for any tool that copies files off the server, with its own password or certificate and no rights beyond what the backup needs. Keep its secret in the host's secret store rather than in a shell history or cron file. Rotate it on a schedule and on staff changes, and make sure a leaked application password cannot be used to read or delete backup copies.

Encrypt copies in transit and at rest

A frozen part is an uncompressed copy of table data, so the same protection applies as to the live table. Use TLS for any connection that moves files to a remote store, and verify certificates instead of disabling checks. For data at rest, decide between encryption at the storage layer, such as an encrypted volume or object store, and encryption applied before upload. Ask your storage team to confirm key custody and who can access the keys, since encryption that the same compromised host can decrypt provides limited protection.

Move copies off the server

A frozen copy on the same disk protects against a dropped table, but not against disk failure, ransomware or a host compromise. Copy frozen parts to a separate system such as object storage or a backup server, and make that destination write-once or versioned where the platform supports it. Control deletion from the storage side so that a root user on the ClickHouse host cannot erase retained copies with the same credentials used for backups.

Limit the blast radius of a compromised host

Assume a ClickHouse node may be compromised and check whether its backups survive that event. The copy destination should not accept deletes from the database host, the backup credentials should not be stored on it in plain form, and the network path should allow writes only from the backup job. Document which accounts can reach both the live data and the backup store, and test that a restore is still possible when the primary host is unavailable.

Questions teams ask before backing up ClickHouse with FREEZE

How much effort does a FREEZE-based backup process take to build and run?

Building it is mostly scripting and wiring: a job that freezes partitions, copies the shadow directory off the server, records what was copied and cleans up afterward. Running it is recurring work: watching the job, rotating old copies, handling tables that change shape and keeping the script aligned with each ClickHouse upgrade. The build effort depends on how many tables, databases and replicas you run and whether you already have object storage and job scheduling in place. Ask any partner to split its estimate into build and run effort so you can see the recurring share.

What drives the cost of FREEZE backups?

Three components carry most of the cost. Storage is the copied data plus retention, so the number of retained copies and the size of your partitions matter most. Engineering time covers the initial build and every later change to the scripts or upgrade path. Restore testing is recurring work on a spare environment, including the compute to run it and the people who verify results. Storage is recurring, while build work is largely one-time. When you request a quote, ask for each component separately, the assumed retention period and who operates the restore environment.

Can our team run FREEZE backups without a partner?

Yes, if you have engineers who know ClickHouse storage layout, scripting, and your storage platform, and who can own the job after handover. FREEZE itself is a documented SQL command, so the hard part is operational discipline: consistent naming, verified copies, retention and restore rehearsals. Teams that lack capacity for this usually struggle most with the restore tests, which are the step most often skipped. A partner can build the process, document it and run the rehearsals, or your team can take that over after a planned handover.

How long does a restore rehearsal take, and what drives it?

Duration depends on the volume of data restored, the speed of the storage you copy from, the number of parts and partitions that must be re-attached, and how many replicas must resynchronize afterward. Restores to a spare cluster take longer when the copied data must travel over a network or from cold storage. Ask for a rehearsal plan that states the data set size, the target environment and the checks you will run, because those inputs determine the schedule. No fixed duration applies to every environment, so measure a representative table first.

Does FREEZE replace replication for ClickHouse?

No. Replication keeps replicas in sync, so a failed node recovers its data from a healthy replica, but it also copies deletions, bad inserts and corruption to every copy. FREEZE creates point-in-time copies of parts on one server through hard links, which protects against those human and software errors and gives you a copy to restore from. The ClickHouse backup documentation makes the same point: replication alone does not protect against human error or corruption. Plan both, and keep the frozen copies off the server they came from so a site or disk loss does not remove them too.

Is ClickHouse FREEZE backup suitable for regulated data?

FREEZE can support a regulated backup process, but the control design around it decides whether it does. Encrypt copies in transit and at rest, restrict who can read backup storage, record each backup and restore with timestamps and checks, and define retention and deletion rules that match your policy. Compliance assessors look for evidence that restores were tested, so keep rehearsal records. DeployOpen prepares systems, controls and evidence for compliance assessments, but independent auditors and certification bodies issue any audit report or certification.

What should we ask a partner for in a FREEZE backup quote?

Ask for a written scope that names the clusters, databases and tables in and out of scope, the backup schedule, retention periods, the off-server storage target and the access model. Request the restore rehearsal plan, the evidence each test produces, the handover and documentation deliverables, and what support or retainer applies after go-live. Also ask how upgrades to ClickHouse will be handled, who owns the scripts, and which costs are one-time versus recurring. DeployOpen's engagements are typically fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.

Limits to confirm in your environment before relying on FREEZE

FREEZE is a low-level mechanism, and its behavior depends on the ClickHouse version, disk layout and workload you run. The checks below are things to confirm with the vendor documentation or on a test cluster before a freeze-based backup becomes your recovery path.

Supported versions in the security policy

Start by checking which ClickHouse versions receive security updates in the project's security policy. A backup routine built on FREEZE is only as durable as the server it runs on, so confirm that the version you run, or the version you plan to move to, is on the supported list. Record the date you checked, because the list changes with releases. Treat any version missing from the list as a finding to address in your upgrade plan.

Behavior across versions in use

Confirm how FREEZE, ATTACH PARTITION and the shadow directory behave on every version in your cluster, including mixed versions during a rolling upgrade. Read the changelog for the versions between your current release and your target. Then reproduce a freeze and restore on each version you run, rather than assuming identical output across releases.

Disk layout and storage policy effects

Hard links only work within the same filesystem, so confirm how your storage policy places parts across disks, volumes and object storage. Parts on remote or tiered disks may not be captured the way local parts are. Test a freeze on a table that uses your real storage policy, and confirm where the shadow directory lands and whether it can be copied off the server as expected.

Mutations and merges during a freeze

Background merges and mutations can rewrite or replace parts while you copy a freeze. Confirm how ClickHouse handles these operations during and after FREEZE on your version, and whether a freeze gives a consistent point-in-time view of a partition. Test a freeze while inserts, merges and an ALTER UPDATE or DELETE run against the same partition, then compare row counts after restore.

Retention of shadow data

Frozen data stays in the shadow directory until someone removes it, so confirm how long it persists and who owns cleanup. Check whether your disks have enough headroom for accumulated freezes, and whether dropping a table or partition removes its frozen copies. Write the cleanup step into your runbook so that old freezes do not silently fill the disk.

Choosing between FREEZE, BACKUP and RESTORE, and a backup strategy

FREEZE suits fast, partition-level copies when one team owns the scripts and the restore runbook. ALTER TABLE ... FREEZE creates hard links to a table's data parts under the shadow directory on the same disk, which is cheap and quick, but it copies only table data. It does not carry users, roles, row policies or the table definitions you need to rebuild a schema from scratch, and it leaves the copying off the server to you. Choose it when your recovery scope is a known set of partitions, when a named engineer owns the cron job or orchestration that moves the frozen parts to separate storage, and when that same owner has rehearsed the ATTACH PARTITION path against a spare table.

BACKUP and RESTORE cover more of the cluster and support incremental chains, so they fit when the recovery scope is wider than a few partitions. The backup documentation describes full and incremental backups of tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage. Plan for the trade-offs: backups run as managed operations that you monitor in system.backups, incremental chains add restore dependencies you must track, and you should confirm with your ClickHouse version's documentation which metadata and access objects each method captures in your environment. Use a restore test to settle those questions, not assumptions.

Whichever method you pick, a backup strategy defines the schedule, retention and recovery targets, and a backup that has not been restored is not yet a backup. The backup documentation recommends regular restore tests on spare clusters, and the topics that follow cover strategy and recovery objectives in depth, S3-compatible targets, incremental chains, and restore testing with recorded evidence. Ask your team to write down which tables sit in each recovery tier, who can run each restore, and how long a restore into a spare cluster takes in your own environment before you commit to either method.

Backing up ClickHouse with ALTER TABLE FREEZE