Backup and restore with clickhouse-backup
Tags: clickhouse
Published: 2021-01-13 13:45:40

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](https://github.com/AlexAkulov/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:

```sql
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:

```sql
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 │
└─────────┘
```