Designing a ClickHouse backup strategy

Why replication alone does not make a ClickHouse backup strategy

A platform team that runs replicated ClickHouse often assumes the data is protected. Replication does guard against node loss: a recovered replica syncs missing data from a healthy replica without manual intervention, as the replication guide describes. But replicas copy what they are given. A DROP TABLE, a bad ALTER, a faulty ingestion job or a corrupt part is applied to every replica that receives the change, so the damage spreads instead of being contained. The ClickHouse backup documentation makes the same point directly: replication alone does not protect against human error or corruption. A backup is a separate, point-in-time copy that a bad write on the live cluster cannot reach.

Designing a backup strategy means making four decisions before the first BACKUP runs. The first is methods: full and incremental BACKUP and RESTORE to local disk, S3-compatible storage or Azure Blob Storage, or partition-level copies made with ALTER TABLE FREEZE, which depend on the shape of your tables. The second is frequency and retention, which set how many copies exist and how far back a restore can go. The third is recovery objectives: how much data loss the business accepts and how long a restore may take. The fourth is where copies live, since a backup stored on the same hosts and failure domain as the cluster offers little protection against site loss. Confirm each choice with the owners of the data, not only the platform team.

Teams usually need this design work in one of three situations: before go-live, when the first production dataset becomes too valuable to rebuild from source; after an incident in which a replicated table was damaged or deleted and the team discovered there was no clean copy to return to; or when an auditor or internal risk function asks for evidence of backup, retention and recovery controls. In each case, ask for the written backup policy, the restore test records and the list of tables and users covered before you assume the cluster is protected. Anything not in those records should be treated as unprotected until a restore proves otherwise.

What replication does not cover in a ClickHouse cluster

Replication keeps copies of data in step across replicas, which protects against a node failing. It does not protect against a change that is applied everywhere, bad data that is copied everywhere, or configuration that sits outside the replicated tables. Each card below is a gap that needs its own backup or recovery control.

Accidental drops and mutations

A DROP TABLE, TRUNCATE, or DELETE or ALTER ... UPDATE mutation runs on one replica and then propagates to every replica that holds the table. A recovered replica resyncs from a healthy replica, so it cannot bring back data that was already removed everywhere. Check which database and table names your operators can drop, and keep a backup taken before the change window that you can restore to a spare cluster.

Corruption copied by replication

A bad part, a faulty ingestion job, or a broken schema change can be written to one replica and then fetched by the others. All replicas then agree on the wrong data, so the health checks that compare replicas report nothing wrong. Keep backups that are older than the suspected corruption, and confirm with your team how far back a restore can reach before the corrupt data is merged out.

Data lost before replication completes

An insert is acknowledged once it is written to the node that received it, and replication to the other replicas happens afterward. If the node that took the write fails before its data reaches a peer, those rows can be lost. Ask your team which insert settings they use and how they confirm that writes reached more than one replica. A backup does not close this window, but it limits the loss to what happened after the last backup.

Configuration files outside the database

Files such as those in config.d and users.d define storage, cluster layout, and default access, and they are not stored in the replicated tables. Losing them on one host means a rebuilt node may not start with the same settings. Put these files under version control and include them in your recovery runbook, because a database backup alone does not contain them.

SQL-managed users and roles

Users, roles, row policies, quotas, and settings profiles created with SQL are stored by the server, not in the replicated data tables. Their definitions can be deleted or changed by mistake, and they are not restored by a table-level recovery. Decide which access objects must be included in your backups, and test that a restored cluster enforces the same row policies before you rely on it.

Keeper state and recovery scope

ClickHouse Keeper holds the coordination metadata that replicated tables depend on, and it is separate from the data parts. Losing Keeper data without a plan can leave tables unable to agree on their contents, even when the data files are intact. Ask your team how Keeper is backed up, where its snapshots are stored, and what the rebuild steps are, and record them alongside your table backup procedure.

Which backup method fits which data and failure

This table compares the four ways to protect a self-managed ClickHouse cluster: BACKUP and RESTORE, incremental backups, FREEZE with copied shadow directories, and replaying the ingestion stream. Read each row for what the method captures, where it can write, its main trade-off and the failure it fits best. Choosing a method is separate from setting its schedule.

