Migrate a database to AlloyDB for PostgreSQL by using Database Migration Service

Preview

This product is covered by the Pre-GA Offerings Terms of the Google Cloud Terms of Service. Pre-GA products might have limited support, and changes to pre-GA products might not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

MySQL   |  PostgreSQL   |  Oracle to PostgreSQL   |  PostgreSQL to AlloyDB

This quickstart shows you how to use Database Migration Service to migrate data to AlloyDB for PostgreSQL. The resources created in this quickstart typically cost less than one dollar (USD), assuming you complete the steps, including the clean up, in a timely manner.

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 Cloud project. Learn how to check if billing is enabled on a 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 Cloud project. Learn how to check if billing is enabled on a project.

6. Enable the Database Migration Service API.

   Enable the API

7. Make sure that you have the Database Migration Admin role assigned to your user account.

   Go to the IAM page

Requirements

Database Migration Service offers a variety of source database options and connectivity methods. Different sources work better with some connectivity methods than with others. In this quickstart, we assume that you're using a standalone PostgreSQL database in an environment where you can configure your network to add an inbound firewall rule. The source database can be on-premises or in a cloud provider. Because we can't know the specifics of your environment, we can't provide detailed steps when it comes to your networking configuration.

In this quickstart, you'll select PostgreSQL for the Source database engine, AlloyDB for PostgreSQL for the Destination database engine and VPC peering as the networking method.

Create a connection profile

By creating a connection profile, you're creating a record that contains information about the source database. Database Migration Service uses the information in the connection profile to migrate data from your source database to the destination AlloyDB database instance.

1. Go to the Database Migration Service Connection profiles page in the Google Cloud console.

   Go to the Database Migration Service Connection Profiles Page

2. Click CREATE PROFILE.

3. Open the Database engine drop-down list and select the classification type of your source database.

   The options are:

   • A standalone PostgreSQL instance (PostgreSQL)
   • An existing Cloud SQL for PostgreSQL instance (Cloud SQL for PostgreSQL)
   • An Amazon RDS instance for PostgreSQL (Amazon RDS for PostgreSQL)

   For this quickstart, select PostgreSQL.

   There are three list items associated with a standalone MySQL instance (MySQL), an existing Cloud SQL for MySQL instance (Cloud SQL for MySQL), and an Amazon RDS instance for MySQL (Amazon RDS for MySQL). These list items are covered in the MySQL version of this quickstart.

   There are also three list items associated with a standalone SQL Server instance (SQL Server), an existing Cloud SQL for SQL Server instance (Cloud SQL for SQL Server), and an Amazon RDS instance for SQL Server (Amazon RDS for SQL Server). These list items are covered in the SQL Server version of this quickstart.

   Lastly, there's a list item associated with an Oracle database. This item is covered in the Oracle to Cloud SQL for PostgreSQL version of this quickstart.

4. Supply the following information:
   • In the Connection profile name field, enter a name for the connection profile for your source database, such as My Connection Profile.
   • Keep the auto-generated Connection profile ID.
   • Enter Connectivity information:
     • If you're replicating from a self-hosted database, then enter the Hostname or IP address (domain or IP) and Port to access the host. (The default PostgreSQL port is 5432.)
     • If you're replicating from a Cloud SQL database, then select the Cloud SQL instance from the drop-down list.
   • Enter a Username and Password to authenticate to your source instance.

5. In the Connection profile region section of the page, select the region where you want to save the connection profile.

   Connection profiles, like all resources, are saved in a region. Region selection doesn't impact which migration jobs can use them, or which regions can connect to the data location itself, but can impact availability in the case of regional downtime.

6. Click CREATE.

Create a migration job

Database Migration Service uses migration jobs to migrate data from your source database instance to the destination AlloyDB database instance.

Creating a migration job includes:

• Defining settings for the job
• Selecting the connection profile that you created for your source database (source connection profile)
• Defining settings for the destination AlloyDB database instance
• Setting up connectivity between the source and destination database instances
• Testing the migration job to ensure that the connection information you provided for the job is valid

Define settings for the migration job

1. Go to the Database Migration Service Migration jobs page in the Google Cloud console.

   Go to the Database Migration Service Migration Jobs Page

2. Click CREATE MIGRATION JOB.

3. In the Migration job name field, enter a name for the migration job, such as My Migration Job.

4. Keep the auto-generated Migration job ID.

5. Open the Source database engine drop-down list and select the classification type of your source database. In this quickstart, select AlloyDB for PostgreSQL.

6. Select the Destination region where the destination instance is to be created.

7. Set the Migration job type to "Continuous" because you want ongoing changes in your source database to be migrated to the destination AlloyDB database instance.

8. Review the required prerequisites that are generated automatically to reflect how the environment must be prepared for a migration job. These prerequisites can include how to configure the source database and how to connect it to the destination AlloyDB database instance. It's best to complete these prerequisites at this step, but you can complete them at any time before you test the migration job or start it. For more information about these prerequisites, see Configure your source.

9. Click SAVE & CONTINUE.

Specify information about the source connection profile

