Migrate from Cloud SQL for PostgreSQL to AlloyDB for PostgreSQL

Preview — AlloyDB for PostgreSQL

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. You can process personal data for this feature as outlined in the Cloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Standard Free trial

This page describes how to migrate a Cloud SQL for PostgreSQL instance by copying a Cloud SQL backup into an AlloyDB for PostgreSQL cluster. Copying a Cloud SQL backup into an AlloyDB cluster enables you to quickly load data into AlloyDB for PostgreSQL, which lets you evaluate or migrate into AlloyDB.

This page assumes familiarity with Cloud SQL. If you're new to AlloyDB, see the AlloyDB overview.

To learn how to migrate your data from Cloud SQL to AlloyDB using continuous data replication, see Database Migration Service for PostgreSQL to AlloyDB.

The following aren't supported:

• Cross-project and cross-region restores
• Instances with Customer Managed Encryption Keys (CMEK)
• Instances with Identity and Access Management (IAM) group authentication

Before you begin

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. Enable the AlloyDB, Compute Engine, and Service Networking APIs.

   Enable the APIs

7. Make sure that you have the following:
   
   • The necessary Identity and Access Management (IAM) permissions
   • A Cloud SQL backup with a size that's less than 5 TB
   • A version of PostgreSQL supported by AlloyDB

Required roles

Note: You can't use custom Cloud SQL roles to migrate a Cloud SQL for PostgreSQL instance by copying the Cloud SQL backup into an AlloyDB cluster .

To get the permissions that you need to copy a Cloud SQL for PostgreSQL backup into an AlloyDB cluster, grant yourself the following IAM roles on your project:

• Viewer (roles/viewer)
• Cloud AlloyDB Admin (roles/alloydb.admin)

Copy a Cloud SQL backup into an AlloyDB cluster

Copying a Cloud SQL backup into an AlloyDB standard cluster restores the backup to the same version of PostgreSQL on AlloyDB, for example, a PostgreSQL 14 Cloud SQL backup restores to a PostgreSQL 14 standard cluster. Keep in mind that extension versions and PostgreSQL minor versions might be different.

Copying a backup from Cloud SQL only supports the configuration of those items supported in the AlloyDB standard cluster.

Note: You can't copy a backup if it's greater than 5TB.

To copy a Cloud SQL backup into an AlloyDB standard trial cluster, follow these steps:

Console

1. In the Google Cloud console, go to the Clusters page.
   

   Go to Clusters

2. Click Migrate data and then select Copy from Cloud SQL Backup.
3. In the Copy from Cloud SQL backup page, enable the required APIs. If you already enabled the APIs, you don't need to re-enable the APIs.
4. On the Select the cluster type page, select Provisioned cluster.
5. Click Select Cluster Type.
6. Select the Cloud SQL instance that you want to copy a backup from and then click Select instance. You can filter Cloud SQL instances.
   Only compatible database versions are displayed. Replicas don't have backups and aren't displayed in the list of available instances.
7. Select the backup you want to import from and then click Select backup. This page displays the most recent 1,000 backups.
8. In the Create your provisioned cluster page, enter your networking information. The cluster ID is populated by default, but you can also customize your provisioned cluster.
9. Click Create Cluster.

After the operation completes, a primary instance is automatically created. You see a new AlloyDB cluster with the data that is copied from the Cloud SQL for PostgreSQL backup that you selected.

When you copy a Cloud SQL for PostgreSQL backup into an AlloyDB cluster, database flags are migrated if the database flags are in Cloud SQL for PostgreSQL and in AlloyDB. Resource level permissions aren't automatically migrated. After the copy is complete, you must manually set up permissions and those database flags that weren't migrated due to a mismatch.

You can check which database flags in Cloud SQL for PostgreSQL are supported in AlloyDB in the Create your provisioned cluster page.

REST API

1. To get a list of backups for the instance from which you want to restore your backup, call the backupsRuns.list method:
   

         GET "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/backupRuns"
         

   Replace the following:

   • PROJECT_ID: the project ID.
   • INSTANCE_ID: the instance ID.

   Request JSON body:

         {
           "kind": string,
           "items": [
             {
               object (BackupRun)
             }
           ],
           "nextPageToken": string
         }
         

   To send your request, use one of these options:

   curl (Linux, macOS, or Cloud Shell)

   The following command assumes that you signed into the Google Cloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically signs you into the gcloud CLI.

   You can check the active account by running gcloud auth list.

   Save the request body in a file named request.json and execute the following command:

           curl -X GET \
                  -H "Authorization: Bearer $(gcloud auth print-access-token)" \
                  -H "Content-Type: application/json; charset=utf-8" \
                  "https://sqladmin.googleapis.com//sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/backupRuns"
           

   PowerShell (Windows)

   The following command assumes that you signed into the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically signs you into the gcloud CLI.

   You can check the active account by running gcloud auth list.

   Save the request body in a file named request.json and execute the following command:

            $cred = gcloud auth print-access-token
            $headers = @{ "Authorization" = "Bearer $cred" }
            Invoke-WebRequest `
             -Method GET `
             -Headers $headers `
             -ContentType: "application/json; charset=utf-8" `
             -Uri
             "https://sqladmin.googleapis.com//sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/backupRuns"|Select-Object -Expand Content
         

   You receive a JSON response similar to the following:

   Response

   If successful, the response body contains a list of BackupRun.

   The API response returns a list of backups for the instance as an array list, including "id": string,.

