How to back up ClickHouse

Pick a method

In October 2026 the current ClickHouse releases are 26.9 (stable) and 26.8 and 26.3 (long-term support). A self-hosted server has three ways to back up:

Check the size first. Parts on disk are already compressed, so a backup comes out close to this figure:

clickhouse-client --query "SELECT database, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active GROUP BY database"

If analytics shows 48.00 GiB, plan for about 48 GiB per full backup on the backup disk, and as much again for each copy you keep off the server.

Allow a backup destination

Disk() and File() refuse to write anywhere the server config doesn't list. Create a directory, ideally on a different disk from /var/lib/clickhouse, and give it to the clickhouse user:

sudo mkdir -p /var/backups/clickhouse && sudo chown clickhouse:clickhouse /var/backups/clickhouse

<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/var/backups/clickhouse/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/var/backups/clickhouse/</allowed_path>
    </backups>
</clickhouse>

• <disks><backups> defines a local disk named backups; Disk('backups', ...) refers to it by that name.
• allowed_disk lists the disks BACKUP may use. Repeat the element to allow more.
• allowed_path does the same for File(). A relative File() path lands under the first allowed_path.

sudo systemctl restart clickhouse-server

Back up a database

clickhouse-client --query "BACKUP DATABASE analytics TO Disk('backups', 'analytics-2026-10-04.zip')"

If your ClickHouse user has a password, add --password (the client then asks for it) or use the client config file shown under the nightly script. The command returns an operation ID and BACKUP_CREATED when it finishes; you don't stop the server. The .zip name writes one archive. .tar, .tar.gz and .tar.zst work too, and a name with no extension writes a directory. TO File('analytics-2026-10-04.zip') does the same through allowed_path. Variations:

• TABLE analytics.events backs up one table; PARTITIONS '2026-10-01' narrows it to partitions.
• EXCEPT TABLES analytics.scratch leaves a table out of a database backup.
• ALL takes every database, system included; its log tables, such as query_log, back up like any other table and can be large.
• SETTINGS compression_method = 'lzma', compression_level = 3 sets compression; password = '...' encrypts, for .zip and .zipx names only.

Include users, roles and functions

Access entities created with SQL are saved only when you name their system tables. ClickHouse stores them as the SQL statements that recreate them:

clickhouse-client --query "BACKUP DATABASE analytics, TABLE system.users, TABLE system.roles, TABLE system.settings_profiles, TABLE system.row_policies, TABLE system.quotas, TABLE system.functions TO Disk('backups', 'analytics-full-2026-10-04.zip')"

On restore, users that already exist are skipped by default. Anything defined in users.xml or config.d is a file: back up /etc/clickhouse-server with ordinary file backups, encrypted, since it holds password hashes and keys.

Back up to S3

S3() takes a URL ending in the backup's own directory, plus an access key ID and secret. For S3-compatible storage, use the provider's endpoint followed by the bucket and path. See setting up an S3 bucket for the bucket and key.

clickhouse-client --query "BACKUP DATABASE analytics TO S3('https://my-ch-backups.s3.us-east-1.amazonaws.com/analytics/2026-10-04/', 'ACCESS_KEY_ID', 'SECRET_ACCESS_KEY')"

Keys typed into queries end up in scripts and shell history. A named collection keeps them in the server config instead:

<clickhouse>
    <named_collections>
        <s3_backups>
            <url>https://my-ch-backups.s3.us-east-1.amazonaws.com/analytics/</url>
            <access_key_id>ACCESS_KEY_ID</access_key_id>
            <secret_access_key>SECRET_ACCESS_KEY</secret_access_key>
        </s3_backups>
    </named_collections>
</clickhouse>

clickhouse-client --query "BACKUP DATABASE analytics TO S3(s3_backups, '2026-10-04/')"

The second argument is appended to the collection's url. Make the file readable only by root and the clickhouse group (chown root:clickhouse, chmod 640). From 26.8, ClickHouse refuses .zip names on S3; use a directory, as here, or .tar.gz.

Incremental backups

base_backup points a new backup at an earlier one; only what changed since that base is stored. A common pattern is a full backup on Sunday and a daily incremental that names Sunday's backup as its base:

clickhouse-client --query "BACKUP DATABASE analytics TO Disk('backups', 'analytics-2026-10-05-inc.zip') SETTINGS base_backup = Disk('backups', 'analytics-2026-10-04.zip')"

To restore, name only the incremental; ClickHouse reads the unchanged files from the base. With S3() destinations, add use_same_s3_credentials_for_base_backup = 1 to reuse the keys for the base.

Run long backups in the background

A large backup can outlast your terminal session. ASYNC returns at once with status CREATING_BACKUP; SETTINGS id names the operation so you can find it:

clickhouse-client --query "BACKUP DATABASE analytics TO Disk('backups', 'analytics-2026-10-04.zip') SETTINGS id = 'analytics-2026-10-04' ASYNC"

clickhouse-client --query "SELECT status, error, formatReadableSize(total_size) AS size, start_time, end_time FROM system.backups WHERE id = 'analytics-2026-10-04' FORMAT Vertical"