1. Open the Select source connection profile drop-down list and select the connection profile that you created.

2. Click SAVE & CONTINUE.

Create a destination AlloyDB cluster

Clusters are the top-level resource in AlloyDB. To create a destination cluster:

1. Choose a cluster type. Database Migration Service currently supports Highly available AlloyDB clusters. They can serve data from more than one zone in a region, with no read pools.
2. Click CONTINUE.
3. Configure your cluster:
   1. In the Cluster ID field, enter an ID for your cluster.
   2. In the Password field, enter a password for the default postgres user. You will need the password to log in to your database.
   3. In the Network field, select a network path to define which resources are available when setting the migration connectivity. Clusters can only be configured with a private IP network path. If you plan to connect to the source database via VPC peering, select the VPC where it resides.
   4. Optionally, select an allocated IP range name to specify IP addresses your instance can connect with.
   5. Click CONTINUE.
4. Configure your primary instance. A primary instance determines a cluster's compute capacity and supports read and write operations:
1. In the Instance ID field, enter an ID for your primary instance.
2. Select a machine type.
3. Optional: Set flags for your instance. You can use flags to customize your instance. For information on supported flags, see AlloyDB documentation. For each flag:
   1. Click ADD FLAG.
   2. Select a flag from the New database flag list.
   3. Provide a value for the flag.
   4. Click DONE.
5. Click SAVE & CONTINUE.
6. Confirm your choice by clicking CREATE DESTINATION & CONTINUE.
7. Wait for the creation of the destination instance to finish.

Your AlloyDB cluster will be in a "Bootstrapping" state while it's being managed by Database Migration Service. While in "Bootstrapping", you will not be able to perform any operations on your cluster. If you still wish to delete the cluster, you can do so using gcloud with the --force option.

Set up connectivity

1. Choose the networking method that you'd like to use to establish connectivity between the source and destination databases. For this quickstart, use the Connectivity method drop-down list to select VPC peering as the networking method.
Review the VPC network of your source database. Click CONFIGURE & CONTINUE to complete the connection profile configuration.

Test and create the migration job

1. Review the settings you chose for the migration job.

2. Click TEST JOB to verify that the source has been configured correctly, that the source and destination instances are able to communicate with each other, and that the migration job is valid.

3. Verify that you see the "Tests passed successfully!" status.

   If the test fails, then you can address the problem in the appropriate part of the flow, and return to re-test.

4. Click CREATE & START JOB to create the migration job and start it immediately.

5. Click START in the subsequent dialog box.

6. In the Migration jobs page, verify that your migration job has a status of "Starting". After a few minutes, confirm that the status changes to "Running".

Verify the migration job

In this section, you confirm that Database Migration Service used the migration job to migrate data from your source database instance to the destination AlloyDB database instance.

1. Go to the AlloyDB Clusters page in the Google Cloud console.

   Go to the AlloyDB Clusters page

2. Click the read replica entry of your migration job.
3. Click the Activate Cloud Shell icon that appears in the upper-right region of the page.
4. At the Cloud Shell prompt, press Enter.
5. Optional: If an Authorize Cloud Shell dialog box appears, then click Authorize.
6. At the Enter password prompt, enter the password that you either provided or that Database Migration Service generated for you in Define and create the destination AlloyDB instance.

7. At the postgres prompt, enter \list to list the databases and verify that you see your source database instance.
8. At the postgres prompt, enter \connect SOURCE_DB_NAME because you want to see the tables associated with this database instance. The name of the prompt changes from postgres to SOURCE_DB_NAME.
9. At the SOURCE_DB_NAME prompt, enter \dt to see the tables of this instance.
10. At the SOURCE_DB_NAME prompt, enter GRANT alloydbexternalsync to USER; because you want to give this user permission to access the data in the tables of this instance.

    Replace USER with the name of the user you used to connect to the destination database instance.

11. At the SOURCE_DB_NAME prompt, enter SELECT * from TABLE_NAME; to see the information that's replicated from a table in your source database instance.
12. Verify that you see the correct information in the table.

This confirms that Database Migration Service migrated the data.

Promote the migration job

You're ready to promote the migration job. As a result, the destination AlloyDB database instance replaces the source database instance as the primary database.

1. Return to the Migration jobs page.

2. Click the migration job that you want to promote. The Migration job details page appears.

3. Wait for the replication delay to trend toward zero.

4. Stop all writes to the source database.

   You must stop all writes to the source database because, by promoting the migration job, the destination AlloyDB database will become your primary database.

5. Wait until the replication delay is at zero.

6. Click the migration job that you want to promote. This job should have a status of "Running".

7. Click PROMOTE to promote the migration job.

8. Click PROMOTE again in the subsequent dialog box.

9. Verify that the migration job has a status of "Promote in progress". After a few minutes, confirm that the status changes to "Completed".

Your new AlloyDB database instance is ready to use.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

1. Use the Google Cloud console to delete your migration job, connection profile, AlloyDB cluster, and project if you don't need them.

What's next

• Read more about how to manage connection profiles.
• Read more about migration job statuses.
• Read more about migration job metrics.

Send feedback