Method	What it captures and where it writes	Main trade-off	When it fits
BACKUP and RESTORE (tables, partitions or databases)	Logical copy of chosen tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage	Full copies cost the most storage and time on large tables; restores must be tested on a spare cluster	Default choice for database-level protection against deletion, bad migrations and site loss
Incremental backups with base_backup	Only data added since a base backup, written to the same target types as BACKUP	Restores depend on the whole chain; a damaged base or link can block every later restore	Large, append-heavy datasets where full copies are too slow or costly, paired with periodic full backups
ALTER TABLE FREEZE with copied shadow directory	Hard-linked snapshot of table parts on the server, copied off-host by your own tooling	Captures data files only; schema, users and config must be saved separately and restores need careful ATTACH PARTITION steps	Fast partition-level snapshots where you control the copy process and accept more manual restore work
Replaying the ingestion stream	Rebuilds tables from a retained copy of the source stream, such as Kafka messages kept in object storage	Recovery window is limited by how long the stream or its copy is retained; rebuild time can be long	Recovering from logical corruption or lost tables when the raw input is kept longer than ClickHouse data
Replication across replicas	Keeps copies of data on healthy nodes and resyncs a recovered replica automatically	Replicates deletes and bad writes too, so it does not protect against human error or corruption	Node and disk failure only; always complemented by one of the methods above
Configuration and access control files	Outside BACKUP: config.d and users.d files, plus SQL-managed users and roles where they are not included	Easy to miss because they live on disk outside the database	Required for rebuilding a cluster on new hosts; keep them in version control and test the rebuild

How recovery point and time objectives set backup frequency

The recovery point objective (RPO) is the most data loss the business will accept, measured in time, and it sets how often data must be captured by a backup or by a replayable source. If the owners of an events or telemetry table can tolerate losing one hour of data, a daily full backup alone does not meet that target, and the team needs either more frequent incremental backups or a second copy of the ingestion stream that can rebuild the lost window. The recovery time objective (RTO) is how long a restore may take, and it drives three decisions: how fast the backup storage must deliver data back to the cluster, how often restores are rehearsed so the duration is known rather than guessed, and whether a spare cluster is already provisioned to receive the restore. Set both values with the data owners and the people who answer for the business impact before choosing any BACKUP settings, and write them down per table or per database, because a large raw event table and a small reference table rarely share the same targets.

Full and incremental schedules trade storage and restore time against chain length. An incremental backup is written with base_backup pointing to an earlier backup, so it stores only what changed, which lowers the frequency cost and the storage used for each run. The cost appears at restore time: restoring a late incremental means reading the full backup and every incremental after it in order, so a long chain increases both the number of artifacts that must be intact and the time to recover. Periodic full backups cap that risk by starting a new chain at a known interval. The interval is a design choice tied back to the RTO: a shorter restore window justifies more frequent fulls, while a larger storage budget makes the same choice cheaper. Confirm with your own storage team how much capacity and throughput each schedule consumes, since those figures depend on your data volume and churn rather than on any general rule.

Once the objectives are agreed, test them. A backup schedule that meets the RPO on paper still misses the RTO if the restore has never been timed on a spare cluster of comparable size. Ask whoever runs the platform to record the duration of a full restore and of a restore from the longest incremental chain, then compare those numbers with the agreed targets. If they fall short, the options are shorter chains, faster storage, a warm standby, or a relaxed objective agreed with the business. Keep these decisions in the strategy document so the next change to retention or table volume prompts a review of the objectives instead of a silent drift.

Combining methods for deletion, corruption and site loss

Accidental deletion, a DROP TABLE run against the wrong database, or an ALTER TABLE ... UPDATE or DELETE with a faulty predicate is replicated to every replica within seconds. Replication therefore protects against hardware loss but offers no protection here. The primary method is a logical BACKUP of the affected database or tables, taken on a schedule that leaves a recent restore point before typical change windows. The secondary method is the source replay path, where it exists, which rebuilds a table from a retained copy of the ingestion stream.

Primary method: scheduled BACKUP TABLE or BACKUP DATABASE runs, with incremental backups between periodic full backups.
Secondary method: source replay from a retained stream, if the ingestion design supports it, to recover data written after the last backup.
Retention: keep short-term restore points for days, sized to how long a bad change can take to notice.
Evidence to keep: backup job logs, system.backups status records and a dated restore test that restores into a separate database.

