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

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/07_OSS_to_Cloud/02_oss_to_cloud_backups.md)

## Overview[​](#overview-migration-approaches "Direct link to Overview")

There are two primary methods to migrate data from self-managed ClickHouse (OSS) to ClickHouse Cloud:

- Using the [`remoteSecure()`](/docs/cloud/migration/clickhouse-to-cloud) function in which data is directly pulled/pushed.
- Using `BACKUP`/`RESTORE` commands via cloud object storage

> This migration guide focuses on the `BACKUP`/`RESTORE` approach and offers a practical example
> of migrating a database or full service in open source ClickHouse to Cloud via an S3 bucket.

**Prerequisites**

- You have Docker installed
- You have an [S3 bucket and IAM user](/docs/integrations/s3/creating-iam-user-and-s3-bucket)
- You're able to create a new service ClickHouse Cloud service

To make the steps in this guide easy to follow along with and reproducible, we'll use one of the docker compose recipes
for a ClickHouse cluster with two shards, and two replicas.

Cluster required

This backup method requires a ClickHouse cluster because tables must be converted from the `MergeTree` engine to `ReplicatedMergeTree`.
If you're running a single instance, follow the steps in ["Migrating between self-managed ClickHouse and ClickHouse Cloud using remoteSecure"](/docs/cloud/migration/clickhouse-to-cloud) instead.

## OSS preparation[​](#oss-setup "Direct link to OSS preparation")

We'll first spin up a ClickHouse cluster using a Docker Compose configuration from our examples repository.
You can ignore spinning up the ClickHouse cluster if you already have one running.

1. Clone the [examples repository](https://github.com/ClickHouse/examples) to your local machine
2. From your terminal, `cd` into `examples/docker-compose-recipes/recipes/cluster_2S_2R`
3. Make sure Docker is running, then start the ClickHouse cluster:

```
docker compose up
```

You should see:

```
[+] Running 7/7
 ✔ Container clickhouse-keeper-01  Created  0.1s
 ✔ Container clickhouse-keeper-02  Created  0.1s
 ✔ Container clickhouse-keeper-03  Created  0.1s
 ✔ Container clickhouse-01         Created  0.1s
 ✔ Container clickhouse-02         Created  0.1s
 ✔ Container clickhouse-04         Created  0.1s
 ✔ Container clickhouse-03         Created  0.1s
```

From a new terminal window at the root of the folder run the following command to connect to the first node of the cluster:

```
docker exec -it clickhouse-01 clickhouse-client
```

### Create sample data[​](#create-sample-data "Direct link to Create sample data")

ClickHouse Cloud works with [`SharedMergeTree`](/docs/cloud/reference/shared-merge-tree).
When restoring a backup, ClickHouse automatically converts tables with `ReplicatedMergeTree` to `SharedMergeTree` tables.

It's likely your tables are already using the `ReplciatedMergeTree` engine if you're running a cluster.
If not, you will need to convert any `MergeTree` tables to `ReplicatedMergeTree` before backing them up.

For the sake of demonstration of how to convert `MergeTree` tables to `ReplicatedMergeTree`, we will begin with a `MergeTree` table and convert it to `ReplicatedMergeTree` after wards.
We're going to follow the first two steps of the [New York taxi data guide](/docs/getting-started/example-datasets/nyc-taxi) to create a sample table and load data into it.
Those steps are included below for your convenience.

Run the following commands to create a new database and insert data from an S3 bucket into a new table:

```
CREATE DATABASE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small (
    trip_id             UInt32,
    pickup_datetime     DateTime,
    dropoff_datetime    DateTime,
    pickup_longitude    Nullable(Float64),
    pickup_latitude     Nullable(Float64),
    dropoff_longitude   Nullable(Float64),
    dropoff_latitude    Nullable(Float64),
    passenger_count     UInt8,
    trip_distance       Float32,
    fare_amount         Float32,
    extra               Float32,
    tip_amount          Float32,
    tolls_amount        Float32,
    total_amount        Float32,
    payment_type        Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
    pickup_ntaname      LowCardinality(String),
    dropoff_ntaname     LowCardinality(String)
)
ENGINE = MergeTree
PRIMARY KEY (pickup_datetime, dropoff_datetime);
```

```
INSERT INTO nyc_taxi.trips_small
SELECT
    trip_id,
    pickup_datetime,
    dropoff_datetime,
    pickup_longitude,
    pickup_latitude,
    dropoff_longitude,
    dropoff_latitude,
    passenger_count,
    trip_distance,
    fare_amount,
    extra,
    tip_amount,
    tolls_amount,
    total_amount,
    payment_type,
    pickup_ntaname,
    dropoff_ntaname
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz',
    'TabSeparatedWithNames'
);
```

Run the following command to `DETACH` the table.

```
DETACH TABLE nyc_taxi.trips_small;
```

Then attach it as replicated:

```
ATTACH TABLE nyc_taxi.trips_small AS REPLICATED;
```

Finally, restore the replica metadata:

```
SYSTEM RESTORE REPLICA nyc_taxi.trips_small;
```

Check that it was converted to `ReplicatedMergeTree`:

```
SELECT engine
FROM system.tables
WHERE name = 'trips_small' AND database = 'nyc_taxi';

┌─engine──────────────┐
│ ReplicatedMergeTree │
└─────────────────────┘
```

You're now ready to proceed with setting up your Cloud service in preparation for later
restoring a backup from your S3 bucket.

## Cloud preparation[​](#cloud-setup "Direct link to Cloud preparation")

You will be restoring your data into a new Cloud service.
Follow the steps below to create a new Cloud service.

Open Cloud Console

#### Open Cloud Console[​](#open-cloud-console "Direct link to Open Cloud Console")

Go to <https://console.clickhouse.cloud/>

Create a new service

#### Create a new service[​](#create-new-service "Direct link to Create a new service")

Configure and create a service

#### Configure and create a service[​](#configure-and-create "Direct link to Configure and create a service")

Choose your desired region and configuration, then click `Create service`

Create an access role

#### Create an access role[​](#create-an-access-role "Direct link to Create an access role")

Open SQL console

### Set up S3 access[​](#set-up-s3-access "Direct link to Set up S3 access")

To restore your backup from S3, you'll need to configure secure access between ClickHouse Cloud and your S3 bucket.

1. Follow the steps in ["Accessing S3 data securely"](/docs/cloud/data-sources/secure-s3) to create an access role and obtain the role ARN.
2. Update the S3 bucket policy you created in ["How to create an S3 bucket and IAM role"](/docs/integrations/s3/creating-iam-user-and-s3-bucket) by adding the role ARN from the previous step.

Your updated policy for the S3 bucket will look something like this:

```
{
    "Version": "2012-10-17",
    "Id": "Policy123456",
    "Statement": [
        {
            "Sid": "abc123",
            "Effect": "Allow",
            "Principal": {
                "AWS": [
#highlight-start                  
                    "arn:aws:iam::123456789123:role/ClickHouseAccess-001",
                    "arn:aws:iam::123456789123:user/docs-s3-user"
#highlight-end                            
                ]
            },
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::ch-docs-s3-bucket",
                "arn:aws:s3:::ch-docs-s3-bucket/*"
            ]
        }
    ]
}
```

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'
)
```