Skip to main content

Backups

ClickHouse Cloud creates a backup of your service every 24 hours. Backups for Production services are retained for 2 days, while backups for Development services are retained for 1 day.

note

Please do not use the BACKUP and RESTORE commands when working with ClickHouse Cloud services. Cloud backups should be managed from the UI.

Backup status list​

Each of your services are backed up daily. You can see the backup list for a service on the Backups tab of the service. From there you can restore a backup, or delete a backup:

List of backups

Restore a backup​

Backups are restored to a new ClickHouse Cloud service. After clicking on the Restore backup icon you can specify the Service name of the new service that will be created, and then Restore this backup:

Restore details

The new service will show in the services list as Provisioning until it is ready:

New service provisioning

Working with your restored service​

You now have two similar services, the original service one that for some reason needed to be restored, and a new restored service that was restored from a backup of the original. You have two choices:

  1. Use the new restored service and remove the original service.
  2. Migrate data from the new restored service back to the original service and remove the new restored service.

Use the new restored service​

To use the new service perform these steps:

  1. Verify that the new service has the IP Access List entries required by your use cases.
  2. Verify that the new service contains the data that you need.
  3. Remove the original service.

Migrate data from the new restored service back to the original service​

Suppose you cannot work with the newly restored service for any reason; for example, if you have users or applications that connect to the existing service, you may decide to migrate the newly restored data into the original service. The migration can be accomplished by following these steps:

Allow remote access to the newly restored service​

The new service is restored from backup with the same IP Allow List as the original service, this means that connections will not be allowed from other ClickHouse Cloud services unless you had allowed access from everywhere. Modify the allow list and allow access from Anywhere temporarily. See the IP Access List docs for details.

On the newly restored ClickHouse service (the system that hosts the restored data)​

note

You will need to reset the password for the new service in order to access it, you can do that from the service list Settings tab.

  • Add a read only user that can read the source table (db.table in this example)

    CREATE USER exporter
    IDENTIFIED WITH SHA256_PASSWORD BY 'password-here'
    SETTINGS readonly = 1;
    GRANT SELECT ON db.table TO exporter;

  • Copy the table definition

    select create_table_query
    from system.tables
    where database = 'db' and table = 'table'

On the destination ClickHouse Cloud system (the one that had the damaged table):​

  • Create the destination database:

    CREATE DATABASE db

  • Using the CREATE TABLE statement from the source, create the destination.

    tip

    Change the ENGINE to to ReplicatedMergeTree without any parameters when you run the CREATE statement. ClickHouse Cloud always replicates tables and provides the correct parameters.

    CREATE TABLE db.table ...
    ENGINE = ReplicatedMergeTree
    ORDER BY ...

  • Use the remoteSecure function to pull the data from the newly restored ClickHouse Cloud service

    INSERT INTO db.table SELECT * FROM
    remoteSecure('source-hostname', db, table, 'exporter', 'password-here')

  • Verify the data in the service

  • Delete the newly restored service once the data is verified

Undeleting or undropping tables​

The UNDROP command is not supported in ClickHouse Cloud. If you accidentally DROP a table, the best course of action is to restore your last backup and recreate the table from the backup.

To prevent users from accidentally dropping tables, you can use GRANT statements to revoke permissions for the DROP TABLE command for a specific user or role.

Additionally, to prevent accidental deletion of data, please note that it is not possible to drop tables >1TB in size in ClickHouse Cloud. Please contact [email protected] if you wish to drop tables greater than this threshold.