Confirm with your team how long a bad mutation can go unnoticed, because that window sets the minimum retention for this tab.

Losing a data center, a cloud region or a storage array takes out the replicas and any local backups in the same place. Replication across hosts in one site does not help. The primary method is a full backup written to storage in a separate failure domain, such as S3-compatible or Azure Blob Storage in another site, using BACKUP with a remote destination. The secondary method is a second copy of the ingestion stream or source files held elsewhere, so events after the last backup can be replayed.

Primary method: scheduled BACKUP to remote object storage in a different site or region.
Secondary method: a replicated copy of the source stream or files, with its own retention.
Retention: retain enough off-site copies to cover the recovery point target, and confirm the storage provider's own durability terms.
Evidence to keep: the destination's access logs, credential rotation records, and a restore to a spare cluster built in the second site.

Confirm that the spare cluster can be built from your runbooks without staff who are no longer available, and record the time the restore took.

Designing and checking the ClickHouse backup plan

This sequence turns the backup decision into a written, tested plan: what gets protected, how fast it must come back, which methods write where, and proof that a restore reproduces the source data.

Inventory tables, databases, users and configuration
List every database and table on the cluster, then add the SQL-managed users, roles, row policies, quotas and settings profiles, and the config.d and users.d files that live on each host. Mark which items are rebuilt from code or source systems and which exist only on the cluster. The check is a written inventory that someone other than the author can compare against a live cluster, with a count of tables and access objects that matches what the server reports.
Agree recovery objectives for each dataset
Work with the data owners to set a recovery point objective and a recovery time objective for each dataset, because a raw event stream and a derived reporting table rarely need the same answer. Record which datasets can be rebuilt by replaying a source stream and how far back that stream is retained. The output is a signed table of datasets, objectives and recovery sources that the restore tests will be measured against.
Choose backup methods and destinations
Match each dataset to a method. BACKUP and RESTORE covers tables, partitions and databases to local disk, S3-compatible storage or Azure Blob Storage, while partition freezes and external tools suit other cases. Replication protects against a lost node but not against a bad DELETE or corruption that replicates, so at least one copy must live outside the cluster's failure domain. Confirm that the chosen destination is in a different site or account than the cluster itself.
Set full and incremental schedules and retention
Define how often full backups run, how often incremental backups run against a base, and how many generations are kept. Longer incremental chains save storage but lengthen restores and tie every later backup to its base, so set a maximum chain length and a regular full backup to cap that risk. Confirm the retention window covers the recovery point objective and any audit requirement, and that old generations are deleted only after a newer full backup is verified.
Configure credentials and encryption for the destination
Create a dedicated storage identity with write access for backups and read access only for restores, and store its secret in a named collection or the organization's secrets manager rather than in query text. Protect backups with a password or encryption at the storage layer, and restrict who can read the bucket or container. The check is a test backup that completes with the intended identity, and a failed attempt with a wrong credential that is logged and not silent.
Verify jobs and schedule restore tests on a spare cluster
Confirm in system.backups that scheduled jobs report completed status with expected sizes, and alert on failed or stuck jobs. Then restore to a spare cluster or clean host on a regular cadence, including the configuration backup, and compare restored row counts and checksums with the source for each dataset. ClickHouse documentation warns that an untested backup will probably not restore when needed, so the restore record, with dates, versions and results, is the evidence that the plan works.

Protecting the backups themselves

A backup that an attacker, a careless administrator or a single failure can reach is not a recovery plan. The controls below decide whether your copies survive the same incident that took down the cluster. Each card addresses one layer, from who can write backups to what the logs reveal.

Separate backup write credentials

Create a dedicated ClickHouse user for BACKUP and RESTORE jobs, with privileges limited to what those jobs need, and keep it apart from the accounts that serve dashboards and applications. The backup credential should not be able to drop or alter production tables from the query path. Ask who can read the credential's secret in your secrets store, and record every rotation. Illustrative: a reporting user that can also run BACKUP is one leaked password away from a destination it should never reach.

Encrypt and restrict backup destinations

Treat the backup bucket, container or directory as a sensitive data store. Encrypt data at rest, restrict write and read access to the backup identity and a small set of named operators, and block public access. Confirm whether your storage platform encrypts with keys you manage and who holds them. Backups contain the same personal or regulated data as the live tables, so their access policy should match or exceed the production policy.

