Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Take a backup or restore a backup using commands

View as MarkdownEdit this pageReport an issueSet up docs MCP

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

  1. GCS endpoint, in the format: https://storage.googleapis.com/<bucket_name>/
  2. Access HMAC key and HMAC secret.

  1. Azure storage connection string.
  2. Azure container name in the storage account.
  3. Azure Blob within the container.

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.

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

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.

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

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.

RESTORE DATABASE test_backups
FROM AzureBlobStorage(
  '<AzureBlobStorage endpoint connection string>',
  '<container>',
  '<blob>/<uuid>'
)

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.

RESTORE ALL
FROM S3(
    'https://testchbackups.s3.amazonaws.com/<uuid>',
    '<key id>',
    '<key secret>'
)
SETTINGS restore_access_entities_with_current_grants = 1

The restore_access_entities_with_current_grants setting is available from ClickHouse Cloud version 26.4 (build 26.4.1.1942 or later). You can check the version your service is running with:

SELECT version()

When restore_access_entities_with_current_grants is enabled, restored users and roles have their grants limited to what the restoring user is allowed to grant (the same semantics as GRANT CURRENT GRANTS), instead of the RESTORE failing with ACCESS_DENIED when the backup contains more permissions than the restoring user can grant.

On versions earlier than 26.4, omit the setting.

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 don’t clean up or delete any of the backups once written. You’re 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.

Navigation