Migrate from RDS for PostgreSQL to RDS for MySQL

Prerequisites

• An ApsaraDB RDS for PostgreSQL instance is created. Create an ApsaraDB RDS for PostgreSQL instance.

• An ApsaraDB RDS for MySQL instance is created. Quickly create an ApsaraDB RDS for MySQL instance.

• The destination RDS for MySQL instance must have more available storage space than the source RDS for PostgreSQL database uses.

Important notes

• During a full data migration, DTS consumes read and write resources on the source and destination databases, increasing their load. If your databases have poor performance, low specifications, or high workloads (for example, if the source database has many slow SQL queries or tables without primary keys, or if deadlocks occur in the destination database), the increased load can strain your databases or even cause service interruptions. Perform the data migration during off-peak hours, such as when the CPU utilization of both databases is below 30%.

• If a source table lacks a primary key or a unique constraint and contains non-unique data, duplicate data may be created in the destination database.

• Primary/secondary switchover during migration is supported only for ApsaraDB RDS for PostgreSQL 11. Set rds_failover_slot_mode to sync. Logical Replication Slot Failover.
  Warning A primary/secondary switchover on a self-managed PostgreSQL database or other RDS versions interrupts the migration.

• Modifying the endpoint or zone of the source RDS for PostgreSQL instance interrupts the migration.

• Schema migration is not supported. Create the databases and tables in the destination instance before you configure the migration task.

• Each migration task supports only one database. To migrate multiple databases, create a separate task for each.

• DTS automatically attempts to resume failed migration tasks. Stop or release the task before you switch your business to the destination instance. Otherwise, a resumed task may overwrite data in the destination.

• A primary/secondary switchover during migration causes incremental migration to fail. Perform switchovers only after the migration is complete.

• For incremental migration, long-running transactions can cause write-ahead logging (WAL) files to accumulate and exhaust disk space on the source database.

Billing

Migration types

Database account permissions

1. Log on to the DTS console.

   Note

   If you are automatically redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click to return to the classic DTS console.

2. In the left-side navigation pane, click Data Migration.

3. At the top of the Migration Tasks page, select the region of the destination cluster.

4. In the upper-right corner of the page, click Experience the new console.

5. On the Create Task page, configure the migration task settings.

   1. Configure the source and destination databases.

      Category	Setting	Description
      N/A	Task name	DTS generates a name automatically. Specify a descriptive name for easy identification. Uniqueness is not required.
      Source database information	Select an existing database connection template	Use a saved template to populate connection settings.
      	Database type	Select PostgreSQL.
      	Access method	Select Alibaba Cloud Instance.
      	Instance Region	Select the region of the source RDS for PostgreSQL instance.
      	Instance ID	Select the ID of the source RDS for PostgreSQL instance.
      	Database name	Enter the source database name.
      	Database account	Enter the account for the source database.
      	Database password	Enter the password for the source database account.
      	Save connection information as template	Save these settings as a reusable template.
      Destination database information	Database type	Select MySQL.
      	Access method	Select Alibaba Cloud Instance.
      	Instance Region	Select the region of the destination RDS for MySQL instance.
      	RDS instance ID	Select the ID of the destination RDS for MySQL instance.
      	Database account	Enter the account for the destination database.
      	Database password	Enter the password for the destination database account.
      	Encryption	Select Non-encrypted or SSL-encrypted as required. If you select SSL-encrypted, you must enable SSL encryption for the RDS for MySQL instance in advance. .
      	Save connection information as template	Save these settings as a reusable template.

   2. After you complete the configurations, click Test Connectivity and Proceed to verify the connection information for the source and destination databases.

      • If the test passes, a Passed message is displayed.

      • If the test fails with a Failed message, click the DiagnoseFailed button that appears and modify the database settings based on the results.

   3. Configure the task objects and advanced settings.

      Category	Setting	Description
      Basic settings	Task steps	We recommend selecting both Full Data Migration and Incremental Data Migration to ensure data consistency. If you select only Full Data Migration: If data is updated in the source RDS for PostgreSQL instance during the full migration, the updated data might not be migrated to the destination RDS for MySQL instance. If the destination RDS for MySQL instance has fewer than 2 CPU cores, disable slow query logging. Improve migration performance by disabling slow query logs for an ApsaraDB RDS for MySQL instance. If you select only Incremental Data Migration: DTS migrates only the incremental data generated in the source RDS for PostgreSQL instance after the task starts. Triggers are not synchronized during incremental migration. Configure a synchronization or migration task when the source database contains triggers.
      	Processing mode for existing tables in destination	Precheck and Report Errors: Checks whether the destination database is empty. If it is empty, the precheck passes. If not, an error is reported during the precheck, and the data migration task will not start. Ignore Errors and Proceed: Skips the check for an empty destination database. Warning If you select Ignore Errors and Proceed, data in the source database will overwrite records with the same primary key in the destination database. Use this option with caution.
      	Objects to synchronize	In the Source Objects box, select the objects that you want to migrate, and then click Note You can select objects at the database, table, or column level. By default, object names remain the same in the destination. To rename objects, use the Map object names feature. If you use object name mapping, the migration of other objects that depend on a renamed object may fail.
      Advanced settings	Configure alerts	Enable or disable alert reporting as needed. No: Alerts will not be reported. Yes: Alerts will be reported.
      	Retry duration for unsuccessful connections to source or destination	Valid values: 10 to 1440. A minimum of 30 minutes is recommended. When multiple DTS tasks share the same source or destination, the shortest retry duration among them takes effect.

   4. After you complete the configurations, click Next: Save Task Settings and Precheck.

   5. Before the migration task starts, DTS performs a Precheck. The migration begins only after the task passes this precheck. If it fails, click View Details for the failed item, fix the issue, and run the precheck again.

   6. After the precheck is passed, click Next: Purchase Instance.

   7. On the Purchase page, select a Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.

   8. Click Buy and Start to start the migration task.

      • Full data migration

        Do not manually stop the task, as this may result in incomplete data. The task automatically stops when it is complete.

      • Incremental data migration

        This task does not stop automatically and must be stopped manually.

        1. Wait until the task progress changes to Incremental Data Migration and the status shows Undelayed. Then, stop write operations on the source database for a few minutes. The Incremental Data Migration status may then show a delay.

        2. Wait until the Incremental Data Migration status changes back to Undelayed, and then manually stop the task.

      Note

      If you no longer need the migration task, select the checkbox

6. To switch your business from the ApsaraDB RDS for PostgreSQL instance to the ApsaraDB RDS for MySQL instance, see Cutover procedure.