Backing up ClickHouse

Step 1: Install clickhouse-backup

Follow the instructions at https://github.com/AlexAkulov/clickhouse-backup to build from source. This is done on the ClickHouse server we are trying to backup.

sudo apt install golang-go
GO111MODULE=on go get github.com/AlexAkulov/clickhouse-backup/cmd/clickhouse-backup

The binary will be at ~/go/bin/clickhouse-backup. Run the following command to verify it is working:

clickhouse-backup tables

Step 2: Create a backup

To backup all tables, run

clickhouse-backup create 20210901-backup

If no name was provided after create, the default name is a long timestamp. The above command runs in a matter of seconds because all it does is create a set of hardlinks in /var/lib/clickhouse/backup. No actual copying is done so no extra space is used.

Note: Because these are hardlinks, do not ever attempt to change the file permissions or rename files in /var/lib/clickhouse/backup/. It might corrupt the database!

We can verify the backup directory’s size using du.

cd /var/lib/clickhouse/backup
du -sh *
... 7.9G    20210901-backup
tar cvf 20210901-backup.tar 20210901-backup 

Do tar cvf 20210901-backup.tar 20210901-backup so that it can be copied easily to another server. Note that we only use cvf and not zcvf for speed since the files are already compressed.

Step 3: Install ClickHouse on backup server

Create a docker-compose.yml file on backup server. Here is a barebone example:

version: '3'

services:
  clickhouse_backup:
    image: yandex/clickhouse-server
    ports:
      - "0.0.0.0:8123:8123"
      - "0.0.0.0:9000:9000"
    volumes:
      - ./db:/var/lib/clickhouse
    networks:
      - clickhouse_network
    environment:
      - TZ:"UTC"

networks:
  clickhouse_network:
    driver: bridge
    ipam:
      config:
        - subnet: 10.9.8.0/24

Save the above into an empty directory, say ~/chsB. Go into it and start ClickHouse with:

docker-compose up -d

We can then access the running Docker instance as root using:

docker-compose exec clickhouse_backup /bin/bash

Install clickhouse-backup in this Docker instance as described in Step 1.

Step 4: Restore ClickHouse on backup server

Rsync the 20210901-backup.tar to the backup server. Since the docker configuration saves the database at ~chsB/db, we can move the file into it so that it is easily accessed from within the Docker instance.

mkdir backup
chown clickhouse.clickhouse backup
cd backup
tar xvf ../20210901-backup.tar

Once the /var/lib/clickhouse/backup/20210901-backup is created, we can restore the database with:

clickhouse-backup restore 20210901-backup

Read https://altinity.com/blog/introduction-to-clickhouse-backups-and-clickhouse-backup for more details.