Last updated:

A backup is worthless if the restoration process hasn’t been tested. If you backed something up and never tried to restore it, chances are that restore will not work properly when you actually need it or it will take longer than business can tolerate. Perform regular test restores to ensure your data will be there when you need it!

Automated using clickhouse-backup

The tool provides an automatic restore operation that you can invoke by running: clickhouse-backup restore <BACKUP NAME>.

For more information please look at the official documentation.


In this specific example, we will restore the table events.

  1. Optional: create the table you would like to restore (if it doesn’t exist yet) from its backed up metadata file

    cat events.sql | clickhouse-client --database posthog
  2. Copy the backup data from the data/posthog/events/ directory inside the backup you want to restore to the /var/lib/clickhouse/data/posthog/events/detached/ directory

    cp -r $SOURCE_BACKUP_LOCATION/data/posthog/events/* /var/lib/clickhouse/data/posthog/events/detached/
  3. Add the data to the table from the detached directory. It is possible to add data for an entire partition or for a separate part (take a look here for more info)

    clickhouse-client --database posthog --query "ALTER TABLE events ATTACH PARTITION 202111"
  4. Your data should be now available

    clickhouse-client --database posthog --query "SELECT COUNT(1) from events"