Use quick-start migrations to migrate to AlloyDB for PostgreSQL

MySQL   |  PostgreSQL   |  PostgreSQL to AlloyDB

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

This page describes how to perform quick-start migrations for homogeneous PostgreSQL migrations to AlloyDB for PostgreSQL.

Overview

Quick-start migrations are a lightweight continuous migration flow for homogeneous PostgreSQL scenarios. With quick-start migrations, Database Migration Service can automatically set up everything you need to migrate your databases: networking components (network attachments, subnets, private connectivity configurations), connection profiles, and migration jobs.

With quick-start migrations, you can move your data to an existing AlloyDB for PostgreSQL cluster, or create a new one as you configure the migration. Quick-start migrations are best suited for:

• Basic migrations, where you don't need to precise control over data dump parallelism settings.
• Migrating your database from one Google Cloud project to another. When you configure a quick-start migration, you can create your destination cluster in a different project than your source and set Database Migration Service to create the network attachment and subnet in the Virtual Private Cloud (VPC) network of another project.

• Migrating from sources that have a private IP address in your Google Cloud Virtual Private Cloud (VPC) network, such as self-managed databases on Compute Engine, or Cloud SQL for PostgreSQL instances enabled for private networking.

  Sources hosted outside Google Cloud might need additional network components (such as a Cloud VPN connection) so that they can be reached at a private IP address within your VPC network.

• The only supported method for database connectivity is Private Service Connect interfaces with Database Migration Service private connectivity configurations. Your source database needs to have a private IP assigned in the VPC network. Other homogeneous source connectivity methods (such as public IP allowlists, reverse SSH tunnels, or VPC peering) aren't supported for quick-start migrations.

To learn more about quick-start migrations, see Quick-start migrations overview in the main Database Migration Service documentation.

Before you begin

1. Verify if quick-start migrations can fully support your scenario. See Quick-start migration limitations.
Sign in to your Google Account.

If you don't already have one, sign up for a new account.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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

Verify that billing is enabled for your Google Cloud project.

2. Enable the Database Migration Service, Compute Engine, Network Connectivity Center, and AlloyDB for PostgreSQL Admin APIs.

   Enable the APIs

Required roles

To get the permissions that you need to run quick-start migrations to AlloyDB for PostgreSQL with Database Migration Service, ask your administrator to grant the required Identity and Access Management (IAM) roles for the accounts involved in the migration in the relevant projects. For more information about granting roles, see Manage access in the IAM documentation.

Important: You must assign the roles directly to these accounts. Roles inherited through Google group membership aren't supported for quick-start migrations.

Roles and permissions in the source project

Grant the following roles to specific accounts on the Google Cloud project where your source database resides:

• For the user account that performs the migration: Compute Network Admin role (roles/compute.networkAdmin)
• For the Database Migration Service service account of the destination project: Compute Network Viewer (roles/compute.networkViewer)

  The Database Migration Service service account is created for when you enable the Database Migration Service API. The email address associated with this account is generated automatically and can't be changed. This email address uses the following format:

  [e-mail]

These predefined roles contain the permissions required to set up connectivity on your source database project as part of a quick-start migration with Database Migration Service. To see the exact permissions that are required, expand the Required permissions (source) section:

Required permissions (source)

The following permissions are required to perform homogeneous quick-start migrations with Database Migration Service:

For the user account that performs the migration:

• compute.networks.*
• compute.subnetworks.*
• compute.networkAttachments.*

For the Database Migration Service service account of the destination project: compute.networkAttachments.get.

Roles and permissions in the destination project

Grant the following roles to the user account that performs the migration on the Google Cloud project where your destination database resides:

• Database Migration Admin (roles/datamigration.admin)
• AlloyDB Admin (roles/alloydb.admin)

These predefined roles contain the permissions required to set up the required migration components on your destination database project as part of a quick-start migration with Database Migration Service. To see the exact permissions that are required, expand the Required permissions (destination) section:

Required permissions (destination)

The following permissions are required to perform homogeneous quick-start migrations with Database Migration Service:

• datamigration.*
• alloydb.clusters.create
• alloydb.clusters.get
• alloydb.clusters.list
• alloydb.clusters.update
• alloydb.clusters.delete
• alloydb.instances.create
• alloydb.instances.get
• alloydb.instances.list
• alloydb.instances.update
• alloydb.instances.delete
• alloydb.instances.executeSql
• alloydb.operations.get
• alloydb.users.list
• alloydb.users.get
• alloydb.users.create
• alloydb.users.update
• alloydb.users.delete

