Take a backup or restore a backup from the UI

Page describing how to take a backup or restore a backup from the UI with your own bucket

Backup / restore via user-interface

Automated backups to your external bucket are configured to run as “full” backups every 24 hours, and the frequency is not configurable.

AWS

Taking backups to AWS

1. Steps to follow in AWS

These steps are similar to the secure s3 setup as described in “Accessing S3 data securely”, however, there are additional actions required in the role permissions

Follow the steps below on your AWS account:

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

2. 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"
       }
     ]
   }
   
3. 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"
       }
     ]
   }
   

2. Steps to follow in ClickHouse Cloud

Follow the steps below in the ClickHouse Cloud console to configure the external bucket:

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

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

3. Click on “Save External Bucket” to save the settings

4. 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.

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

Restoring backups from AWS

Follow the steps below to restore backups from AWS:

1. Create a new service to restore the backup to.

2. 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.

3. 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 doesn’t 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.

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

GCP

Taking backups to GCP

Follow the steps below to take backups to GCP:

Steps to follow in GCP

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

2. 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 won’t be displayed again after you close this window.

Steps to follow in ClickHouse Cloud

Follow the steps below in the ClickHouse Cloud console to configure the external bucket:

1. On the Settings page, click on Change external backup

2. In the popup dialogue, provide the GCP bucket path, HMAC key and Secret created in the previous section.

3. Click on Save External Bucket to save the settings.

4. 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 ClickHouse cloud owned bucket.

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

Restoring backups from GCP

Follow the steps below to restore backups from GCP:

1. Create a new service to restore the backup to.

2. 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 should look like this, and you can pick the appropriate backup from the dropdown to get the restore command for that specific backup. You will need to add your secret access key to the command:

   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 doesn’t 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.

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

Azure

Taking backups to Azure

Follow the steps below to take backups to Azure:

Steps to follow in Azure

1. Create a storage account or select an existing storage account in the Azure portal where you want to store your backups.

2. • a. In your storage account overview, look for the section called Security + networking and click on Access keys.
   • b. Here, you will see key1 and key2. Under each key, you’ll find a Connection string field.
   • c. Click Show to reveal the connection string. Copy the connection string which you will use to for set-up on ClickHouse Cloud.

Steps to follow in ClickHouse Cloud

Follow the steps below in the ClickHouse Cloud console to configure the external bucket:

1. On the Settings page, click on Change external backup

2. On the next screen provide the Connection String and Container Name for your Azure storage account created in the previous section:

3. Click on Save External Bucket to save the settings

4. 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 ClickHouse cloud owned bucket.

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

Restoring backups from Azure

To restore backups from Azure, follow the steps below:

1. Create a new service to restore the backup to. Currently, we only support restoring a backup into a new service.

2. Click on the access or restore a backup link above the list of backups in the UI to obtain the SQL command to restore the backup. The command should look like this, and you can pick the appropriate backup from the dropdown to get the restore command for that specific backup. You will need to add your Azure storage account connection string to the command.

   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 doesn’t 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.

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