Keep copies outside the cluster's failure domain

A copy stored on the same hosts, rack or storage array as the replicas will fail with them. Place at least one copy in a separate storage system, account or site that a cluster-level outage, ransomware event or misconfigured automation cannot reach. Replication keeps replicas in step, so it cannot serve as the off-site copy. Confirm with your infrastructure team which failure domains each destination shares with the cluster.

Retention that resists early deletion

Set retention so that recent backups cannot be removed by ordinary administrators, even when they hold broad storage rights. Ask your storage vendor whether it offers immutability or object lock, how the retention period is set, who can shorten it, and whether the feature applies to the backup prefix you use. Do not assume such a feature exists. Test one deletion attempt from an administrator account in a non-production bucket and record the result.

Treat backup logs as sensitive

The system.backups table and server logs record backup names, destinations, timings and sometimes error text that reveals paths or identifiers. Restrict who can query these tables and where logs are shipped, and mask any credential or endpoint strings before they reach a shared log store. Ensure log retention is set deliberately, since a log trail is often the only evidence of which backups succeeded.

Protect configuration copies alongside data

Backups of tables do not capture the config.d and users.d files, so copies of those files must be stored with the same care. They can contain authentication settings, endpoint definitions and sometimes secrets. Store them in version control or a configuration backup with restricted access, and avoid committing plaintext passwords. Confirm which settings must be recreated from these files during a full rebuild.

Questions teams ask before designing ClickHouse backups

How much effort does a ClickHouse backup strategy design take?

The effort depends on how many decisions the design has to cover, not on the server software itself. The main drivers are the number of clusters, the data volume and how it is partitioned, the number of recovery point and recovery time objectives you need to meet, and whether compliance evidence must be produced. A single cluster with one set of objectives and no audit requirement is a much smaller exercise than several clusters with different retention rules for logs, metrics and user data. Ask any partner to show how each driver changes the scope in their written proposal, and to list which items are fixed and which depend on your environment.

What does the internal team need to provide for the design?

Plan for three inputs from your side: data owners who can state how much data loss is acceptable for each table and how long each dataset must be kept, access to the storage target where backups will be written, and a spare cluster or environment where restores can be tested. Storage access includes credentials, network paths and bucket or container permissions for the backup target. Without a named owner for each dataset, the design tends to stall on retention questions, which are business decisions rather than technical ones. Confirm these items with your own platform and data teams before the work is scoped.

Can our own team design a ClickHouse backup strategy alone?

Yes, a team with ClickHouse operations experience can design a backup strategy internally, and the documentation covers the BACKUP and RESTORE commands, incremental backups and replication behavior in detail. The usual gaps are not the commands but the surrounding decisions: writing down recovery objectives with the business, deciding what replication does not cover, and planning restore tests that are actually run. Many teams find it useful to have an outside reviewer challenge the design before it is implemented. Whichever route you choose, record the decisions so that the people who operate the cluster in two years can understand why the schedule looks the way it does.

How long does planning the restore tests take?

Restore test planning takes less time than the test runs themselves, and its length depends on the number of datasets, the size of each restore, and how much of the cluster must be recreated on the spare environment. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, so the plan should name which tables or partitions are restored, how often, and how row counts or checksums are compared against the source. Ask a partner to state the planning deliverables and the environment assumptions in writing, and confirm the spare cluster sizing with your own infrastructure team before estimating the schedule.

Does replication make backups unnecessary for ClickHouse?

No. Replication keeps copies of data available when a node fails, and a recovered replica syncs missing data from a healthy replica automatically. However, replication copies a deletion or a corrupted write to every replica, so it does not protect against human error or corruption. A backup gives you a point in time you can restore from, and it sits outside the cluster's replication path. Plan separate backups for deletion and corruption scenarios, and consider the stream or source system that feeds the table as a second recovery path where one exists.

What recurring work follows once the backup strategy is in place?

The recurring work includes reviewing the backup schedule and retention as data volumes and business rules change, running restore tests on a regular cadence, and checking that backup jobs complete and their results are monitored. Upgrades also need attention, because a new ClickHouse release can change behavior, so the backup and restore procedure should be re-tested after each upgrade. Stable releases ship roughly monthly, and the production FAQ recommends reading the changelog before upgrading, so build that review into the operating calendar. Decide early who owns each of these tasks and what evidence is kept for each one.

