CloudSQL backup and restore

As our life gets more and more easy to handle our infrastructure on Cloud. I came across backing up and restoring databases from a CloudSQL instance on a project and restore it to a different project's instance.

Google's Cloud SQL provides a cloud-based alternative to local MySQL, PostgreSQL, and SQL Server databases. You should use Cloud SQL if you want to spend less time managing your database and more time using it.

We'll see 4 different use cases and I am using PostgreSQL version of CloudSQL here.

1) Backing up our source instance

2) Restoring an instance from a backup

3) Restoring to a different instance

4) Restoring to an instance in another project

First 3 scenarios, we can get this done from console, gcloud or REST services. However the 4th scenario can be done only through a REST call. Let's see them one by one.

Backing up our source instance:

Restoring an instance from a backup:

Describe the instance to see whether it has any replicas:

    gcloud sql instances describe INSTANCE_NAME

Note any instances listed under replicaNames.

Delete all replicas if you have any.

    gcloud sql instances delete REPLICA_NAME

Repeat for all replicas.

List the backups for the instance.

    gcloud sql backups list --instance INSTANCE_NAME

Find the backup you want to use and record its ID value.

Note: Select a backup that is marked SUCCESSFUL.

Restore the instance from the specified backup:

    gcloud sql backups restore BACKUP_ID \
    --restore-instance=INSTANCE_NAME

After the restore operation completes, recreate any replicas you deleted in the first step.

Restoring to a different instance:

Only difference from previous step is you have to mention both source and target in the restore command.

    gcloud sql backups restore BACKUP_ID \
    --restore-instance=TARGET_INSTANCE_NAME \
     --backup-instance=SOURCE_INSTANCE_NAME

Restoring to an instance in another project:

Here we have to do a REST API call to restore backup of CloudSQL instance from an existing project to a different instance from another project.

Note: The user restoring to a different project must have the cloudsql.instances.restoreBackup permission for the target project and the cloudsql.backupRuns.get permission for the source instance. These permissions are included in the Cloud SQL Admin role.

curl -X GET \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
"https://sqladmin.googleapis.com/v1/projects/project_id/instances/instance_id/backupRuns" -u balaajid:<myPassword>

You should see a JSON output as below,

{

"kind": "sql#backupRunsList",

"items": [

{

"kind": "sql#backupRun",

"status": "SUCCESSFUL",

"enqueuedTime": "2021-12-15T08:31:08.337Z",

"id": "123456600000",

"startTime": "2021-12-15T08:31:08.367Z",

"endTime": "2021-12-15T08:32:39.693Z",

"type": "AUTOMATED",

"windowStartTime": "2021-12-15T07:00:00Z",

"instance": "cloudsql",

"selfLink": "https://sqladmin.googleapis.com/v1/projects/project_id/instances/instance_id/backupRuns/123456600000",

"location": "australia-southeast1",

"backupKind": "SNAPSHOT"

}

]

}

Let's create a file called restore.json and put the contents as below. This is filled with the detailed as per output from previous command.

vi restore.json

{
  "restoreBackupContext":
    {
    "backupRunId": 123456600000,
    "project": "source_project_id",
    "instanceId": "source_instance_id"
    }
 }

Save the file.

Now we have to POST an API call to trigger the restore.

curl -X POST \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @restore.json \
"https://sqladmin.googleapis.com/v1/projects/project_id/instances/instance_id/restoreBackup" -u balaajid:<myPassword>

Output as below,

{

"kind": "sql#operation",

"targetLink": "https://sqladmin.googleapis.com/v1/projects/project_id/instances/instance_id",

"status": "PENDING",

"user": "balaajid@dbgenre.com",

"insertTime": "2021-12-16T06:23:20.273Z",

"operationType": "RESTORE_VOLUME",

"name": "80ab4036-3349-47d0-a5b2-9751ecdfe1de",

"targetId": "targer_instance_id",

"selfLink": "https://sqladmin.googleapis.com/v1/projects/project_id/operations/80ab4036-3349-47d0-a5b2-9751ecdfe1de",

"targetProject": "targer_project_id"

}

Done. Based on the size of your source database, wait for the restore to complete successfully. You will see a wait sign until the instance is restored. Once the restore is complete, you will see a green tick symbol in the SQL instances dashboard.

Hope this article was useful.

Cheers,

Balaaji Dhananjayan