Migrating a database from Heroku PostgreSQL to Google Cloud SQL can seem daunting, but with the right steps, it can be done efficiently. This guide will walk you through the entire process, from setting up your Google Cloud environment to restoring your database on Cloud SQL.

Step 1: Create a New SQL Instance in Google Cloud

The first step is to create a new PostgreSQL instance in Google Cloud SQL. You can do this from the Google Cloud Console by following these steps:

1. Go to SQL in the Google Cloud Console.
2. Click Create Instance and select PostgreSQL.
3. Configure your instance settings, such as the region, machine type, and storage.
4. Set up database authentication and connectivity settings.
5. Click Create and wait for the instance to be provisioned.

Step 2: Create a Virtual Machine (VM) Instance

To facilitate the migration, you'll need a VM instance where you can run the database restoration process.

1. Go to the Google Compute Engine.
2. Click Create Instance.
3. Choose a machine type that fits your needs.
4. Set the zone to match your SQL instance’s region.
5. Click Create.

Step 3: Connect to the VM

Once the VM instance is up and running, connect to it via SSH:

gcloud compute ssh migrator --zone europe-west3-c

This will give you shell access to the VM, where you’ll download the Cloud SQL Proxy.

Step 4: Download and Set Up Cloud SQL Proxy

The Cloud SQL Proxy is necessary to connect securely to your Cloud SQL instance. Download and install it using the following commands:

URL="https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.14.2" curl "$URL/cloud-sql-proxy.linux.amd64" -o cloud-sql-proxy chmod +x cloud-sql-proxy

Start the Cloud SQL Proxy:

./cloud-sql-proxy instance-name:europe-west10:main-prod

This will establish a secure connection between your VM and your Cloud SQL instance.

Step 5: Authorize Google Cloud Instance

To ensure proper authentication, authorize your instance:

gcloud auth application-default login

Follow the prompts to authenticate via OAuth and paste the token back into the console when prompted.

Step 6: Install PostgreSQL Client

To restore the database, install the PostgreSQL client on your VM:

sudo apt install postgresql-client

Step 7: Download the Heroku Database Backup

Before restoring the database, you need to create a backup from Heroku and download it.

Get the backup URL from Heroku:

heroku pg:backups:url --app your-app-name

Download the backup file to your VM:

curl "https://your-backup-url.s3.amazonaws.com/path-to-backup" > db.back

(Replace "https://your-backup-url.s3.amazonaws.com/path-to-backup" with the actual URL from Heroku.)

Step 8: Use screen to Keep the Restore Process Running

Since database restoration can take a while, use screen to keep the session running in the background:

screen

This allows you to detach from the session and reconnect if needed.

Step 9: Restore the Database to Cloud SQL

Now, restore the database using pg_restore:

pg_restore \ -j 20 \ --verbose \ --clean \ --no-acl \ --no-owner \ -h 127.0.0.1 \ -p 5432 \ -U user \ -d staging \ db.back

You’ll be prompted to enter the database password.

To detach from screen and let the process continue running in the background, press:

Ctrl + A, then D

To return to the session, use:

screen -r

Step 10: Allow IPs for External Access

If your application requires access to the database from specific IP ranges, update the authorized networks:

gcloud sql instances patch main-prod --authorized-networks="52.48.0.0/14,34.248.0.0/13,34.236.123.2,52.208.0.0/13,..."

(Replace the IP ranges with your actual application’s IPs.)

Conclusion

Congratulations! You have successfully migrated your Heroku PostgreSQL database to Google Cloud SQL. Your database is now running on Google Cloud, and you can integrate it with your application.

For further optimizations, consider configuring automated backups, setting up failover replicas, and fine-tuning PostgreSQL performance settings in Google Cloud.