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
detacheddirectory 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 │
└─────────┘
Top comments (0)