Take a backup or restore a backup from the UI

Create an AWS S3 bucket

Create an AWS S3 bucket in your account where you want to export backups.

Create an IAM role

AWS uses role based authentication, so create an IAM role that the ClickHouse Cloud service will be able to assume into, to write to this bucket.

a. Obtain the ARN from the ClickHouse Cloud service settings page, under Network security information, which looks similar to this:

b. For this role create the trust policy as follows:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "backup service",
      "Effect": "Allow",
      "Principal": {
        "AWS":  "arn:aws:iam::463754717262:role/CH-S3-bordeaux-ar-90-ue2-29-Role"
      },
      "Action": "sts:AssumeRole"
    },
  ]
}

Update permissions for role

You will also need to set the permissions for this role so this ClickHouse Cloud service can write to the S3 bucket. This is done by creating a permissions policy for the role with a JSON similar to this one, where you substitute in your bucket ARN for the resource in both places.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": [
        "s3:GetBucketLocation",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::byob-ui"
      ],
      "Effect": "Allow"
    },
    {
      "Action": [
        "s3:Get*",
        "s3:List*",
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::byob-ui/*"
      ],
      "Effect": "Allow"
    },
    {
      "Action": [
        "s3:DeleteObject"
      ],
      "Resource": [
        "arn:aws:s3:::byob-ui/*/.lock"
      ],
      "Effect": "Allow"
    }
  ]
}

Change external backup

On the Settings page, click on Set up external backup:

Configure AWS IAM Role ARN and S3 bucket details

On the next screen provide the AWS IAM Role ARN you just created and the S3 bucket URL in the following format:

Save changes

Click on “Save External Bucket” to save the settings

Changing the backup schedule from the default schedule

External Backups will now happen in your bucket on the default schedule. Alternatively, you can configure the backup schedule from the “Settings” page. If configured differently, the custom schedule is used to write backups to your bucket and the default schedule (backups every 24 hours) is used for backups in the ClickHouse cloud owned bucket.

View backups stored in your bucket

The Backups page will display these backups in your bucket in a separate table as shown below:

Create a new service to restore to

Create a new service to restore the backup to.

Add service ARN

Add the newly created service’s ARN (from the service settings page in Clickhouse Cloud console) to the trust policy for the IAM role. This is the same as the second step in the AWS Steps section above. This is required so the new service can access the S3 bucket.

Get SQL command used to restore backup

Click on the “access or restore a backup” link above the list of backups in the UI to get the SQL command to restore the backup. The command will look like this:

Moving backups to another location

If you move the backups to another location, you will need to customize the restore command to reference the new location.

ASYNC command

For the Restore command you can also optionally add an ASYNC command at the end for large restores. This allows the restores to happen asynchronously, so that if connection is lost, the restore keeps running. It is important to note that the ASYNC command immediately returns a status of success. This does not mean the restore was successful. You will need to monitor the system.backups table to see if the restore has finished and if it succeeded or failed.

Run the restore command

Run the restore command from the SQL console in the newly created service to restore the backup.

Create a GCP storage bucket

Create a storage bucket in your GCP account to export backups to.

Generate an HMAC Key and Secret

Generate an HMAC Key and Secret, which is required for password-based authentication. Follow the steps below to generate the keys:

a. Create a service account
- I. Navigate to the IAM & Admin section in the Google Cloud Console and select Service Accounts.
- II. Click Create Service Account and provide a name and ID. Click Create and Continue.
- III. Grant the Storage Object User role to this service account.
- IV. Click Done to finalize the service account creation.
b. Generate the HMAC key
- I. Go to Cloud Storage in the Google Cloud Console, and select Settings
- II Go to the Interoperability tab.
- III. In the Service account HMAC section, click Create a key for a service account.
- IV. Choose the service account you created in the previous step from the dropdown menu.
- V. Click Create key.
c. Securely store the credentials:
- I. The system will display the Access ID (your HMAC key) and the Secret (your HMAC secret). Save these values, as the secret will not be displayed again after you close this window.