Configure your source database

To configure your source PostgreSQL database, do the following:

1. Create a dedicated migration user account in your instance.
   • For Cloud SQL for PostgreSQL sources, see Create a user in the Cloud SQL documentation.
   • For other sources, see your database provider documentation or create and manage users in the PostgreSQL documentation.
2. Assign the necessary privileges to the dedicated migration user account. Run the following commands for each database you want to migrate:

   -- Grant the REPLICATION attribute
   ALTER ROLE MIGRATION_USER REPLICATION;
   
   -- Grant database-level permissions.
   -- Repeat for each database you want to migrate.
   GRANT CONNECT, CREATE ON DATABASE DATABASE_NAME TO MIGRATION_USER;
   
   -- Grant schema-level usage.
   -- Repeat for each schema in each database you want to migrate.
   GRANT USAGE ON SCHEMA SCHEMA_NAME TO MIGRATION_USER;

   Replace the following:

   • MIGRATION_USER with the name of the migration user account.
   • DATABASE_NAME with the name of the database you want to migrate.
   • SCHEMA_NAME with the name of the schema in the database you want to migrate.
3. The migration user account needs to have ownership access to the tables you migrate. To grant this level of access, do one of the following:
   • Assign the SUPERUSER PostgreSQL role to the migration account.
     • For Cloud SQL for PostgreSQL sources, assign the cloudsqlsuperuser role.
     • For other sources, assign the SUPERUSER role or see your database provider documentation for an equivalent set of permissions.
   • Add the migration user account to the user group that owns the tables. Run the following command:

     -- Grant table ownership.
     GRANT TABLE_OWNER_GROUP_NAME TO MIGRATION_USER;

     Replace the following:

     • TABLE_OWNER_GROUP_NAME with the name of the user group that owns each table that you want to migrate.
     • MIGRATION_USER with the name of the migration user account.
4. For tables that don't have primary keys: Database Migration Service doesn't replicate UPDATE or DELETE operations for tables without primary keys during the Change Data Capture (CDC) phase. If you want to include such operations in your replication, modify tables without primary keys with REPLICA IDENTITY:

   ALTER TABLE TABLE_NAME REPLICA IDENTITY FULL;
   ALTER TABLE TABLE_NAME REPLICA IDENTITY USING INDEX INDEX_NAME;

   Replace the following:

   • TABLE_NAME is the name of the table that doesn't have primary keys.
   • INDEX_NAME is the unique index that can track rows from tables without primary keys.
5. Configure the replication settings using database flags.

   Self-managed sources

   Saving changes to database flags requires a full database restart. The following examples use SQL queries for changing flag values. If you can't run SQL queries directly, refer to your provider documentation for steps about changing these flags.

   1. Set the wal_level parameter to logical. Run the following command:

      ALTER SYSTEM SET wal_level = 'logical';

   2. Set the wal_sender_timeout parameter to 0. This value disables the timeout mechanism that's used to terminate inactive replication connections. Run the following command:

      ALTER SYSTEM SET wal_sender_timeout = 0;
          

   3. Configure the maximum number of replication slots with the max_replication_slots parameter. This parameter must be set to at least the number of databases you're migrating per migration job, plus some reserves for table synchronization.

      For example, if you're migrating 5 databases and there are 2 migration jobs created for the source instance, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you.

      To set this parameter, run the following command:

      ALTER SYSTEM SET max_replication_slots = NUMBER_OF_SLOTS;
          

      Where NUMBER_OF_SLOTS represents the maximum number of replication slots.
   4. Configure the max_wal_senders parameter to at least the same value as max_replication_slots, plus the number of senders already used on your instance.

      For example, if the max_replication_slots parameter is set to 10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.

      To set this parameter, run the following command:

      ALTER SYSTEM SET max_wal_senders = NUMBER_OF_SENDERS;
          

      Where NUMBER_OF_SENDERS represents the number of WAL sender processes running simultaneously.
   5. Set the max_worker_processes to at least the number of databases that you want to migrate, plus the number of max_worker_processes already used on your instance. To set this parameter, run the following command:

      ALTER SYSTEM SET max_worker_processes = NUMBER_OF_PROCESSES;
          

      Where NUMBER_OF_PROCESSES represents the number of databases that will be migrated.

   Cloud SQL for PostgreSQL sources

   For Cloud SQL sources, you configure database flags in the Google Cloud console. You have to restart your instance after modifying database flags for the setting to take effect. For more information on setting database flags in Cloud SQL, see Configure database flags in the Cloud SQL documentation.

   1. Set the cloudsql.logical_decoding flag to on.
   2. Set the wal_sender_timeout parameter to 0. This value disables the timeout mechanism that's used to terminate inactive replication connections.
   3. Configure the maximum number of replication slots with the max_replication_slots parameter. This parameter must be set to at least the number of databases you're migrating per migration job, plus some reserves for table synchronization.

      For example, if you're migrating 5 databases and there are 2 migration jobs created for the source instance, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you.

   4. Configure the max_wal_senders parameter to at least the same value as max_replication_slots, plus the number of senders already used on your instance.

      For example, if the max_replication_slots parameter is set to 10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.

   5. Set the max_worker_processes to at least the number of databases that you want to migrate, plus the number of max_worker_processes already used on your instance.