2. Call the restoreFromCloudSQL method:

           POST https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters:restoreFromCloudSQL
         

   Replace the following:

   • PROJECT_ID: the project ID.
   • REGION: the region in which the AlloyDB cluster is deployed.

   Request JSON body:

         {
           "clusterId": string,
           "cluster": {
             "databaseVersion": "POSTGRES_14/POSTGRES_15/POSTGRES_16",
             "subscriptionType": "STANDARD"
           },
           // Union field source can be only one of the following:
           "cloudsqlBackupRunSource": {
             object (CloudSQLBackupRunSource)
           }
           // End of list of possible types for union field source.
         }
         

   To send your request, use one of these options:

   curl (Linux, macOS, or Cloud Shell)

   The following command assumes that you signed into the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically signs you into the gcloud CLI.

   You can check the active account by running gcloud auth list.

   Save the request body in a file named request.json and execute the following command:

           curl -X POST \
                  -H "Authorization: Bearer $(gcloud auth print-access-token)" \
                  -H "Content-Type: application/json; charset=utf-8" \
                  -d @request.json \
                  "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters:restoreFromCloudSQL"
         

   PowerShell (Windows)

   The following command assumes that you signed into the gcloud CLI with your user account by running gcloud init or gcloudauth login, or by using Cloud Shell, which automatically signs you into the gcloud CLI.

   You can check the active account by running gcloud auth list.

   Save the request body in a file named request.json and execute the following command:

           $cred = gcloud auth print-access-token
           $headers = @{ "Authorization" = "Bearer $cred" }
           Invoke-WebRequest `
             -Method POST `
             -Headers $headers `
             -ContentType: "application/json; charset=utf-8" `
             -InFile request.json `
             -Uri "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters:restoreFromCloudSQL"|Select-Object -Expand Content
         

   You receive a JSON response similar to the following:

   Response

   If successful, the response body contains an instance of Operation.

   A new cluster is created in the specified project and location, with a volume restored from the backup pointed in the CloudSQLBackupRunSource message.

3. When the cluster is in a READY state, create the primary instance by calling the projects.locationsinstances.create method:
   

         POST https://alloydb.googleapis.com/v1beta/{parent=projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID}/instances
         

   Replace the following:

   • PROJECT_ID: the project ID.
   • REGION: the region in which the AlloyDB cluster is deployed.
   • CLUSTER_ID: the cluster ID.

   Request JSON body:

           {
             "instanceId": "string",
             "instance": {
               "object": "Instance"
             }
           }
         

   The request body contains an instance of Instance.

   To send your request, use one of these options:

   curl (Linux, macOS, or Cloud Shell)

   The following command assumes that you signed into the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically signs you into the gcloud CLI.

   You can check the active account by running gcloud auth list.

   Save the request body in a file named request.json and execute the following command:

           curl -X POST \
                -H "Authorization: Bearer $(gcloud auth print-access-token)" \
                -H "Content-Type: application/json; charset=utf-8" \
                -d @request.json \
                "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID/instances"
         

   PowerShell (Windows)

   The following command assumes that you signed into the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically signs you into the gcloud CLI.

   You can check the active account by running gcloud auth list.

   Save the request body in a file named request.json and execute the following command:

            $cred = gcloud auth print-access-token
            $headers = @{ "Authorization" = "Bearer $cred" }
            Invoke-WebRequest `
              -Method POST `
              -Headers $headers `
              -ContentType: "application/json; charset=utf-8" `
              -InFile request.json `
              -Uri "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID/instances"|Select-Object -Expand Content
         

   You receive a JSON response similar to the following:

   Response

   If successful, the response body contains a newly created instance of Operation.

4. After the instance is updated to the READY state, connect to the instance and access the restored data from the Cloud SQL snapshot.

What's next

• Connect to AlloyDB using public IP.
• Manage your data using AlloyDB Studio.