Which compliance evidence should a backup design plan for?

Compliance evidence usually includes the written recovery objectives, the backup schedule and retention rules, records of completed backups, and results of restore tests with the comparisons used to verify them. DeployOpen prepares systems, controls and evidence for compliance assessments, but independent auditors and certification bodies issue the actual audit reports and attestations. Ask your auditor early which artifacts they expect, because the format of restore test records and retention justifications varies between assessments. Include the storage location and access controls for backups in the same evidence set.

Should we use one backup method or combine several?

Most designs combine methods, since each covers a different failure. Full backups give a self-contained restore point, incremental backups reduce the storage and time per run, and partition-level or table-level restores help recover a single dataset without rebuilding the whole cluster. Configuration and access control definitions live outside the database files, so they need their own copy. Decide the combination from your recovery objectives and the restore tests you plan to run, and document which method is used for which failure scenario.

Limits to confirm in your environment

A backup design is only as good as the assumptions behind it. The items below are the points where the ClickHouse version, your storage destination, your hardware and your audit scope can each change the plan. Confirm them early, because several of them decide whether the schedule and retention you chose are achievable at all.

Exact BACKUP and RESTORE behavior

Settings, supported object types and option names for BACKUP and RESTORE can change between releases, so the design should match the version you run in production. Read the backup documentation for your version and confirm how it handles tables, partitions, databases, access entities and incremental bases. Then run a test backup and restore on a non-production cluster before you write the runbook.

Retention and immutability at the destination

Backup retention depends on what your storage platform actually enforces. Ask whether the destination supports lifecycle rules, versioning, and object lock or write-once retention that stops a compromised account from deleting backups. Confirm the behavior of these features on your specific storage product, since a feature name alone does not show how deletion, expiry or legal hold will work for ClickHouse backup files.

Full backup time and load

A full backup reads every part of the tables it covers, so it competes with ingestion and queries for disk, network and CPU. Measure how long a full backup takes on a copy of your largest table, and how much it slows production during the run. Those measurements, not estimates, decide whether a full backup fits your window and how often incremental backups must be taken between fulls.

Destination support in your environment

The chosen destination must be reachable from every ClickHouse node and must work in your cloud account, data center or restricted network. Confirm that the required endpoint, DNS, TLS trust chain, firewall rules and credential method are available in that location. An S3-compatible target inside a private network needs the same checks as a public one, and a missing route discovered during an incident is a common failure.

Evidence accepted by your audit scope

Backup logs, restore test records and retention settings are evidence only if your auditor accepts them for the controls in scope. Ask the assessor which artifacts they expect, such as job history, restore reports with row counts, and change records for retention policy. DeployOpen prepares these records, but independent auditors decide whether they satisfy your requirements.

Key and credential ownership

Backups are encrypted or protected only as far as the keys and credentials around them allow. Decide who holds the destination credentials, where any backup password or named collection secret is stored, and how it rotates. Confirm that the team restoring after an incident can reach those secrets without depending on the cluster that failed.

How DeployOpen designs and tests your backup strategy

DeployOpen plans, implements, hardens, backs up and documents self-hosted open-source platforms inside your own infrastructure, so the backup design for your ClickHouse cluster is built around your data, your sites and your recovery targets. Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.

How we scope

Inventory which tables, databases and ingestion sources hold data you must be able to recover.
Agree recovery point and recovery time objectives per dataset in the written scope before design begins.
Document what replication covers and what it misses, such as human error, bad merges and corruption.

How we build

Configure BACKUP and RESTORE schedules with full and incremental backups to your chosen storage target.
Set up retention rules and credentials so backup storage access follows your identity and secrets policy.
Pair backups with source replay or part-level copies where your recovery objectives require them.

How we test

Run scheduled test restores to a spare cluster and verify row counts and checksums against source tables.
Record restore duration and gaps found during tests so you can confirm objectives are met in practice.
Repeat restore tests after upgrades, schema changes and storage migrations, not only at go-live.

How we hand over and support

Deliver runbooks, restore procedures, configuration and test evidence for your operations and audit records.
Train your operators on restore steps and failure scenarios so recovery does not depend on one person.
Ongoing support, upgrades and continuous restore testing are available as a retainer after handover.