Skip to main content
Skip to main content
Edit this page

Take a backup or restore a backup using commands

Users can utilize BACKUP and RESTORE commands to export backups to their storage buckets, in addition to backing up or restoring via user interface. Commands for all three CSPs are given in this guide.

Requirements​

You will need the following details to export/restore backups to your own CSP storage bucket:

  1. AWS S3 endpoint, in the format: s3://<bucket_name>.s3.amazonaws.com/<optional_directory> For example: s3://testchbackups.s3.amazonaws.com/ Where:
    • testchbackups is the name of the S3 bucket to export backups to.
    • backups is an optional subdirectory.
  2. AWS access key and secret. AWS role based authentication is also supported and can be used in place of AWS access key and secret as described in the section above.

Backup / Restore specific DB​

Here we show the backup and restore of a single database. See the backup command summary for full backup and restore commands.

AWS S3​

BACKUP DATABASE test_backups 
TO S3(
'https://testchbackups.s3.amazonaws.com/<uuid>',
'<key id>',
'<key secret>'
)

Where uuid is a unique identifier, used to differentiate a set of backups.

Note

You will need to use a different uuid for each new backup in this subdirectory, otherwise you will get a BACKUP_ALREADY_EXISTS error. For example, if you are taking daily backups, you will need to use a new uuid each day.

Google Cloud Storage (GCS)​

BACKUP DATABASE test_backups 
TO S3(
'https://storage.googleapis.com/<bucket>/<uuid>',
'<hmac-key>',
'<hmac-secret>'
)

Where uuid is a unique identifier, used to identify the backup.

Note

You will need to use a different uuid for each new backup in this subdirectory, otherwise you will get a BACKUP_ALREADY_EXISTS error. For example, if you are taking daily backups, you will need to use a new uuid each day.

Azure Blob Storage​

BACKUP DATABASE test_backups 
TO AzureBlobStorage(
'<AzureBlobStorage endpoint connection string>',
'<container>',
'<blob>/<>'
);
)

Where uuid is a unique identifier, used to identify the backup.

Note

You will need to use a different uuid for each new backup in this subdirectory, otherwise you will get a BACKUP_ALREADY_EXISTS error. For example, if you are taking daily backups, you will need to use a new uuid each day.

Backup / Restore entire service​

For backing up the entire service, use the commands below. This backup will contain all user data and system data for created entities, settings profiles, role policies, quotas, and functions. We list these here for AWS S3. You can utilize these commands with the syntax described above to take backups for GCS and Azure Blob storage.

BACKUP 
    TABLE system.users,
    TABLE system.roles,
    TABLE system.settings_profiles,
    TABLE system.row_policies,
    TABLE system.quotas,
    TABLE system.functions,
    ALL EXCEPT DATABASES INFORMATION_SCHEMA, information_schema, system
TO S3(
    'https://testchbackups.s3.amazonaws.com/<uuid>',
    '<key id>',
    '<key secret>'
)

where uuid is a unique identifier, used to identify the backup.

FAQ​

What happens to the backups in my cloud object storage? Are they cleaned up by ClickHouse at some point?

We provide you the ability to export backups to your bucket, however, we do not clean up or delete any of the backups once written. You are responsible for managing the lifecycle of the backups in your bucket, including deleting, or archiving as needed, or moving to cheaper storage to optimize overall cost.

What happens to the restore process if I move some of the existing backups to another location?

If any backups are moved to another location, the restore command will need to be updated to reference the new location where the backups are stored.

What if I change my credentials required to access the object storage?

You will need to update the changed credentials in the UI, for backups to start happening successfully again.

What if I change the location to export my external backups to?

You will need to update the new location in the UI, and backups will start happening to the new location. The old backups will stay in the original location.

How can I disable external backups on a service that I enabled them for?

To disable external backups for a service, go to the service setting screen, and click on Change external backup. In the subsequent screen, click on Remove setup to disable external backups for the service.