ClickHouse Backup and Restore: BACKUP, Incrementals and S3 in Practice

A working ClickHouse backup is one you have restored at least once. ClickHouse has native BACKUP and RESTORE commands that write to a local disk, S3 or Azure, support incremental backups and run in the background. They are simple to start with, and most of the real work is in the details: where backups go, how long the incremental chain gets, and whether a restore fits in your recovery window.

The first point is the one teams most often skip. The ClickHouse documentation puts it directly: replication protects from hardware failures, but it does not protect against human errors, and in many cases such mistakes affect all replicas. A wrong ALTER ... DELETE, a DROP on the wrong cluster or a bad migration is replicated as faithfully as a good insert.

Step 1: give ClickHouse a place to write backups

For a local or mounted target, define a disk and allow it for backups. This is the configuration from the official guide, and it needs a server restart:

<!-- /etc/clickhouse-server/config.d/backup_disk.xml, then restart -->
<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/backups/</allowed_path>
    </backups>
</clickhouse>

For S3 you do not need a disk at all: the S3() destination takes the endpoint and path, the access key and the secret directly in the command. Azure Blob Storage works the same way with AzureBlobStorage(). A local disk on the same server is only a staging area; a backup that lives on the machine it protects is not a backup.

Step 2: take a full backup and watch it

-- full backup of one database, in the background
BACKUP DATABASE analytics
TO S3('https://s3.eu-central-1.amazonaws.com/acme-ch-backups/analytics/2026-11-12-full',
      '<key id>', '<secret>')
ASYNC;

-- returns an id; follow it
SELECT id, name, status, num_files, uncompressed_size, compressed_size, error
FROM system.backups
ORDER BY start_time DESC
LIMIT 5;

Without ASYNC the command blocks until the backup is complete, which on a large database means a client session that must stay open for hours. With ASYNC it returns an id immediately. In system.backups the status moves from CREATING_BACKUP to BACKUP_CREATED, or to BACKUP_FAILED with the reason in error. Restores report RESTORING, RESTORED or RESTORE_FAILED. Every operation is also recorded in system.backup_log, which is what your monitoring should alert on.

You can back up a single table, a database, or ALL with exclusions, and the syntax supports EXCEPT TABLES and EXCEPT DATA FROM TABLES to keep a definition without its rows, for example for large staging tables you can rebuild.

Step 3: incremental backups with base_backup

A full backup of a multi-terabyte database takes time and space, so the usual pattern is a periodic full backup plus incrementals. An incremental stores only what changed since the backup named in base_backup:

BACKUP DATABASE analytics
TO S3('https://s3.../analytics/2026-11-13-incr', '<key id>', '<secret>')
SETTINGS base_backup = S3('https://s3.../analytics/2026-11-12-full',
                          '<key id>', '<secret>')
ASYNC;

The catch is in the documentation: restoring an incremental backup requires all the backups it is built on. If someone applies an S3 lifecycle rule that deletes the full backup after 30 days, every incremental based on it becomes useless. Keep the chain short, start a new full backup regularly, and let retention delete whole chains, never single links.

Step 4: restore, the part that matters

-- restore next to the original, never over it
RESTORE TABLE analytics.events AS analytics.events_restored
FROM S3('https://s3.../analytics/2026-11-13-incr', '<key id>', '<secret>');

-- only the schema, e.g. to rebuild an empty replica of the structure
RESTORE DATABASE analytics FROM Disk('backups', 'analytics-full.zip')
SETTINGS structure_only = 1;

Restoring under a new name lets you recover a few partitions after a bad delete without touching the live table. If you are recovering from an accidental mass delete, our notes on deleting rows in ClickHouse explain why lightweight and mutation deletes behave differently, which matters when you decide what to copy back. Settings such as restore_table_data and restore_access_entities control what a restore brings back; users, roles and quotas are included only if they were created with SQL, not defined in users.xml.

Clusters, concurrency and alternatives

• ON CLUSTER runs a backup or restore across the cluster. Keeper coordinates it, so a Keeper or host problem during a long backup is a failure mode to plan for.
• Concurrency. allow_concurrent_backups and allow_concurrent_restores default to true. Set them to false in the <backups> section if you want only one operation on the cluster at a time.
• ALTER TABLE ... FREEZE creates hard links in /var/lib/clickhouse/shadow/, which costs little space for old data. The documentation notes the copies are not managed by the server and sit on the same hardware, so they must be shipped elsewhere. The third-party clickhouse-backup tool automates this approach and is still common in older setups.
• Filesystem snapshots (for example ZFS) are suggested in the docs on dedicated replicas excluded from query traffic, not on the nodes serving live queries.

What changes with multi-terabyte data

• Measure restore time, not backup time. Your recovery time is how long it takes to read the full backup plus every incremental from S3 and write it back. Measure it on a spare cluster at full size; the docs recommend practising restores on a spare cluster regularly. Our backup restore testing guide covers how to turn that into a routine.
• Protect production while backing up. max_backup_bandwidth (0, unlimited, by default) limits read speed per backup, and backup_threads limits parallelism. A full backup at full speed competes with merges and queries for disk and network.
• Cost of storage class. s3_storage_class sets the class for backup objects. Colder classes are cheaper but can make restores slower or more expensive; check this before an incident, not during one.
• The 50 GB safety net. By default max_table_size_to_drop and max_partition_size_to_drop are 50 GB: ClickHouse refuses DROP TABLE, TRUNCATE or DROP PARTITION above that size. Do not set them to 0 to make scripts pass. On large tables this check is often the last thing between a typo and a full restore.
• Keep credentials out of scripts. S3 keys typed into BACKUP commands end up in shell history and job definitions. Use a restricted IAM user with access to the backup bucket only, and keep the bucket in a separate account or with object lock if you need protection from a compromised server.