Altinity Backup for ClickHouse®

A tool for easy backup and restore utility for ClickHouse databases with support for many cloud and non-cloud storage types.

Don't run clickhouse-backup remotely

During backup and restore data, clickhouse-backup requires access to the same files as clickhouse-server in /var/lib/clickhouse folders. For that reason, it's required to run clickhouse-backup on the same host or same Kubernetes Pod or the neighbor container on the same host where clickhouse-server ran. WARNING You can backup and restore only schema when connect to remote clickhouse-server hosts.

Features

• Easy creating and restoring backups of all or specific tables
• Efficient storing of multiple backups on the file system
• Uploading and downloading with streaming compression
• Works with AWS, GCS, Azure, Tencent COS, FTP, SFTP
• Support for Atomic Database Engine
• Support for Replicated Database Engine
• Support for multi disks installations
• Support for custom remote storage types via rclone, kopia, restic, rsync etc
• Support for incremental backups on remote storage

Limitations

• ClickHouse above 1.1.54394 is supported
• Only MergeTree family tables engines (more table types for clickhouse-server 22.7+ and USE_EMBEDDED_BACKUP_RESTORE=true)

Community

Altinity Backup for ClickHouse is a community effort sponsored by Altinity. The best way to reach us or ask questions is:

• Join the Altinity Slack - Chat with the developers and other users
• Log an issue on GitHub - Ask questions, log bugs and feature requests

Support

Altinity is the primary maintainer of clickhouse-backup. We offer a range of software and services related to ClickHouse.

• Official website - Get a high level overview of Altinity and our offerings.
• Altinity.Cloud - Run ClickHouse in our cloud or yours.
• Altinity Support - Get Enterprise-class support for ClickHouse.
• Slack - Talk directly with ClickHouse users and Altinity devs.
• Contact us - Contact Altinity with your questions or issues.
• Free consultation - Get a free consultation with a ClickHouse expert today.

Installation

Download the latest binary from the releases page and decompress with:

tar -zxvf clickhouse-backup.tar.gz

Use the official tiny Docker image and run it on a host with clickhouse-server installed:

docker run -u $(id -u clickhouse) --rm -it --network host -v "/var/lib/clickhouse:/var/lib/clickhouse" \
   -e CLICKHOUSE_PASSWORD="password" \
   -e S3_BUCKET="clickhouse-backup" \
   -e S3_ACCESS_KEY="access_key" \
   -e S3_SECRET_KEY="secret" \
   altinity/clickhouse-backup --help

Build from the sources (required go 1.21+):

GO111MODULE=on go install github.com/Altinity/clickhouse-backup/v2/cmd/clickhouse-backup@latest

Brief description of how clickhouse-backup works

Data files are immutable in the clickhouse-server. During a backup operation, clickhouse-backup creates file system hard links to existing clickhouse-server data parts via executing the ALTER TABLE ... FREEZE query. During the restore operation, clickhouse-backup copies the hard links to the detached folder and executes the ALTER TABLE ... ATTACH PART query for each data part and each table in the backup. A more detailed description is available here: https://www.youtube.com/watch?v=megsNh9Q-dw

Default Config File

By default, the config file is located at /etc/clickhouse-backup/config.yml, but it can be redefined via the CLICKHOUSE_BACKUP_CONFIG environment variable or via --config command line parameter. All options can be overwritten via environment variables. Use clickhouse-backup default-config to print the default config.

Configurable Parameters

Use clickhouse-backup print-config to print the current config. Environment variables can override each config parameter defined in the config file. Their names should be UPPERCASE, and exact names are provided after the comment character #. The following values are not defaults; they explain what each config parameter with an example.

``yaml general: remote_storage: none # REMOTE_STORAGE, choice from:azblob,gcs,s3, etc; ifnonethenuploadanddownloadcommands will fail. max_file_size: 1073741824 # MAX_FILE_SIZE, 1G by default, useless when upload_by_part is true, use to split data parts files by archives backups_to_keep_local: 0 # BACKUPS_TO_KEEP_LOCAL, how many latest local backup should be kept, 0 means all created backups will be stored on local disk # -1 means backup will keep aftercreatebut will delete aftercreate_remotecommand # You can runclickhouse-backup delete local command to remove temporary backup files from the local disk backups_to_keep_remote: 0 # BACKUPS_TO_KEEP_REMOTE, how many latest backup should be kept on remote storage, 0 means all uploaded backups will be stored on remote storage. # If old backups are required for newer incremental backup then it won't be deleted. Be careful with long incremental backup sequences. log_level: info # LOG_LEVEL, a choice fromdebug,info,warning,error` allow_empty_backups: false # ALLOW_EMPTY_BACKUPS # Concurrency means parallel tables and parallel parts inside tables # For example, 4 means max 4 parallel tables and 4 parallel parts inside one table, so equals 16 concurrent streams download_concurrency: 1 # DOWNLOAD_CONCURRENCY, max 255, by default, the value is floor(AVAILABLE_CPU_CORES / 2). If result is < 1, then 1. upload_concurrency: 1 # UPLOAD_CONCURRENCY, max 255, by default, the value is round(sqrt(AVAILABLE_CPU_CORES / 2)). If result is < 1, then 1.

