How incremental ClickHouse backups reduce repeat storage and where they add restore risk
When a self-managed ClickHouse cluster grows, a full BACKUP each night can stop fitting inside the backup window and can consume more storage than the team planned for. An incremental backup, taken with the base_backup setting, records only the data that changed since an earlier backup, so each run copies less. Teams usually need incremental rather than full backups once the time or space needed for a full copy grows past what their schedule and storage budget allow, while still needing a point-in-time copy they can restore from. The storage saving is real only if the chain stays restorable, so the cost of each run must be weighed against what a restore will require.
ClickHouse BACKUP and RESTORE support both full and incremental backups of tables, partitions or databases, and they can write to local disk, S3-compatible storage or Azure Blob Storage. The ClickHouse backup documentation also makes a point that shapes the whole design: replication does not protect against human error or corruption. A DROP TABLE or a bad INSERT is copied to every replica within moments, so the replicas give you availability, not a rollback path. Backups are the control that lets you return to an earlier state, which is why the incremental design has to be judged on recoverability first and on storage savings second.
The risk in an incremental chain is that every backup depends on the ones before it. If one link is lost, damaged or unreadable, the later backups may not restore, even when their own files look intact. Before you adopt the pattern, confirm with your own test restores how many increments a restore must read, how long a restore takes from the end of a long chain, and where the base backups are stored and protected. Plan periodic full backups to cap chain length, keep the retention of older chains aligned with your recovery objectives, and treat each full backup as a new starting point that must also be restore-tested.
Configuration options and when each fits
This table compares three ways to schedule ClickHouse backups: full only, full plus incremental, and periodic full backups that reset the chain. Read each column against your storage budget, restore time tolerance and the number of people who will run a restore under pressure. The main takeaway is that incremental savings come with a longer chain of dependencies, so the reset interval is the setting to decide first.
| Schedule | Storage and restore profile | Dependency and operational load |
|---|---|---|
| Full backup only | Each run stores all data, so storage grows with every retained copy. A restore reads a single backup and has no chain to assemble. | No dependency on earlier backups. Runs take the longest and put the most load on the cluster and the storage target each time. |
| Full plus incremental | Later runs store only data that changed since the base backup, which lowers storage use. A restore must read the full backup and every incremental after it. | Each incremental depends on its base. Losing or damaging one link can block restores past that point. Confirm the base backup location and destination naming with your own run records before relying on it. |
| Periodic full that resets the chain | Storage sits between the two options, with a full backup at a set interval. Restore length is bounded by the number of incrementals since the last full. | Requires a rule for when a new full starts and a check that it completed. Keeps the chain short enough to test regularly on a spare cluster. |
How to set up an incremental backup schedule with checks
This sequence takes a first full ClickHouse backup, adds incremental backups that reference it, and confirms each one through system.backups and a test restore. The result is a recorded chain of backup names and base references that your team can restore from and audit.
Confirm the ClickHouse version and backup destination
Your platform or database owner confirms the exact ClickHouse server version in each environment, checks it against the supported versions in the project's security policy, and confirms the backup destination: a local disk, S3-compatible storage or Azure Blob Storage. The destination must be reachable from every node that runs BACKUP, and it should sit outside the cluster's failure domain. Done means you have the version string written down, a test write to the destination succeeds, and the storage credentials are stored in a secrets system rather than in SQL scripts.
Run the initial full backup
Run a full BACKUP of the target database or tables to the chosen destination, using a clear naming pattern that includes the date and the backup type, for example as an illustrative name such as full_2026_10_16. Run it during a low-load window if the dataset is large, because the backup reads every part. Done means the statement returns without error and the backup appears as completed in system.backups. Record the backup name at this point, since every later incremental depends on it.
Run an incremental backup against the full base
Run a BACKUP with the base_backup setting that points to the full backup's destination and name. ClickHouse writes only the parts that changed since the base, so this step shows whether the incremental chain works at all. Done means the statement completes and system.backups shows the incremental entry with its base reference. If the status is failed or the base reference is empty, stop and fix the destination path or credentials before scheduling anything.
Check status and size in system.backups
Query system.backups for the status, start and end times, total size and error columns of both the full and incremental entries. Confirm that the incremental is materially smaller than the full backup on data that has not changed, and that no entry is stuck in a creating or failed state. Done means both rows show a successful status and the sizes match what you expected for the amount of data written since the base. Capture this query output in your runbook as the baseline check for the schedule.
Record backup names and base references
Write a manifest that lists each backup name, its type, its timestamp, its base reference, the destination path and the ClickHouse version used. Store the manifest outside the ClickHouse cluster, alongside the backup destination, so it survives a server loss. Done means someone who did not run the backups can follow the manifest to identify the full backup and every incremental that depends on it. Flag in the manifest the restore order, because an incremental cannot be restored without the chain beneath it.
Run a first test restore to a spare cluster
Restore the most recent incremental, including its base chain, to a spare cluster that is not connected to production writes. Verify row counts and a few known checksums against the source tables, and confirm the restored database answers representative queries. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, and recommends regular restore tests on spare clusters. Done means the restore completes, the counts match, and you have written the evidence to the manifest.
Schedule incrementals and periodic full backups
Once the manual steps pass, schedule the incremental runs with your scheduler, and set a rule for when a new full backup starts a fresh chain, such as after a fixed number of incrementals or a chosen interval. Confirm with your team how long the chain must stay on storage to meet your recovery point and recovery time objectives, and how old chains are removed. Done means the scheduled job writes a row to the manifest on every run, failures alert the on-call channel, and the restore test repeats on a schedule your team has agreed in writing.
Protecting incremental backup chains from deletion and misuse
An incremental chain is only as safe as its weakest link. A missing base, a deleted middle backup or a misused credential can make every later increment unusable, so the destination needs the same protection as the data it holds. The controls below cover who can write and delete, how credentials are kept apart, how retention respects the chain, and how failures get noticed.
Restrict write and delete access
Treat the backup destination as a separate security boundary. Only the backup process should be able to write new backup objects, and only a small, named administrative path should be able to delete them. Check that ordinary database administrators and application accounts cannot remove or overwrite objects under the backup prefix. Confirm with your storage vendor how object-level permissions and deletion protection work on your platform, since the ClickHouse backup documentation describes the BACKUP command but not your storage's access model.
Separate backup credentials from database users
The account that runs BACKUP should not be the same account that serves analytical queries or holds administrative rights over tables. Create a dedicated ClickHouse user for backup jobs with only the grants needed for BACKUP and RESTORE on the required databases. Review the access control documentation for how users, roles and grants are defined in SQL, and confirm that the default user is restricted so no backup or application path falls back to it.
Manage S3 and Azure credentials
Backups to S3-compatible storage or Azure Blob Storage need storage credentials that ClickHouse can use without exposing them in query logs, configuration files shared across teams or scripts in source control. Decide where these secrets live, who can rotate them, and how a rotation is tested against a restore. Ask your team to confirm the exact mechanism ClickHouse supports for your storage type and version, and keep the credential scope as narrow as the storage platform allows.
Keep the chain intact under retention
Retention rules that delete old objects can break an incremental chain if they remove a base backup that later increments still reference. Define retention at the level of complete chains, not individual backup files, and only expire a chain after its full backup and all dependent increments are past the retention window. Verify this with a test that deletes the oldest chain in a non-production bucket and confirms that the newest chain still restores.
Encrypt data at rest in the destination
Backups contain the same sensitive rows as the live cluster, often in a single set of objects that is easier to copy than the production tables. Confirm whether encryption at rest is applied by the storage platform, by ClickHouse, or both, and who holds the keys. Agree on key rotation and on who can use keys for restores. Encryption does not replace access control, so the write and delete restrictions above still apply.
Monitor failed and partial backups
A backup that fails silently leaves a gap that only appears during a restore. Track the status of each BACKUP operation, including the system.backups table, and alert on failed, cancelled or incomplete runs. Alert also when a scheduled backup does not start. Confirm that alerts reach the team that owns recovery, and that a missing increment triggers a review of the chain before the next scheduled run.
Questions teams ask before running incremental ClickHouse backups
How much effort does setting up incremental ClickHouse backups take?
Effort depends mainly on the number of tables and databases in scope, how many clusters or shards need coverage, and where the backups will land. Setup covers the BACKUP and RESTORE statements, the storage target and its credentials, a schedule that chains incremental backups onto a base, and monitoring of the backup jobs in system.backups. Teams with a single cluster and one storage target usually have the smallest workload, while multi-shard clusters, several storage targets and strict retention rules add design and testing work. Ask any provider to break the effort into design, build, restore testing and handover, so you can see which parts depend on your environment.
Can an in-house team set up incremental ClickHouse backups without outside help?
Yes, a team with Linux, ClickHouse and storage administration skills can build incremental backups from the official documentation. The harder part is proving the backups restore. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, so the team needs a spare cluster, a repeatable restore procedure and someone who owns the evidence. Consider outside help if nobody on the team has run a restore end to end, if the platform has no spare capacity for test restores, or if compliance requires an independent view of the design.
How long does a restore from an incremental chain take, and what drives it?
Restore time is driven by the amount of data to read, the number of backups in the chain and the speed of the storage and network between the backup target and the cluster. A restore from an incremental backup must read the base backup and every incremental backup that depends on it, so a long chain means more reads before the data is back. Restore time also depends on table sizes, merge state, replication catch-up after the restore and whether you restore the whole database or selected tables. Measure restore time on a spare cluster with representative data, because a rough estimate from backup size is not reliable.
How often should full backups run in an incremental schedule?
Schedule full backups at intervals that match your recovery point and recovery time objectives, and stop extending the chain once restore time starts to exceed what the business accepts. Frequent fulls shorten restore chains and reduce the blast radius of a damaged base backup, but they increase storage use and backup load on the cluster. Longer intervals save storage and load but make each restore depend on more incremental files. A common pattern is a periodic full backup with incremental backups between them, and the interval should be set from your own restore tests rather than from a generic rule.
What does the work need from our side: storage, credentials and a spare cluster?
The work needs four things from your side:
- Backup storage that is separate from the cluster, such as local disk on another host, S3-compatible object storage or Azure Blob Storage, with capacity planned for full and incremental sets.
- Credentials for the storage target, managed through a secrets process your team controls, and a dedicated ClickHouse user with only the grants needed to run backups and restores.
- A spare ClickHouse cluster or environment for restore tests, sized close enough to production to be meaningful.
- Named owners for schedules, alerts and sign-off on restore evidence.
Confirm each item early, because storage access and spare capacity often decide the timeline more than the SQL itself.
What should a quote for incremental ClickHouse backups include?
A quote should follow from a written scope and list the systems, clusters and databases covered, the storage targets, the backup schedule and retention, and the restore tests included. It should separate one-time work, such as design, build and first restore test, from recurring work, such as monitoring, scheduled restore tests and upgrade-related backup checks. Ask for the assumptions behind each line, the client responsibilities, the acceptance criteria for a successful restore test and what happens if tests find gaps. DeployOpen works from fixed scopes agreed in writing before work starts, and ongoing support is available as a retainer.
What breaks most often with incremental backup chains?
The most common failures are a missing or damaged base backup, incremental backups that were written to a target with insufficient retention, credentials that expire between runs and schedules that overlap so that jobs fail silently. A chain is only as recoverable as its weakest link, so a failed base or a deleted intermediate backup can make later incrementals unusable. Check the status of each backup in system.backups, alert on failures, keep retention rules aligned with the full-backup interval and run restore tests that use the chain as it exists in storage, not a copy made on the day of the test.
Limits to confirm in your environment before relying on incremental backups
An incremental chain is only as good as the release, storage and retention rules underneath it. The items below are the points where a chain can look healthy and still fail at restore time, so each one should be confirmed against the vendor documentation for your release and proven with a test restore.
Exact base_backup syntax and options
The base_backup setting, its accepted destinations and the options that come with it can change between releases. Confirm the exact syntax and supported options for the ClickHouse version you run in production, not the version in a blog post or an older forum answer. Record the version alongside each backup so the restore runbook names the same build that produced the chain.
Tables with updates and mutations
Incremental behavior depends on which parts changed since the base backup. Check how your tables with ALTER UPDATE, ALTER DELETE, merges and lightweight deletes are captured, and whether a backup taken during heavy mutation activity restores to a consistent state. Test a chain on a representative table that receives the same write pattern as production.
Destination compatibility with your storage
Local disk, S3-compatible object storage and Azure Blob Storage are supported backup destinations, but each vendor's S3-compatible implementation can differ in multipart upload, listing and consistency behavior. Confirm that your specific object store works with the destination configuration you plan to use, and that writes and reads succeed at the sizes your backups produce.
Restore across ClickHouse versions
A chain written by one release may not restore cleanly on another, especially after an upgrade that changes table metadata or defaults. Decide which versions you must be able to restore into, and test a restore from an older chain onto the target version before an upgrade window opens. Keep the upgrade plan and the restore plan in the same document.
Retention and deletion rules
A base backup that is deleted, moved or expired by a lifecycle policy breaks every incremental that depends on it. Review object storage lifecycle rules, backup cleanup scripts and manual housekeeping for anything that removes older backups. Set retention so that whole chains expire together, and alert when a base is missing before the chain is needed.
Access and key management
Backup credentials, bucket permissions and any encryption keys used for the destination must be available to the restore team during an incident, not only to the team that wrote the backup. Confirm how credentials are stored, who can read and write the destination, and whether a restore can run from a separate account or site if the primary environment is lost.
Why a chain is only proven after a test restore
The ClickHouse backup documentation warns that a backup that has never been test-restored will probably not restore properly when you need it. For an incremental chain, that risk is higher than for a single full backup, because each BACKUP in the chain depends on the ones before it. A chain can look healthy in system.backups and still fail at restore time if a base in the middle is missing, moved or damaged. The only way to know the chain resolves is to restore it and check the result.
A test restore should run on a spare cluster, never on production. Restore the newest backup in the chain, not an older one, because that restore exercises every base it depends on. Confirm that the restore completes without errors, that the tables and row counts match what you expected at the backup time, and that the restored data can be queried. Record the backup name, the restore command, the settings used and the outcome, so you can show the evidence later. Repeat the test on a schedule, and again after changing the storage location, credentials or retention policy for the chain.
Run the test against the same storage path, credentials and named collections your production job uses, since a restore that works only with an administrator's local configuration proves less than it appears to. Ask your team to confirm how long a restore takes on the spare cluster, because that figure, not the backup duration, is what decides your recovery time. For the full procedure, including partial restores and checksum comparisons, see the sibling topic on testing ClickHouse restores.
How DeployOpen scopes and delivers incremental backup work
Incremental backups only protect you if the chain is scheduled, tested and documented from the start. DeployOpen works through the steps below under a written scope, so the backup schedule, the restore evidence and the operating runbook arrive together.
Written scope before work starts
Most engagements are fixed-scope, so the cluster, databases, tables, storage target, backup cadence and restore objectives are agreed in writing before any build begins. Check that the scope names which tables are included, which storage endpoint receives the backups, and who owns credentials. A quote follows from that written scope, so unclear boundaries show up as cost questions later.
Schedules in your infrastructure
DeployOpen implements the incremental schedule inside your own cloud accounts, data centers or restricted networks, using the BACKUP statement with base_backup pointing to the prior full or incremental backup. The work covers scheduler placement, retry behavior and alerting on failed runs. Confirm that the backup credentials are stored in a secrets system you already operate.
Restore tests on a spare cluster
Every chain is proven by restoring it to a separate cluster, not by checking that the backup job reported success. The test covers the full chain from the latest incremental back to its base, row counts on representative tables and a timed run that you can compare against your recovery objectives. The ClickHouse documentation warns that a backup never test-restored will probably not restore properly when needed.
Chain and runbook documentation
DeployOpen documents each backup in the chain, its parent, its storage location and its retention date, so an operator can see which full backup every incremental depends on. The runbook gives step-by-step restore commands for a single table, a partition and a whole database, with the decision points for choosing between them. Ask for the runbook to be tested by someone who did not write it.
Audit evidence, not audit opinions
DeployOpen prepares the evidence an assessor will ask for: schedules, configuration, restore test records and access logs for the backup store. DeployOpen does not issue audit reports, attestations or certifications. Independent auditors and certification bodies make those determinations, so confirm early which evidence format your assessor expects.
Retainers for ongoing testing
Once the chain is live, restore tests and version upgrades keep needing attention. Optional retainers cover recurring restore tests on a spare cluster, periodic full backups, review of the chain length and updates to the runbook after ClickHouse upgrades. Ask for the retainer scope to name the test frequency, the cluster used and who is notified when a test fails.
