Dockerized cron job to backup ClickHouse databases on single host or cluster with shards and replicas. Based on Alpine docker image, clickhouse-backup tool along with it's ability to work as a REST API service. Logrotate has been added to manage the log files produced by the backup agent. If any issues/suggestions, use the issues tab or create a new PR. FYI: If you're seeking to PostgreSQL backup agent, it can be accessed here.

Diagram as Code in Python and described in this blog post

The agent does the following:

• creates scheduled FULL or DIFF backups (POST to /backup/create)
• checks "create backup" action status before every upload (GET to /backup/status)
• uploads each backup to a remote storage (POST to /backup/upload/)
• checks and waits until upload operation finishes (GET to /backup/actions)
• manages log file with API responses and errors
• generates customized output to standard container logs
• if a backup is not uploaded to remote storage, it's marked as failed and will not be used as the last backup for subsequent DIFF backups

Important: according to the clickhouse-backup official FAQ, "incremental backup calculate increment only during execute upload or create_remote command or similar REST API request". In other words, DIFF and FULL local backups are actually the same (clickhouse-backup list local) Clickhouse-backup creates local backups first before uploading them to remote storage.

If you list remote backups using the command (clickhouse-backup list remote), you will notice the distinction between these two backup types. This is why the agent only issues a warning when you attempt to create a DIFF backup for the first time without having any prior FULL backups.

Default settings:

• DIFF backups: every hour from Monday through Friday and Sunday, plus every hour from 0 through 20 on Saturday
• FULL backups: every Saturday at 8.30 PM
• Rotate and compess logs weekly, rotated 14 times before being removed
• Clickhouse-backup API basic authentication is enabled (rlAPIuser)
• Clickhouse server authentication is enabled (rlbackup)
• Remote storage is ftp with authentication enabled
• Backups to keep local: 6
• Backups to keep remote: 336

• docker-compose.yml - describes environment to test the agent locally There are the following services:
  • clickhouse server (clickhouse-server:23.8-alpine)
  • clickhouse-backup (altinity/clickhouse-backup:2.4.0)
  • our clickhouse-backup-agent (ch-backup-agent)
  • ftpd_server (stilliard/pure-ftpd)
• ./clickhouse/clickhouse-backup-config.yml - clickhouse-backup config file
• ./agent/Dockerfile - backup agent's docker image
• ./agent/ch-backup-logrotate.conf - logrotate config file
• ./agent/clickhouse-backup.sh - script to define backup and upload steps
• ./agent/cronfile - cron job backup and logrotate tasks
• ./github/workflows/docker-image.yml - simple GitHub action to build agent's docker image on every Dockerfile change

• as a resource for learning docker, docker compose, bash, cron and logrotate
• as a source of the script, cron job task or docker files. just grab them and you're set
• as a sample of pairing clickhouse-backup and clickhouse server

• check out logrotate and cron settings in the agent folder
• verify the Dockerfile in the agent folder (if docker is being used)
• adjust clickhouse backup settings if necessary (./clickhouse/clickhouse-backup-config.yml) Change credentials, clickhouse host and remote storage at least
• clickhouse-backup API container or standalone service shoud have access to /var/clickhouse/ folders to create backup successfully. In case of a container, see docker-compose.yml. If your clickhouse-backup API is a Linux service, run the service on the first replica for each shard, and then update cronfile accordingly
• copy cron and script files to a remote host, and then make a test run
• in the case of using Docker, please check the 'docker-compose.yml' file and remove any unnecessary services (such as clickhouse and ftp). Afterward, run docker-compose up -d --build to get containers started
• use docker logs or docker compose to check service logs Log files are also located under the /var/log/clickhouse-backup/ folder

More info and tricks at the blog post

Output with error, warning and info messages:

Log file:

Diagram as code: