Migrating to Cloud SQL using Google Cloud Database Migration Service

Image by Gerd Altmann from Pixabay

This article is part of the series Migrating a Self-Managed Database to Google Cloud SQL. The first part of the series covered why you may consider migrating to Cloud SQL, the migration process and setting up a postgres server on Google Compute Engine (GCE) to simulate on-prem setup. The second part covered migrating to Cloud SQL using the standard pg_dump utility. If you have not already seen the previous articles, you may consider checking them out. This article covers migrating to Cloud SQL using Google Cloud Database Migration Service.

Database Migration Service (DMS) is a serverless offering that relies on native database replication to quickly and securely migrate databases to Cloud SQL with minimal downtime. It makes it easy to migrate databases to Cloud SQL from on premises, GCE, and other clouds.

The serverless offering eliminates the typical need to provision, manage and monitor compute resources. The easy-to-use and guided experience eliminates the need to read through lengthy documentation in order to set up a network. Whether the database is on-premises, self-hosted on Google cloud or running on another cloud, DMS provides an integrated experience that simplifies the migration process. The result is improved performance, lowered cost, and ease of management and operations.

DMS provides options for one-time and continuous migrations from source databases to Cloud SQL destination databases using different connectivity options, including IP allowlists, VPC peering, and reverse SSH tunnels. The current list of supported sources and destinations can be found in the documentation.

One-time migration

In the one-time migration type, a single point-in-time snapshot of the database is taken from the source (self-managed database) and loaded into the destination (Cloud SQL). After which the destination instance is automatically promoted to a primary instance. When the migration is in progress, there can be no new writes to this source database, the dependent applications may experience downtime.

Continuous or on-going migration

For the continuous or on-going migration type, an initial snapshot of the database is taken and loaded into the destination. After the initial full dump phase is completed, on-going changes in the data are sent in Change Data Capture (CDC) mode. When you decide to switch to using the destination, you stop writing to the source and initiate a promotion of the destination instance (Cloud SQL) to a primary instance. The dependent applications may experience a little downtime when the instance is being promoted, then you point the applications to the new primary instance. See the documentation for further details.

At the time of this writing, one-time migration support for PostgreSQL has not been launched. Also, DMS is available for MySQL and PostgreSQL, with SQL Server migrations and Oracle to PostgreSQL migrations in preview.

Connectivity Options

There are 3 ways to configure connectivity from the source database to the source database.

• VPC peering: uses private connectivity. To use this connectivity option, you need to set up some networking to peer your source database’s network to the Google Cloud network using Cloud VPN or Cloud Interconnect.
• Reverse SSH tunnel using a cloud-hosted VM: used for secure connectivity when you don’t have a virtual private cloud (VPC) network in Google Cloud from which you can reach the source database.
• IP allowlists: uses public connectivity. Compared to VPC peeing and Reverse SSH, IP allowlist is the least secure.

See documentation for more details on the connectivity options and their use cases.
This Google blog also explains the technicalities of the connectivity methods, you may consider checking it out.

Configuring a continuous Database Migration Service job for PostgreSQL

To configure a continuous DMS job to migrate databases from a PostgreSQL instance to Cloud SQL for PostgreSQL, the following steps are required:

1. Prepare the source database for migration.
2. Create a DMS connection profile for the source instance.
3. Configure connectivity between the source and destination database instances.
4. Configure firewall and database access rules to allow access to the source database for migration.
5. Create, run, and verify a continuous migration job using Database Migration Service.
6. Promote the destination instance (Cloud SQL for PostgreSQL) to be a stand-alone database for reading and writing data.

To prepare the source PostgreSQL database for migration, it is important to note the following:

• DMS doesn’t support migrating tables that don’t have a primary key. Starting a migration against a database that has tables without primary keys will still succeed, but it won’t bring over the data from a table that’s lacking a primary key, but the table will still be created. To walk around this, check out this Google blog for details.
• DMS relies on pglogical for the migration work. This means that the pglogical extension has to be installed on each of the databases you want to migrate. Check out known limitations for using a PostgreSQL database as a source.
• Be sure your source instance version is supported. Current list of supported versions can be found in the documentation.
• If encrypted databases require customer-managed encryption keys to decrypt the databases, and if Database Migration Service doesn’t have access to the keys, then the databases can’t be migrated.
• Be sure to enable your database to listen and accept connections from non-localhost locations. To achieve this, edit the postgresql.conf file and change the default #listen_addresses = ‘localhost’ to listen_addresses = ‘*’
• Databases that are added after the migration job has started are not migrated.

Further details of the limitations for using a PostgreSQL database as a source can be seen in the documentation.

This comprehensive and detailed lab on Google Cloud Skills Boost demonstrates migrating to Cloud SQL using continuous Database Migration Service jobs with VPC peering connectivity method.

Migrate to Cloud SQL for PostgreSQL using Database Migration Service | Google Cloud Skills Boost

And there you have it, Database Migration Service makes it easy to migrate to Cloud SQL. The result is improved performance, lowered cost, and ease of management and operations.