Create and run a quick-start migration

To create and run a quick-start migration, do the following:

1. In the Google Cloud console, go to the Get started page.

   Go to Get started

2. In the Source engine menu, select PostgreSQL.
3. In the Destination engine menu, select AlloyDB for PostgreSQL.

   The Introducing quick-start PostgreSQL migration section appears.

4. In the Introducing quick-start PostgreSQL migration section, click Start migration.

   The Migrate to AlloyDB for PostgreSQL page opens.

5. In the Configure migration section, do the following:
   1. From the Destination region menu, select the region of your destination AlloyDB for PostgreSQL cluster.
   2. In the Migration prefix box, enter a human-friendly string that will be added to the names of all migration entities created for the quick-start migration: connection profiles, private connectivity configuration, network attachment and its subnet, and migration job.
   3. From the Configuration type menu, select one of the following:
      1. Existing connectivity configuration: Select this option if you already have a network attachment and a private connectivity configuration that uses Private Service Connect interfaces method. This option is best used if you previously used quick-start migrations and want to re-use the same networking resources.
      2. New connectivity configuration: Select this option to create a new network attachment and the network attachment subnet in the source database VPC network. The private connectivity configuration will be created in the same project as your destination cluster.
   Click Continue.
6. In the Connect source section, do the following:
   1. Enter a Hostname or the private IP address of your source database. The source database address must be reachable from your source VPC network.
   2. Enter the Port that's used to access the host. The default PostgreSQL port is 5432.
   3. Enter the username and password for the dedicated migration account in your source database.
   4. From the Encryption type menu, select one of the following:
      • None: If your source database doesn't require SSL/TLS-encrypted connections.
      • Required: If your source database requires SSL/TLS-encrypted connections. This option doesn't require any certificate verification.
   5. In the Databases to migrate menu, click Customize. Use the side panel to select only the databases you want to migrate to AlloyDB for PostgreSQL.
   Click Continue.
7. In the Configure destination section, you can create a new destination cluster, or select an existing one.

   New clusters

   If you want to create a new destination cluster, do the following:

   1. From the Destination instance type menu, select New cluster.

      Database Migration Service uses the default AlloyDB for PostgreSQL configuration for a new cluster. Click Customize to adjust cluster features, such as the machine type, zonal availability, or data protection settings. For more informations on cluster configuration, see Create a cluster and its primary instance in the AlloyDB for PostgreSQL documentation.

   2. In the Password field, enter a password for the default postgresql admin user. Database Migration Service connects as this user to migrate your data.

   Existing clusters

   You can migrate databases to a cluster that already exists. Make sure the destination cluster is enabled for Private Service Connect and doesn't enforce mTLS with AlloyDB connectors. Do the following:

   1. From the Destination instance type menu, select Existing cluster.
   2. From the Existing cluster ID menu, select the cluster identifier.
8. Click Start migration.

   Database Migration Service now creates the migration job and begins the migration process. You can monitor the migration progress, as well as your destination cluster health in AlloyDB for PostgreSQL. For more information, see View instance details in the AlloyDB for PostgreSQL documentation.

Finalize the migration

When you decide to switch your application to the new AlloyDB for PostgreSQL instance, finalize the migration by following these steps:

1. Stop all write operations on your source database. You can switch them to read-only mode to retain operational functionality.
2. Promote the migration job.
3. Optional: Verify migration data for completeness.

What's next

• Learn more about AlloyDB for PostgreSQL.

Send feedback