Backup and Restore in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/operations_/backup_restore/00_overview.md)

> This section broadly covers backups and restores in ClickHouse. For a more
> detailed description of each backup method, see the pages for specific methods
> in the sidebar.

## Introduction[​](#introduction "Direct link to Introduction")

While [replication](/docs/engines/table-engines/mergetree-family/replication) provides protection from hardware failures, it doesn't
protect against human errors: accidental deletion of data, deletion of the wrong
table or a table on the wrong cluster, and software bugs that result in incorrect
data processing or data corruption.

In many cases mistakes like these will affect all replicas. ClickHouse has built-in
safeguards to prevent some types of mistakes, for example, by [default](/docs/operations/settings/settings#max_table_size_to_drop)
you can't just drop tables with a `MergeTree` family engine containing more than
50 Gb of data. However, these safeguards don't cover all possible cases and
problems can still occur.

To effectively mitigate possible human errors, you should carefully prepare a
strategy for backing up and restoring your data **in advance**.

Each company has different resources available and business requirements, so
there's no universal solution for ClickHouse backups and restores that will fit
every situation. What works for one gigabyte of data likely won't work for tens
of petabytes of data. There are a variety of possible approaches with their own pros
and cons, which are presented in this section of the docs. It is a good idea to
use several approaches instead of just one such as to compensate for their various
shortcomings.

Note

Keep in mind that if you backed something up and never tried to restore it,
chances are that the restore won't work properly when you actually need it (or at
least it will take longer than the business can tolerate). So whatever backup
approach you choose, make sure to automate the restore process as well, and practice
it on a spare ClickHouse cluster regularly.

The following pages detail the various backup and
restore methods available in ClickHouse:

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Backup/restore using local disk or S3 disk](/docs/operations/backup/disk) Details backup/restore to or from a local disk or S3 disk|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Backup/restore using S3 endpoint](/docs/operations/backup/s3_endpoint) Details backup/restore to or from an S3 endpoint|  |  |  |  | | --- | --- | --- | --- | | [Backup/restore using AzureBlobStorage](/docs/operations/backup/azure) Details backup/restore to or from Azure blob storage|  |  | | --- | --- | | [Alternative methods](/docs/operations/backup/alternative_methods) Discusses alternative backup methods | | | | | | | | | |

Backups can:

- be [full or incremental](#backup-types)
- be [synchronous or asynchronous](#synchronous-vs-asynchronous)
- be [concurrent or non-concurrent](#concurrent-vs-non-concurrent)
- be [compressed or uncompressed](#compressed-vs-uncompressed)
- use [named collections](#using-named-collections)
- be password protected
- be taken of [system tables, log tables, or access management tables](#system-backups)

## Backup types[​](#backup-types "Direct link to Backup types")

Backups can be either full or incremental. Full backups are a complete copy of the
data, while incremental backups are a delta of the data from the last full backup.

Full backups have the advantage of being a simple, independent (of other backups)
and reliable recovery method. However, they can take a long time to complete and
can consume a lot of space. Incremental backups, on the other hand, are more
efficient in terms of both time and space, but restoring the data requires all
the backups to be available.

Depending on your needs, you may want to use:

- **Full backups** for smaller databases or critical data.
- **Incremental backups** for larger databases or when backups need to be done frequently and cost effectively.
- **Both**, for instance, weekly full backups and daily incremental backups.

## Synchronous vs asynchronous backups[​](#synchronous-vs-asynchronous "Direct link to Synchronous vs asynchronous backups")

`BACKUP` and `RESTORE` commands can also be marked `ASYNC`. In this case, the
backup command returns immediately, and the backup process runs in the background.
If the commands aren't marked `ASYNC`, the backup process is synchronous and
the command blocks until the backup completes.

## Concurrent vs non-concurrent backups[​](#concurrent-vs-non-concurrent "Direct link to Concurrent vs non-concurrent backups")

By default, ClickHouse allows concurrent backups and restores. This means you
can initiate multiple backup or restore operations simultaneously. However,
there are server-level settings that let you disallow this behavior. If you set
these settings to false, only one backup or restore operation is allowed to run
on a cluster at a time. This can help avoid resource contention or potential
conflicts between operations.

To disallow concurrent backup/restore, you can use these settings respectively:

```
<clickhouse>
    <backups>
        <allow_concurrent_backups>false</allow_concurrent_backups>
        <allow_concurrent_restores>false</allow_concurrent_restores>
    </backups>
</clickhouse>
```

The default value for both is true, so by default concurrent backup/restores are
allowed. When these settings are false on a cluster, only a single backup/restore
is allowed to run on a cluster at a time.

## Compressed vs uncompressed backups[​](#compressed-vs-uncompressed "Direct link to Compressed vs uncompressed backups")

ClickHouse backups support compression through the `compression_method` and `compression_level` settings.

When creating a backup, you can specify:

```
BACKUP TABLE test.table
  TO Disk('backups', 'filename.zip')
  SETTINGS compression_method='lzma', compression_level=3
```

## Using named collections[​](#using-named-collections "Direct link to Using named collections")

Named collections allow you to store key-value pairs (like S3 credentials, endpoints, and settings) that can be reused across backup/restore operations.
They help to:

- Hide credentials from users without admin access
- Simplify commands by storing complex configuration centrally
- Maintain consistency across operations
- Avoid credential exposure in query logs

See ["named collections"](/docs/operations/named-collections) for further details.

## Backing up system, log or access management tables[​](#system-backups "Direct link to Backing up system, log or access management tables")

System tables can also be included in your backup and restore workflows, but their
inclusion depends on your specific use case.

System tables that store historic data, such as those with a `_log` suffix (e.g.,
`query_log`, `part_log`), can be backed up and restored like any other table.
If your use case relies on analyzing historic data - for example, using `query_log`
to track query performance or debug issues - it's recommended to include these
tables in your backup strategy. However, if historic data from these tables is
not required, they can be excluded to save backup storage space.

System tables related to access management, such as users, roles, row\_policies,
settings\_profiles, and quotas, receive special treatment during backup and restore operations.
When these tables are included in a backup, their content is exported to a special
`accessXX.txt` file, which encapsulates the equivalent SQL statements for creating
and configuring the access entities. Upon restoration, the restore process
interprets these files and re-applies the SQL commands to recreate the users,
roles, and other configurations. This feature ensures that the access control
configuration of a ClickHouse cluster can be backed up and restored as part of
the cluster's overall setup.