Restore partitions overwrite others partitions

[bmailhe]

Hello,
I'm trying to backup (and restore) big tables (>4TiB) by partitions. Partitions are by year and month (like 202310 PARTITION BY toYYYYMM(day))

I make one backup per complete year,
example for 2022:

clickhouse-backup create --tables=mydb.test--partitions=202201,202202,202203,202204,202205,202206,202207,202208,202209,202210,202211,202212 LOCAL-YEAR-2022-mydb-test-231004130327

So I have one backup per year.

Now if I truncate my table to simulate an incident, and try to restore by year, one restore overwrite the previous one:

• truncate table cluster :) TRUNCATE table mydb.test
• restore year 2023 clickhouse-backup restore LOCAL-YEAR-2023-mydb-test-231004130353
• check ✅ :

cluster :) select count(*),_partition_id from mydb.test where _partition_id like '20%' group by _partition_id order by _partition_id
┌─count()─┬─_partition_id─┐
│  741122 │ 202301        │
│  721170 │ 202302        │
│  832656 │ 202303        │
│  713965 │ 202304        │
│  688150 │ 202305        │
│  705121 │ 202306        │
│  787518 │ 202307        │
│  785592 │ 202308        │
│  777598 │ 202309        │
│  104160 │ 202310        │
└─────────┴───────────────┘

• restore year 2022 : clickhouse-backup restore LOCAL-YEAR-2022-mydb-test-231004130327
• check ❌ : year 2023 replaced by year 2022

cluster :) select count(*),_partition_id from   mydb.test where _partition_id like '20%'  group by _partition_id order by _partition_id
┌─count()─┬─_partition_id─┐
│  640718 │ 202201        │
│  586906 │ 202202        │
│  673502 │ 202203        │
│  659712 │ 202204        │
│  741308 │ 202205        │
│  679515 │ 202206        │
│  697404 │ 202207        │
│  730178 │ 202208        │
│  685027 │ 202209        │
│  682812 │ 202210        │
│  652550 │ 202211        │
│  723319 │ 202212        │
└─────────┴───────────────┘

I've try to restore using --data and/or --partitions= argument but nothing happens;
Have I made a mistake in using or understanding how backup restoration per partition works?
Thanks

[Slach]

Have I made a mistake in using or understanding how backup restoration per partition works?

Yes, a little, when you try to restore data then exists table will drop and be created from scratch because we can't guaranteed we have the same table structure in backup with exists table

maybe we should convert discussion issue

as a workardoung i could suggest use

clickhouse-backup restore --restore-database-mapping=mydb:mydb2022 LOCAL-YEAR-2022-mydb-test-231004130327

and after it manually attach partitions with following SQL statements

ALTER TABLE mydb.test ATTACH PARTITION '202201' FROM mydb2022.test;
...
ALTER TABLE mydb.test ATTACH PARTITION '202212' FROM mydb2022.test;
DROP DATABASE mydb2022 SYNC;

[bmailhe]

Indeed the workaround works. Thanks!
Could you clarify the goal of backuping partitions if it is not possible to restore them ?
It would be nice to be able to restore them without overwriting the others, may be with an additional option (like --data-only)

[Slach]

Could you clarify the goal of backuping partitions if it is not possible to restore them ?

You can restore
But it will overwrite the whole exists table
I think we could implement ALTER TABLE ..DROP PARTITION instead of DROP TABLE in restore command when --partitions argument passes, but if exists table has a different structure, ATTACH PART could failed

If you want to make incremental backup
use --diff-from-remote for create_remote or upload command
this is a different approach, which has trade-offs
it works good, if you don't use many mutations (ALTER TABLE ... UPDATE / DELETE)

[bmailhe]

Yes the ALTER TABLE ..DROP PARTITION instead of DROP TABLE looks good !
We already use the incremental backup, it works very well for tables with a low size, but those tables > 4Tib, the backups are stored into a S3 bucket and the restore takes too long (up to 36hours for a 15TiB table downloading at 1Gbps) to download from the bucket. This is why we want to divide backups by partitions (per year) in order to quickly restore the most recent data first, and restore the rest later.

[Slach]

Looks weird, in AWS S3 we have 1Tb per hour download speed minimum even for m5.* instances

did you try settings tuning for example:

general:
  download_concurrency: 10
s3:
  concurrency: 10  
  allow_multipart_download: true

?

[bmailhe]

Yes we already use this tuning (see #571)
We use Backblaze storage which is less performant than AWS S3