ClickHouse Backup and Restore

By default ClickHouse disables the backup/restore config. The following config and commands are used to natively backup and restore CH without any 3rd party tools.

Setup

Filename backup_disk.xml

Save this file in /etc/clickhouse-server/config.d/backup_disk.xml

<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/backups/</allowed_path>
    </backups>
</clickhouse>

Filename docker-compose.xml

# docker-compose fragment for creating CH backup config and backup path location
    volumes:
      - ./conf/backup_disk.xml:/etc/clickhouse-server/config.d/backup_disk.xml:ro
      - ./backups:/backups

SQL Commands to Backup/Restore

Use the clickhouse-client to run the backup commands on the server. Large backups will need the ASYNC so the client connection does not timeout.

-- Backup and restore a small DB to the "/backups" folder on the server side
BACKUP  DATABASE hcris TO   Disk('backups', 'hcris.zip')
RESTORE DATABASE hcris FROM Disk('backups', 'hcris.zip')

-- Backup and restore a large DB async
BACKUP  DATABASE insurance TO   Disk('backups', 'insurance.zip') ASYNC
RESTORE DATABASE insurance FROM Disk('backups', 'insurance.zip') ASYNC

-- Check status of async backup jobs
SELECT * FROM system.backups FORMAT Vertical

References

Official CH Backup and Restore docs.

#TODO backing CH up to S3. #TODO incremental backup/restores.