# Throttling speed for upload and download, enforced inline via a token-bucket rate limiter shared across all concurrent workers, so the value is an aggregate cap with smooth (non-bursty) throughput. # Throttling does NOT apply to server-side object disk copy (CopyObject, e.g. S3 server-side copy / Azure copy-blob), because those bytes move inside the cloud provider and never pass through clickhouse-backup. When server-side copy is unavailable (incompatible src/dst remote_storage, or a failed CopyObject falling back to streaming through local memory), the streaming copy IS throttled. # Throttling does NOT apply to remote_storage: custom, because data transfer happens inside your external upload/download command, not through clickhouse-backup. # When clickhouse->use_embedded_backup_restore: true, throttling is delegated to the ClickHouse server via the max_backup_bandwidth query setting passed in the BACKUP/RESTORE SETTINGS clause (requires ClickHouse 25.1+); upload_max_bytes_per_second applies to BACKUP, download_max_bytes_per_second to RESTORE. On older ClickHouse versions embedded transfers are not throttled. download_max_bytes_per_second: 0 # DOWNLOAD_MAX_BYTES_PER_SECOND, 0 means no throttling upload_max_bytes_per_second: 0 # UPLOAD_MAX_BYTES_PER_SECOND, 0 means no throttling # MAX_BROKEN_PART_RATIO, maximum allowed fraction (0..1) of broken data parts (e.g. caused by S3-disk or filesystem failures) that still produces a successful but partial backup during backup creation (create, and the create stage of create_remote). # 0 (default) preserves legacy behavior where any broken part stops the backup completely. When >0 and the broken/total part ratio stays at or below this value, creation skips the broken parts, logs a warning, and the backup is marked successful. # Skipped parts are recorded in the broken_parts section of the table metadata json (next to parts) and counted in the clickhouse_backup_failed_parts_count prometheus metric in server mode, see https://github.com/Altinity/clickhouse-backup/issues/1418 max_broken_part_ratio: 0 # MAX_BROKEN_PART_RATIO

# Buffer tuning for high-bandwidth (10Gbit+) networks, see https://github.com/Altinity/clickhouse-backup/issues/1376 and Examples.md#tuning-for-high-bandwidth-10gbit-networks pipe_buffer_size: 131072 # PIPE_BUFFER_SIZE, size in bytes of the in-memory ring buffer between the compression and the upload/download stream handlers, default 128KB; raise (e.g. 8388608 = 8MB) to let compression run ahead of uploads on fast networks download_copy_buffer_size: 0 # DOWNLOAD_COPY_BUFFER_SIZE, explicit buffer size in bytes for io.CopyBuffer during download/extract, 0 means use the Go default (32KB); raise (e.g. 1048576 = 1MB) to reduce syscalls per file on fast networks

# zstd/gzip compression tuning, see https://github.com/Altinity/clickhouse-backup/issues/1378 and Examples.md#multi-threaded-zstdgzip-compression compression_use_multi_thread: true # COMPRESSION_USE_MULTI_THREAD, enable per-stream multi-threaded zstd/gzip compression and decompression; default true to match pre-1378 behavior (gzip always used pgzip). A single large table dominating a backup is gated by per-stream compression speed and gets no benefit from upload_concurrency/download_concurrency. Set false to save CPU when many tables upload in parallel. Only affects compression_format: zstd and gzip (silently ignored for other formats) compression_threads: 0 # COMPRESSION_THREADS, number of per-stream compression threads when compression_use_multi_thread is enabled (zstd concurrency / pgzip block workers), 0 means auto (GOMAXPROCS); must be unset/0 when compression_use_multi_thread is false compression_buffer_size: 0 # COMPRESSION_BUFFER_SIZE, compression buffer size in bytes, 0 keeps library defaults. Meaning and valid range depend on compression_format and compression_use_multi_thread: zstd = encoder window (power of two, 1024..536870912, e.g. 4194304 = 4MB); single-threaded gzip = DEFLATE window (32..32768); multi-threaded gzip = pgzip block size (>16384). Other formats reject it

# when table data contains in system.disks with type=ObjectStorage, then we need execute remote copy object in object storage service provider, this parameter can restrict how many files will copied in parallel for each table object_disk_server_side_copy_concurrency: 32 # when CopyObject failure or object disk storage and backup destination have incompatible, will warning about possible high network traffic allow_object_disk_streaming: false

# RESTORE_SCHEMA_ON_CLUSTER, execute all schema related SQL queries with ON CLUSTER clause as Distributed DDL. # Check system.clusters table for the correct cluster name, also system.macros can be used. # This isn't applicable when use_embedded_backup_restore: true restore_schema_on_cluster: "" upload_by_part: true # UPLOAD_BY_PART download_by_part: true # DOWNLOAD_BY_PART use_resumable_state: true # USE_RESUMABLE_STATE, allow resume upload and download according to the .resumable file. Resumable state is not supported for custom method in remote storage.

# RESTORE_DATABASE_MAPPING, restore rules from backup databases to target databases, which is useful when changing destination database, all atomic tables will be created with new UUIDs. # The format for this env variable is "src_db1:target_db1,src_db2:target_db2". For YAML please continue using map syntax restore_database_mapping: {}

# RESTORE_TABLE_MAPPING, restore rules from backup tables to target tables, which is useful when changing destination tables. # The format for this env variable is "src_table1:target_table1,src_table2:target_table2". For YAML please continue using map syntax restore_table_mapping: {}

retries_on_failure: 3 # RETRIES_ON_FAILURE, how many times to retry after a failure during upload or download retries_pause: 5s # RETRIES_PAUSE, duration time to pause after each download or upload failure retries_jitter: 30 # RETRIES_JITTER, percent of RETRIES_PAUSE for jitter to avoid same time retries from parallel operations delete_batch_size: 1000 # DELETE_BATCH_SIZE, default batch size for bulk DeleteObjects() requests in remote storages that support batch delete (e.g. S3); upper bound for one API call

watch_interval: 1h # WATCH_INTERVAL, use only for watch command, backup will create every 1h full_interval: 24h # FULL_INTERVAL, use only for watch command, full backup will create every 24h watch_backup_name_template: "shard{shard}-{type}-