Migrating GCP Cloud SQL PostgreSQL from Project A to Project B

Ankit Arora

• 4 min read
Migrating GCP Cloud SQL PostgreSQL from Project A to Project B

Migrating a database from one GCP project to another can be a complex task, especially when dealing with large amounts of data and limited downtime. In this blog post, we will explore a method using backup restoration to migrate a GCP Cloud SQL PostgreSQL database from Project A to Project B with minimal disruption.

Exploring Migration Options

Before diving into the backup restoration method, let's briefly discuss other options that were considered for the migration process.

These options include:

Database Migration Service by GCP: GCP offers a dedicated service for database migration, but due to specific requirements and data size (above 1TB), it was not the optimal choice for this migration.

pg_dump and pg_restore: The pg_dump and pg_restore utilities provide a way to export and import PostgreSQL database dumps. However, setting up and transferring a large dataset could lead to significant downtime and performance issues.

Considering the challenges posed by these options, the backup restoration method emerged as the preferred approach.

The Backup Restoration Method

The backup restoration method involves taking a backup of the source Cloud SQL PostgreSQL instance in Project A and restoring it to a new instance in Project B. This approach allows for minimal downtime and ensures data consistency during the migration process.

Step 1: Prepare the Destination SQL Machine

First, create a new SQL machine in Project B with identical configurations and flags to the source Cloud SQL instance in Project A. This ensures compatibility and consistency during the migration process.

Step 2: Manage Connections and Operations on Source DB

To minimize downtime and prevent data inconsistencies, follow these steps to manage connections and operations on the source Cloud SQL instance in Project A:

  • Stop all write-and-read operations from applications, producers, and consumers connected to the source database.
  • Wait for all existing connections to drop on the source Cloud SQL instance.

Step 3: Take a Backup of the Source Cloud SQL Instance

Once the connections have dropped and the database is idle, it's time to take a backup of the source Cloud SQL instance. This backup will serve as the basis for the migration process.

To obtain the latest backup, execute the following command:

curl -X GET -H "Authorization: Bearer $(gcloud auth print-access-token)" "https://sqladmin.googleapis.com/v1/projects/$SOURCE_PROJECT_ID/instances/$SOURCE_INSTANCE_ID/backupRuns" > "$BACKUP_FILE_NAME"

Or you can also go to GCP Cloud SQL Console in your browser to create an On-Demand backup.

To do it from the console:

  1. Go to the Cloud SQL Instances page.

2. Open the Overview page of an SQL instance, by clicking the instance name.

3. Select Backups from the SQL navigation menu.

4. Click Create backup.

5. On the Create backup page, add a description, if needed, and click Create.

Step 4: Execute the Migration Script

Now, one the backup process is completed, it's time to execute the migration script. The script automates the restoration process using the backup taken from the source Cloud SQL instance. It creates a request.json file, which contains the necessary information for the restoration process.

#!/bin/bash

set -e
set -u

# Set variables
SOURCE_PROJECT_ID="<enter source project id here>"
SOURCE_INSTANCE_ID="<enter source db instance id here>"
BACKUP_FILE_NAME="backup_ids.txt"
DEST_PROJECT_ID="<enter destination project id here>"
DEST_INSTANCE_ID="<enter destination db instance id here>"

# Obtain the backupId and put it into a file
curl -X GET -H "Authorization: Bearer $(gcloud auth print-access-token)" "https://sqladmin.googleapis.com/v1/projects/$SOURCE_PROJECT_ID/instances/$SOURCE_INSTANCE_ID/backupRuns" > "$BACKUP_FILE_NAME"
echo ""

head -18 "$BACKUP_FILE_NAME"

echo ""
echo ""
echo ""

# Get the latest backup id from file
LATEST_BKP_ID=$(grep -A 2 "SUCCESSFUL" "$BACKUP_FILE_NAME" | grep id | sed 's/ //g' | tr -d '"' | tr -d "," | cut -d ":" -f 2 | head -1)

echo "Latest Backup ID is: $LATEST_BKP_ID"

echo "Waiting for 30 seconds, so that we can cross verify the Backup ID in the console."
sleep 30

# Create request.json
cat << EOF > request.json
{
  "restoreBackupContext":
  {
    "backupRunId": "$LATEST_BKP_ID",
    "project": "$SOURCE_PROJECT_ID",
    "instanceId": "$SOURCE_INSTANCE_ID"
  }
}
EOF

echo ""
echo "Created request.json"
cat request.json
echo ""

echo "Waiting for 30 seconds to verify the content of request.json presented on the screen."
sleep 30

# Restore backup to the new instance
curl -X POST -H "Authorization: Bearer $(gcloud auth print-access-token)" -H "Content-Type: application/json; charset=utf-8" -d @request.json "https://sqladmin.googleapis.com/v1/projects/$DEST_PROJECT_ID/instances/$DEST_INSTANCE_ID/restoreBackup"

echo "Restoration started."
💡
You can also find the script here in my GitHub.

Update the following variables in the script according to your specific environment:

  • SOURCE_PROJECT_ID: The ID of Project A.
  • SOURCE_INSTANCE_ID: The ID of the source Cloud SQL instance in Project A.
  • DEST_PROJECT_ID: The ID of Project B.
  • DEST_INSTANCE_ID: The ID of the destination Cloud SQL instance in Project B.

Once the variables are set, execute the script and wait for it to complete:

bash ./migrate_gcp_sql_from_projectA_to_projectB.sh

The script performs the following actions:

  1. Retrieves the latest backup ID from the backup file.
  2. Creates the request.json file with the necessary parameters for the restoration process.
  3. Initiates the restoration process on the destination Cloud SQL instance in Project B using the request.json file.

Step 5: Monitor the Restoration Process

After executing the script, the destination Cloud SQL instance in Project B will enter the restoration phase. It may take a few minutes for the database instance to become ready.

Monitor the restoration progress through the GCP Cloud Console or by using the GCP Cloud SQL APIs. Once the restoration process is complete, the database in Project B will be ready for use.

Step 6: Change the IP in your Application config or DNS and start the services again.

Once you have migrated the DB, you can use the IP of the new DB instance and start using it in your applications.

Verify if everything is up and running and you are seeing connections coming on the DB from GCP monitoring.

Caveats

  • The restore process overwrites all the current data on the instance, including previous point-in-time recovery logs. Overwritten data cannot be recovered. So make sure that the destination instance is fresh and there's no data on it.
  • Disk size of the destination DB instance will become equal to the disk size of the source DB once the migration gets completed.
    Example: If your source DB disk size is 1TB and you have created a destination DB with a disk size 500GBs, then after the migration it will automatically increase the disk size to 1TB.

Conclusion

Migrating a GCP Cloud SQL PostgreSQL database from Project A to Project B can be achieved using various methods. In this blog post, we explored the backup restoration method, which provides a balance between minimal downtime and data consistency. This whole process mentioned should not take more than 15 mins for data up to 1 TBs in size(tested results).

By following the step-by-step process outlined in this article, you can migrate your GCP Cloud SQL PostgreSQL database seamlessly from one project to another. Remember to consider your specific requirements and adapt the script and steps accordingly.

Happy migrating!!!

Sign up for more like this.

Enter your email
Subscribe