Quick and dirty Clickhouse database daily backup script

clickhouse-backup

#!/bin/sh -e

T="$(date +%Y-%m-%d-%H%M)"

BASEDIR=/opt/backups/clickhouse
DESTDIR="$BASEDIR/$T"

. ~/.env.clickhouse  # ..CLICKHOUSE_HOST,USERNAME,PASSWORD,DATABASE

Query() {
    clickhouse-client \
    --host     "$CLICKHOUSE_HOST"     \
    --user     "$CLICKHOUSE_USERNAME" \
    --password "$CLICKHOUSE_PASSWORD" \
    --database "$CLICKHOUSE_DATABASE" \
    --query    "$@"
}

QueryRaw() { Query "$@" --output-format TSVRaw; }

mkdir -pm700 "$DESTDIR"
cd           "$DESTDIR"

QueryRaw "SHOW DATABASE $CLICKHOUSE_DATABASE" > DB.INFO
Query    "SHOW TABLES"                    > TABLES.LIST

while read TableName; do
    QueryRaw "SHOW TABLE $TableName" > "$TableName.schema"
    Query "SELECT * FROM $TableName" > "$TableName.data"
done < TABLES.LIST

nice ionice -c3 env XZ_OPT=-T0 tar cJf "$BASEDIR/$T.txz" -C "$DESTDIR" .

rm -rf "$DESTDIR"

find "$BASEDIR/" -maxdepth 1 -type f -name '*z' -mtime +1 -delete

## END ##