5

We recently started using Yandex ClickHouse for our data backend, and I'm working on figuring out how best to backup our data. There seem to be two ways of doing this.

ALTER TABLE ... FREEZE PARTITION

The ALTER TABLE ... FREEZE PARTITION command appears to simply create a local snapshot of a partition. I'd have to write a script which discovers all of the partitions in each table, and then issue the appropriate command.

In order to get the backups off the system, I reckon I'd have to create a backup of the shadow directory on each server, and store that backup in another location (like S3 or something).

How would I keep the shadow directory clean? Can the freezes be deleted?

Data Dump

The other way I've seen to backup data is to simply dump it to files, as this page suggests.

https://github.com/resure/scpnet/wiki/ClickHouse-backup

I'd have to write a script which discovers all of the tables in each database, and them dump all of the data into a file. This reminds me a lot of using mysqldump to backup databases in MySQL, but without the ability to just dump everything in one command.

Personally, I'm leaning towards this solution, as I it seems to be much easier to script and maintain, but I'm really curious to know what others are doing.

Matt
  • 51
  • 1
  • 2
  • I tried to develop a mysqldump-like script to create a single SQL dump, but I have discovered that it's not feasible, because 1. `select ... format Values` doesn't always produce valid SQL input when the data contains single quotes and backslashes, and 2. `clickhouse-client -n` cannot execute unlimited size scripts, even when each query is limited in size. So we need to either create a bunch of SQL and CSV files and a script to load them, or proceed with the freeze hypothesis. – Tobia Feb 05 '18 at 18:03
  • Another problem is views, which need to be loaded in topological order. (You cannot create a view which depends on a view that is yet to be restored.) – Tobia Feb 05 '18 at 18:04
  • See [official documentation](https://clickhouse.yandex/docs/en/operations/backup/) for possible ClickHouse backup options. – Ivan Blinkov Dec 11 '18 at 16:44

0 Answers0