Backup and restore with clickhouse-backup

ClickHouse includes native support for instantaneous point-in-time backups, through its ALTER TABLE ... FREEZE feature. ClickHouse creates hard links in the shadow directory to all the partitions. This way, each backup is effectively a full backup, and duplicate use of disk space is avoided.

After performing a manual backup we should move it to another location. To restore it, we should:

• recover the backup from the other location
• copy the partitions to the detached directory for each of the tables
• attach each partition with an ALTER TABLE ... ATTACH PARTITION

We can automate this process thanks to clickhouse-backup.

Let's assume we already have clickhouse-backup installed and we have configured a remote storage.

Let's create a database backup, a table test and a user backup than can access this database:

:) CREATE DATABASE IF NOT EXISTS backup

:) CREATE TABLE IF NOT EXISTS backup.test
(
  `date` Datetime,
  `user_id` String,
  `pageviews` Int32
)
ENGINE = MergeTree()
PARTITION BY toStartOfHour(date)
ORDER BY (date)

:) CREATE USER backup IDENTIFIED WITH plaintext_password BY 'qwerty'

:) GRANT ALL ON backup.* TO backup

We can edit /etc/clickhouse-backup/config.yml to configure this user as the clickhouse-backup user.

Let's insert some data in the table:

INSERT INTO backup.test VALUES
('2021-01-13 07:00:00', 'user 1', 7),
('2021-01-13 08:00:00', 'user 2', 3),
('2021-01-13 09:00:00', 'user 3', 1),
('2021-01-13 10:00:00', 'user 4', 12)

We can check all the backups we have performed until now:

# clickhouse-backup list
Local backups:
no backups found
Remote backups:
no backups found

If can check the list of tables that clickhouse-backup would backup:

# clickhouse-backup tables
backup.test

Let's create a local backup:

# clickhouse-backup create
2021/01/13 13:14:43 Create backup '2021-01-13T12-14-43'
2021/01/13 13:14:43 Freeze 'backup.test'
2021/01/13 13:14:43 Copy part hashes
2021/01/13 13:14:43 Writing part hashes
2021/01/13 13:14:43 Copy metadata
2021/01/13 13:14:43   Done.
2021/01/13 13:14:43 Move shadow
2021/01/13 13:14:43   Done.
# clickhouse-backup list
Local backups:
- '2021-01-13T12-14-43' (created at 13-01-2021 13:14:43)
Remote backups:
no backups found

We can upload it to the remote storage and delete the local backup:

# clickhouse-backup upload '2021-01-13T12-14-43'
2021/01/13 13:15:39 Upload backup '2021-01-13T12-14-43'
 4.36 KiB / 4.36 KiB [======================================================] 100.00% 0s
2021/01/13 13:15:39   Done.
# clickhouse-backup delete local '2021-01-13T12-14-43'
# clickhouse-backup list
Local backups:
no backups found
Remote backups:
- '2021-01-13T12-14-43.tar.gz'  3.55 KiB    (created at 13-01-2021 12:15:39)

Disasters can happen at any time. Let's drop the database:

DROP DATABASE backup

😱

Don't worry, we can download the remote backupa and restore:

# clickhouse-backup download '2021-01-13T12-14-43'
 3.55 KiB / 3.55 KiB [======================================================] 100.00% 0s
2021/01/13 13:34:22   Done.
# clickhouse-backup restore '2021-01-13T12-14-43'
2021/01/13 13:35:45 Create table 'backup.test'
2021/01/13 13:35:45 Prepare data for restoring 'backup.test'
2021/01/13 13:35:45 ALTER TABLE `backup`.`test` ATTACH PART '1610517600_1_1_0'
2021/01/13 13:35:45 ALTER TABLE `backup`.`test` ATTACH PART '1610521200_2_2_0'
2021/01/13 13:35:45 ALTER TABLE `backup`.`test` ATTACH PART '1610524800_3_3_0'
2021/01

💃

:) select count() from backup.test

SELECT count()
FROM backup.test

┌─count()─┐
│       4 │
└─────────┘