The status moves to BACKUP_CREATED or BACKUP_FAILED, with the reason in error; restores show RESTORING, RESTORED or RESTORE_FAILED. system.backups empties when the server restarts. system.backup_log keeps the history.

Restore

Restore next to the live data first, then compare:

clickhouse-client --query "RESTORE DATABASE analytics AS analytics_restored FROM Disk('backups', 'analytics-2026-10-04.zip')"

clickhouse-client --query "RESTORE TABLE analytics.events AS analytics.events_restored FROM Disk('backups', 'analytics-2026-10-04.zip')"

• By default RESTORE creates missing databases and tables. It fills an existing table only if it is empty and has the same definition; otherwise it stops with an error (see below).
• allow_non_empty_tables = true adds the backup's rows to a table that has data, so rows can be duplicated.
• PARTITIONS '2026-10-01' restores only those partitions.
• structure_only = true restores the CREATE statements without data. From 26.8, restore_table_data, restore_access_entities and restore_functions switch each category on or off.
• RESTORE isn't transactional: if it fails partway, tables it already finished stay restored.

On a new server: install ClickHouse, add the same backup_disk.xml, copy the archive into /var/backups/clickhouse, restart, and run RESTORE. Check SELECT count() on your main tables against the old figures.

Run it every night

The client reads its login from /root/.clickhouse-client/config.xml (chmod 600). Use an admin user, or a dedicated one with the BACKUP privilege on what it saves; current versions also check the destination against the user's SOURCES grants (FILE, DISK, S3).

<config>
    <user>default</user>
    <password>long-random-password</password>
</config>

The script starts the backup in the background, polls until it ends, exits non-zero on failure and keeps two days of archives:

#!/bin/bash
set -euo pipefail
NAME="analytics-$(date +%F-%H%M)"

clickhouse-client --query "BACKUP DATABASE analytics TO Disk('backups', '$NAME.zip') SETTINGS id = '$NAME' ASYNC" > /dev/null

while true; do
  STATUS=$(clickhouse-client --query "SELECT status FROM system.backups WHERE id = '$NAME'")
  case "$STATUS" in
    BACKUP_CREATED) break ;;
    CREATING_BACKUP) sleep 30 ;;
    *) echo "$(date -Is) $NAME failed: $STATUS" >&2
       clickhouse-client --query "SELECT error FROM system.backups WHERE id = '$NAME'" >&2
       exit 1 ;;
  esac
done

find /var/backups/clickhouse -name 'analytics-*.zip' -mtime +1 -delete
echo "$(date -Is) created $NAME.zip"

sudo chmod 700 /usr/local/bin/clickhouse-backup.sh

30 2 * * * root /usr/local/bin/clickhouse-backup.sh >> /var/log/clickhouse-backup.log 2>&1

Then copy /var/backups/clickhouse off the server, to object storage or another provider (3-2-1 rule). Cron schedules covers timing and alerts. Once a month, restore the newest archive on a spare server and time it (testing restores).

Clusters and replicated tables

On a cluster, BACKUP DATABASE analytics ON CLUSTER 'main' TO S3(...) runs on every host, coordinated through ClickHouse Keeper or ZooKeeper, and RESTORE ... ON CLUSTER reverses it. All hosts write into one destination, so use shared storage such as S3, and a directory name: archives fail with Using archives with backups on clusters is disabled.

clickhouse-backup and FREEZE

clickhouse-backup is Altinity's MIT-licensed tool (2.8.1 in September 2026). It must run on the ClickHouse host, because it reads /var/lib/clickhouse, and is configured in /etc/clickhouse-backup/config.yml. clickhouse-backup create_remote <name> creates and uploads a backup, list remote shows them, and restore_remote <name> brings one back. --rbac adds users and roles, --configs the server's config files, and backups_to_keep_remote sets retention. With use_embedded_backup_restore: true it drives BACKUP and RESTORE instead of freezing.

ALTER TABLE analytics.events FREEZE WITH NAME 'nightly' hard-links the table's parts under /var/lib/clickhouse/shadow/nightly/, almost instantly. It saves data only, not the table definition, and only on the local server. To restore, copy the parts into the table's detached directory and run ALTER TABLE ... ATTACH PARTITION; ALTER TABLE analytics.events UNFREEZE WITH NAME 'nightly' removes the frozen copy.

Common errors

Frequently asked questions

Does ClickHouse BACKUP need downtime?

No. It runs on the live server. Add ASYNC for long backups so the client doesn't have to stay connected, and follow progress in system.backups.

Are ClickHouse backups incremental?

Only when you ask. Add SETTINGS base_backup = Disk(...) or S3(...) and the new backup stores only what changed since that base, which you must keep.

Does a ClickHouse backup include users and passwords?

Only users and roles created with SQL, and only if you include system.users and the related system tables. Users defined in users.xml are config files; back them up as files.

Should I use clickhouse-backup or the BACKUP command?

BACKUP is built in and needs nothing else. Altinity's clickhouse-backup adds remote retention, a REST API and config-file backups, and can drive BACKUP itself.

Can I restore a ClickHouse backup under a different name?

Yes: RESTORE DATABASE db AS db_restored, or RESTORE TABLE db.t AS db.t_restored, from the same backup.