Backup / restore via user-interface

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

Automated backups to your external bucket are full, independent backups. They follow the backup frequency and start time configured for the service (every 24 hours by default). ClickHouse also creates a separate backup in a ClickHouse-managed bucket every 24 hours; that cadence is fixed.

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

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

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

2. Steps to follow in ClickHouse Cloud

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

1. Change external backup

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

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

3. Save changes

   Click on “Save External Bucket” to save the settings

4. 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 and retention setting apply to backups written to your bucket. Backups in the ClickHouse Cloud-managed bucket use the default schedule (backups every 24 hours) and default retention policy.

5. View backups stored in your bucket

   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 to

   Create a new service to restore the backup to.

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

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

   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 GCP storage bucket

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

2. 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 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. Change external backup

   On the Settings page, click on Change external backup

2. Configure GCP HMAC Key and Secret

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

3. Save external bucket

   Click on Save External Bucket to save the settings.

4. 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 and retention setting apply to backups written to your bucket. Backups in the ClickHouse Cloud-managed bucket use the default schedule (backups every 24 hours) and default retention policy.

5. View backups stored in your bucket

   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 to

   Create a new service to restore the backup to.

2. 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 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 SQL command to restore backup

   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

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

2. Get connection string

   • 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. Change external backup

   On the Settings page, click on Change external backup

2. Provide connection string and container name for your Azure storage account

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

3. Save external bucket

   Click on Save External Bucket to save the settings

4. 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 and retention setting apply to backups written to your bucket. Backups in the ClickHouse Cloud-managed bucket use the default schedule (backups every 24 hours) and default retention policy.

5. View backups stored in your bucket

   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 to

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

2. 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 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 SQL command to restore backup

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