Use quick-start migrations with Database Migration Service

MySQL | PostgreSQL | SQL Server

This page describes how to perform quick-start migrations for homogeneous PostgreSQL migrations to Cloud SQL 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 Cloud SQL for PostgreSQL instance, or create a new one as you configure the migration. Quick-start migrations are best suited for:

Basic migrations, where you don't need 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 instance 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 in the Database Migration Service documentation.

Before you begin

Verify if quick-start migrations can fully support your scenario. See Quick-start migration limitations in the Database Migration Service documentation.

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.

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.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Database Migration Service, Compute Engine, Network Connectivity Center, and Cloud SQL Admin APIs.
Enable the APIs

Required roles

To get the permissions that you need to run quick-start migrations to Cloud SQL 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.

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 (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:
```
service-DESTINATION_PROJECT_NUMBER@gcp-sa-datamigration.iam.gserviceaccount.com
```

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)
Cloud SQL Admin (roles/cloudsql.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.*
cloudsql.instances.create
cloudsql.instances.delete
cloudsql.instances.get
cloudsql.instances.list
cloudsql.instances.update
cloudsql.instances.connect
cloudsql.instances.executeSql
cloudsql.instances.export
cloudsql.instances.import
cloudsql.instances.login
cloudsql.operations.get
cloudsql.users.get
cloudsql.users.list
cloudsql.users.create
cloudsql.users.update
cloudsql.users.delete

Configure your source database

To configure your source PostgreSQL database, do the following:

Create a dedicated migration user account in your instance.
- For Cloud SQL for PostgreSQL sources, see Create a user in the Cloud SQL for PostgreSQL documentation.
- For other sources, see your database provider documentation or create and manage users in the PostgreSQL documentation.

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.

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.
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.
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

When you use quick-start migrations, you can create a new Cloud SQL instance and migrate your databases there, or migrate databases to a instance that already exists.

Quick-start migrations for new instances

To create a new Cloud SQL for PostgreSQL instance and immediately migrate your databases there, follow these steps:

Go to the Instances page.
Go to Instances
Click Create instance > From an existing database.
In the Migrate existing database window, select Quick-start PostgreSQL migration and click Continue.
The Database Migration Service quick-start migration form opens.
In the Configure migration section, do the following:
1. From the Destination region menu, select the region of your destination Cloud SQL for PostgreSQL instance.
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 instance.
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 Cloud SQL for PostgreSQL.
In the Configure destination section, do the following:
1. From the Destination instance type menu, select New instance.
  Database Migration Service uses the default Cloud SQL for PostgreSQL configuration for a new instance. Click Customize to adjust instance features, such as the machine type, zonal availability, or data protection settings. For more informations on instance configuration, see Create instances.
Click Start migration.
Database Migration Service now creates the migration job and begins the migration process. You can monitor the migration progress in Database Migration Service migration job details. For more information, see Manage migration jobs in the Database Migration Service documentation.

Quick-start migrations for existing instances

To migrate databases to an existing Cloud SQL instance, follow these steps:

Go to the Instances page.
Go to Instances
Select the Cloud SQL instance where you want to migrate your databases.
On the instance details page, click Migrate database.
The Database Migration Service quick-start migration form opens.
In the Configure migration section, do the following:
1. From the Destination region menu, select the region of your destination Cloud SQL for PostgreSQL instance.
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 in the source database VPC network. 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, network attachment subnet, and a private connectivity configuration in the source database VPC network.
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 Cloud SQL for PostgreSQL.
Click Start migration.
Database Migration Service now creates the migration job and begins the migration process. You can monitor the migration progress in Database Migration Service migration job details. For more information, see Manage migration jobs in the Database Migration Service documentation.

Finalize the migration

When you decide to switch your application to the new Cloud SQL instance, finalize the migration by following these steps:

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

What's next

Learn about other migration flows and full Database Migration Service capabilities.