clickhouse-backup备份还原表需要注意的问题

 

clickhouse-backup备份还原表需要注意的问题

 

通过clickhouse-bakcup备份而后还原一些对象，比如表。

还原的时候会直接将表的数据状态重置为备份时候的样子，就是备份是什么样子，还原之后就是什么样子。

（Oracle的数据泵用久了就觉得其他数据库的类型工具功能太简单了，有点不习惯）

 

备份一张表。

CK01 :) select count(*) from zkm.zkm;

SELECT count(*)
FROM zkm.zkm

Query id: 23cd0253-2d96-4522-bfd9-68fd860f7f46

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

1 rows in set. Elapsed: 0.003 sec. 


[root@dev-app76 zkm]# clickhouse-backup create -t zkm.zkm
[root@dev-app76 zkm]# clickhouse-backup list
2023/03/27 11:17:48.609140  info clickhouse connection prepared: tcp://localhost:9000 run ping logger=clickhouse
2023/03/27 11:17:48.612478  info clickhouse connection open: tcp://localhost:9000 logger=clickhouse
2023/03/27 11:17:48.612580  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER' logger=clickhouse
2023/03/27 11:17:48.619137  info SELECT * FROM system.disks; logger=clickhouse
2023-03-27T03-17-21   4.42KiB   27/03/2023 03:17:21   local      
2023/03/27 11:17:48.627518  info clickhouse connection closed logger=clickhouse

 

 模拟业务对表数据做变更。

CK01 :) insert into zkm.zkm select * from zkm.zkm;

INSERT INTO zkm.zkm SELECT *
FROM zkm.zkm

Query id: 25e8bad9-9f09-44e9-a3fc-bb1db39b1cca

Ok.

0 rows in set. Elapsed: 0.014 sec. 

CK01 :) select count(*) from zkm.zkm;

SELECT count(*)
FROM zkm.zkm

Query id: ecd76c71-b978-4bcb-bd8e-70b336730e81

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

1 rows in set. Elapsed: 0.003 sec. 

CK01 :) optimize table zkm.zkm final;

OPTIMIZE TABLE zkm.zkm FINAL

Query id: 52d0047f-4042-4396-a8ca-d2fd1912a6a8

Ok.

0 rows in set. Elapsed: 0.006 sec. 

 

表在磁盘生分区目录情况。

[root@dev-app76 zkm]# pwd
/clickhouse/data/zkm/zkm
[root@dev-app76 zkm]# ll
total 20
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:15 all_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:18 all_1_2_1
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:18 all_2_2_0
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:15 detached
-rw-r----- 1 clickhouse clickhouse    1 Mar 27 11:15 format_version.txt
...隔一段时间...
[root@dev-app76 zkm]# ll
total 12
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:18 all_1_2_1
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:15 detached
-rw-r----- 1 clickhouse clickhouse    1 Mar 27 11:15 format_version.txt

PS：中间分区目录合并后，目录“all_1_1_0”里边的文件硬链接数原本是2，合并完成后目录消失。

而目录“/clickhouse/backup/2023-03-27T03-17-21/shadow/zkm/zkm/default/all_1_1_0”的硬链接数从2变为1。

 

备份文件的对应表的分区目录情况。

[root@dev-app76 default]# pwd
/clickhouse/backup/2023-03-27T03-17-21/shadow/zkm/zkm/default
[root@dev-app76 default]# ll
total 4
drwxr-x--- 2 root root 4096 Mar 27 11:17 all_1_1_0

 

还原表。

[root@dev-app76 zkm]# clickhouse-backup restore 2023-03-27T03-17-21 -t zkm.zkm
2023/03/27 11:30:41.426243  info clickhouse connection prepared: tcp://localhost:9000 run ping logger=clickhouse
2023/03/27 11:30:41.430581  info clickhouse connection open: tcp://localhost:9000 logger=clickhouse
2023/03/27 11:30:41.430648  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER' logger=clickhouse
2023/03/27 11:30:41.437394  info SELECT * FROM system.disks; logger=clickhouse
2023/03/27 11:30:41.446092  info SELECT engine FROM system.databases WHERE name = 'zkm' logger=clickhouse
2023/03/27 11:30:41.453617  info DROP TABLE IF EXISTS `zkm`.`zkm` NO DELAY logger=clickhouse
2023/03/27 11:30:41.459703  info CREATE DATABASE IF NOT EXISTS `zkm` logger=clickhouse
2023/03/27 11:30:41.463289  info CREATE TABLE zkm.zkm UUID '70b9d6ee-793b-48d9-992a-53c247e95885' (`SEQ_NO` String, `CODE_SCC` String, `TRADE_CODE` String, `IE_FLAG` String, `CODE_TS` String, `TRADE_MARKET` String, `TRADE_MODE` String, `AMOUNT_RMB` Float32, `AMOUNT_USD` Float32, `STAT_DATE` Date, `CREATE_TIME` DateTime, `CRE_DATE` Date) ENGINE = MergeTree ORDER BY CREATE_TIME SETTINGS index_granularity = 8192 logger=clickhouse
2023/03/27 11:30:41.473515  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil' logger=clickhouse
2023/03/27 11:30:41.480224  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL','MaterializedPostgreSQL') logger=clickhouse
2023/03/27 11:30:41.485506  info    SELECT     countIf(name='data_path') is_data_path_present,     countIf(name='data_paths') is_data_paths_present,     countIf(name='uuid') is_uuid_present,     countIf(name='create_table_query') is_create_table_query_present,     countIf(name='total_bytes') is_total_bytes_present    FROM system.columns WHERE database='system' AND table='tables'   logger=clickhouse
2023/03/27 11:30:41.493416  info SELECT database, name, engine , data_paths , uuid , create_table_query , coalesce(total_bytes, 0) AS total_bytes   FROM system.tables WHERE is_temporary = 0 AND match(concat(database,'.',name),'zkm\.zkm')  ORDER BY total_bytes DESC SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1 logger=clickhouse
2023/03/27 11:30:41.594886  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='zkm' AND table='zkm' GROUP BY database, table logger=clickhouse
2023/03/27 11:30:41.603278  info ALTER TABLE `zkm`.`zkm` ATTACH PART 'all_1_1_0' logger=clickhouse
2023/03/27 11:30:41.608316  info done                      backup=2023-03-27T03-17-21 operation=restore table=zkm.zkm
2023/03/27 11:30:41.608378  info done                      backup=2023-03-27T03-17-21 duration=135ms operation=restore
2023/03/27 11:30:41.608416  info done                      backup=2023-03-27T03-17-21 operation=restore
2023/03/27 11:30:41.608478  info clickhouse connection closed logger=clickhouse

 

表在磁盘生分区目录情况。

[root@dev-app76 zkm]# pwd
/clickhouse/data/zkm/zkm
[root@dev-app76 zkm]# ll
total 12
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:30 all_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:30 detached
-rw-r----- 1 clickhouse clickhouse    1 Mar 27 11:30 format_version.txt

 

数据条数变为了100条。

CK01 :) select count(*) from zkm;

SELECT count(*)
FROM zkm

Query id: d3563bee-fe41-476b-9d96-362056e7ef10

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

1 rows in set. Elapsed: 0.003 sec.