ClickHouse Backup Made Easy

Backing Up ClickHouse: Step-by-Step

Backup Steps

1.create /etc/clickhouse-server/config.d/backup_disk.xml

Copy<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/var/lib/clickhouse/data/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/var/lib/clickhouse/data/clickhouse/backups/</allowed_path>
    </backups>
</clickhouse>

2.Change the owner to clickhouse:clickhouse

3.Restart the ClickHouse server

4.Log in to ClickHouse

Copyclickhouse-client --secure --user default --ask-password

5.Backup a table or database using the following command

CopyBACKUP DATABASE databae-name TO Disk('backups', '1.zip')
BACKUP TABLE test.table TO Disk('backups', '1.zip')

6.Restore a table or database using the following command

CopyRESTORE DATABASE databae-name AS databae-name FROM Disk('backups', '1.zip')
RESTORE TABLE test.table AS test.table2 FROM Disk('backups', '1.zip')

Reference : https://clickhouse.com/docs/en/operations/backup

To increase the data retention time using the following command

CopySHOW CREATE TABLE table_name;
ALTER TABLE table_name MODIFY TTL toDateTime(timestamp_ns / 1000000000) + toIntervalDay(180);
SHOW CREATE TABLE table_name;