Migrating from Self-Managed ClickHouse to ClickHouse Cloud Using Backup Commands

The policy includes both ARNs:

- **IAM user** (`docs-s3-user`): Allows your self-managed ClickHouse cluster to back up to S3
- **ClickHouse Cloud role** (`ClickHouseAccess-001`): Allows your Cloud service to restore from S3

## Taking the backup (on self-managed deployment)[​](#taking-a-backup-on-oss "Direct link to Taking the backup (on self-managed deployment)")

To make a backup of a single database, run the following command from clickhouse-client
connected to your OSS deployment:

```
BACKUP DATABASE nyc_taxi
TO S3(
  'BUCKET_URL',
  'KEY_ID',
  'SECRET_KEY'
)
```

Replace `BUCKET_URL`, `KEY_ID` and `SECRET_KEY` with your own AWS credentials.
The guide ["How to create an S3 bucket and IAM role"](/docs/integrations/s3/creating-iam-user-and-s3-bucket)
shows you how to obtain these if you don't yet have them.

If everything is correctly configured you will see a response similar to the one below
containing a unique id assigned to the backup and the status of the backup.

```
Query id: efcaf053-75ed-4924-aeb1-525547ea8d45

┌─id───────────────────────────────────┬─status─────────┐
│ e73b99ab-f2a9-443a-80b4-533efe2d40b3 │ BACKUP_CREATED │
└──────────────────────────────────────┴────────────────┘
```

If you check your previously empty S3 bucket you will now see some folders have appeared:

If you're performing a full migration then you can run the following command to backup the entire server:

```
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(
  'BUCKET_ID',
  'KEY_ID',
  'SECRET_ID'
)
SETTINGS
  compression_method='lzma',
  compression_level=3;
```

The command above backups up:

- All user databases and tables
- User accounts and passwords
- Roles and permissions
- Settings profiles
- Row policies
- Quotas
- User-defined functions

If you're using a different Cloud Service Provider (CSP), you can use the `TO S3()` (for both AWS and GCP) and `TO AzureBlobStorage()` syntax.

For very large databases, consider using `ASYNC` to run the backup in the background:

```
BACKUP DATABASE my_database 
TO S3('https://your-bucket.s3.amazonaws.com/backup.zip', 'key', 'secret')
ASYNC;
       
-- Returns immediately with backup ID
-- Example result:
-- ┌─id──────────────────────────────────┬─status────────────┐
-- │ abc123-def456-789                   │ CREATING_BACKUP   │
-- └─────────────────────────────────────┴───────────────────┘
```

The backup id can then be used to monitor the progress of the backup:

```
SELECT * 
FROM system.backups 
WHERE id = 'abc123-def456-789'
```

It is also possible to take incremental backups.
For more detail on backups in general, the reader is referred to the documentation for [backup and restore](/docs/operations/backup/overview).

## Restore to ClickHouse Cloud[​](#restore-to-clickhouse-cloud "Direct link to Restore to ClickHouse Cloud")

To restore a single database run the following query from your Cloud service, substituting your AWS credentials below,
setting `ROLE_ARN` equal to the value which you obtained as output of the steps detailed
in ["Accessing S3 data securely"](/docs/cloud/data-sources/secure-s3)

```
RESTORE DATABASE nyc_taxi
FROM S3(
    'BUCKET_URL',
    extra_credentials(role_arn = 'ROLE_ARN')
)
```

You can do a full service restore in a similar manner:

```
RESTORE
    TABLE system.users,
    TABLE system.roles,
    TABLE system.settings_profiles,
    TABLE system.row_policies,
    TABLE system.quotas,
    ALL EXCEPT DATABASES INFORMATION_SCHEMA, information_schema, system
FROM S3(
    'BUCKET_URL',
    extra_credentials(role_arn = 'ROLE_ARN')
)
```

If you now run the following query in Cloud you can see that the database and table have been
successfully restored on Cloud:

```
SELECT count(*) FROM nyc_taxi.trips_small;
3000317
```

- [Overview](#overview-migration-approaches)- [OSS preparation](#oss-setup)
    - [Create sample data](#create-sample-data)- [Cloud preparation](#cloud-setup)
      - [Set up S3 access](#set-up-s3-access)- [Taking the backup (on self-managed deployment)](#taking-a-backup-on-oss)- [Restore to ClickHouse Cloud](#restore-to-clickhouse-cloud)

Was this page helpful?