# ClickHouse Backup Guide

This guide covers the two backup options available for ClickHouse in Opik's Kubernetes deployment:

1. **SQL-based Backup** - Uses ClickHouse's native `BACKUP` command with S3
2. **ClickHouse Backup Tool** - Uses the dedicated `clickhouse-backup` tool

## Overview

ClickHouse backup is essential for data protection and disaster recovery. Opik provides two different approaches to handle backups, each with its own advantages:

* **SQL-based Backup**: Simple, uses ClickHouse's built-in backup functionality
* **ClickHouse Backup Tool**: More advanced, provides additional features like compression and incremental backups

## Option 1: SQL-based Backup (Default)

This is the default backup method that uses ClickHouse's native `BACKUP` command to create backups directly to S3-compatible storage.

### Features

* Uses ClickHouse's built-in `BACKUP ALL EXCEPT DATABASE system` command
* Direct S3 upload with timestamped backup names
* Configurable schedule via CronJob
* Supports both AWS S3 and S3-compatible storage (like MinIO)

### Configuration

#### Basic Setup

#### With AWS S3 Credentials

Create a Kubernetes secret with your S3 credentials:

```bash
kubectl create secret generic clickhouse-backup-secret \
  --from-literal=access_key_id=YOUR_ACCESS_KEY \
  --from-literal=access_key_secret=YOUR_SECRET_KEY
```

Then configure the backup:

```yaml
clickhouse:
  backup:
    enabled: true
    bucketURL: "https://your-bucket.s3.region.amazonaws.com"
    secretName: "clickhouse-backup-secret"
    schedule: "0 0 * * *"
```

#### With IAM Role (AWS EKS)

For AWS EKS clusters, you can use IAM roles instead of access keys:

```yaml
clickhouse:
  serviceAccount:
    create: true
    name: "opik-clickhouse"
    annotations:
      eks.amazonaws.com/role-arn: "arn:aws:iam::ACCOUNT:role/clickhouse-backup-role"
  backup:
    enabled: true
    bucketURL: "https://your-bucket.s3.region.amazonaws.com"
    schedule: "0 0 * * *"
```

**Required IAM Policy:**

```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:*",
      "Resource": ["arn:aws:s3:::your-bucket", "arn:aws:s3:::your-bucket/*"]
    }
  ]
}
```

**Trust Relationship Policy:**

```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Federated": "arn:aws:iam::ACCOUNT:oidc-provider/oidc.eks.REGION.amazonaws.com/id/OIDCPROVIDERID"
      },
      "Action": "sts:AssumeRoleWithWebIdentity",
      "Condition": {
        "StringEquals": {
          "oidc.eks.REGION.amazonaws.com/id/OIDCPROVIDERID:sub": "system:serviceaccount:YOUR_NAMESPACE:opik-clickhouse",
          "oidc.eks.REGION.amazonaws.com/id/OIDCPROVIDERID:aud": "sts.amazonaws.com"
        }
      }
    }
  ]
}
```

#### Custom Backup Command

You can customize the backup command if needed:

```yaml
clickhouse:
  backup:
    enabled: true
    bucketURL: "https://your-bucket.s3.region.amazonaws.com"
    command:
      - /bin/bash
      - "-cx"
      - |-
        export backupname=backup$(date +'%Y%m%d%H%M')
        echo "BACKUP ALL EXCEPT DATABASE system TO S3('${CLICKHOUSE_BACKUP_BUCKET}/${backupname}/', '$ACCESS_KEY', '$SECRET_KEY');" > /tmp/backQuery.sql
        clickhouse-client -h clickhouse-opik-clickhouse --send_timeout 600000 --receive_timeout 600000 --port 9000 --queries-file=/tmp/backQuery.sql
```

### Backup Process

The SQL-based backup:

1. Creates a timestamped backup name (format: `backupYYYYMMDDHHMM`)
2. Executes `BACKUP ALL EXCEPT DATABASE system TO S3(...)` command
3. Uploads all databases except the `system` database to S3
4. Uses ClickHouse's native backup format

### Restore Process

To restore from a SQL-based backup:

```bash