Data Transmission Service:Full migration from self-managed PostgreSQL to RDS for PostgreSQL

Background

• This topic uses a User-Created Database with Public IP Address to show how to configure a full data migration task. For a migration with minimal downtime, see Incremental migration from self-managed PostgreSQL (10.1 to 13) to ApsaraDB RDS for PostgreSQL and Incremental migration from self-managed PostgreSQL (10.0 or earlier) to ApsaraDB RDS for PostgreSQL.
• You can also perform a full data migration from a self-managed PostgreSQL database to an ApsaraDB RDS for PostgreSQL instance by restoring data from a logical backup. For more information, see Restore data from a logical backup file by using pg_restore.

Prerequisites

• The source self-managed PostgreSQL database is version 9.2, 9.3, 9.4, 9.5, 9.6, 10.x, 11, 12, or 13.
• The available storage space of the destination ApsaraDB RDS for PostgreSQL instance is larger than the storage space occupied by the source self-managed PostgreSQL database.
• The service port of the source self-managed PostgreSQL database is accessible from the internet.

Considerations

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

• Database names cannot contain hyphens (-). Example: dts-testdata.

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

• A data migration task can migrate only a single database. To migrate multiple databases, create a separate task for each 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.
• If a migration task fails, DTS automatically attempts to resume it. Before switching your workloads to the destination instance, stop or release the task to prevent the auto-resumed task from overwriting data in the destination instance with data from the source.
• If the source database has long-running transactions and the instance is used for incremental data migration, the write-ahead logging (WAL) data generated before these transactions commit cannot be cleared. This can exhaust the source database's disk space.

Migration types

• Schema migration

  DTS migrates object schemas to the destination instance. The following object types are supported: tables, triggers, views, sequences, functions, user-defined types, rules, domains, operations, and aggregates.

• Full data migration

  DTS migrates all existing data from specified objects in the source database to the destination database.

Billing

Database account permissions

To create a database account and grant the required permissions:

• Self-managed PostgreSQL database: CREATE USER and GRANT.
• ApsaraDB for PostgreSQL instance: Create an account.

Full data migration process

To prevent migration failures caused by dependencies between objects, DTS migrates PostgreSQL schemas and data in the following order:

1. Migrate the schemas of tables, views, sequences, functions, user-defined types, rules, domains, operations, and aggregates.
   Note DTS does not support plug-ins or functions that are written in the C programming language.
2. Perform a full data migration.
3. Migrate the schemas of triggers and foreign keys.

Before you begin

Create a database and schema in the destination ApsaraDB RDS for PostgreSQL instance for the objects you want to migrate. The destination schema name must be identical to the source schema name. For more information, see Create a database and Manage schemas.

Procedure

1. Log on to the Data Transmission Service (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 navigation pane on the left, click Data Migration.

3. At the top of the Migration Tasks page, select the region where the destination instance is located.

4. In the upper-right corner of the page, click Create Data Migration Task.

5. Configure the source and destination databases.

   Category	Parameter	Description
   N/A	Task Name	DTS automatically generates a task name. We recommend that you use a descriptive name for easy identification. The name does not need to be unique.
   Source Database	Instance Type	Select the type of the source database based on its deployment location. This topic uses User-Created Database with Public IP Address as an example. Note If the self-managed database uses a different instance type, you must complete additional preparations. For more information, see Overview of preparations.
   	Instance Region	If you select User-Created Database with Public IP Address for Instance Region, you do not need to configure this parameter. Note If you have configured an IP address whitelist for your self-managed PostgreSQL database, click Get DTS IP Segment next to the Instance Region parameter to obtain the IP addresses of the DTS servers. Then, add these IP addresses to the whitelist of your self-managed PostgreSQL database.
   	Database Type	Select PostgreSQL.
   	Hostname or IP Address	Enter the endpoint of the self-managed PostgreSQL database. In this example, enter the public endpoint.
   	Port	Enter the service port of the self-managed PostgreSQL database. The default port is 5432.
   	Database Name	Enter the name of the database to migrate from the self-managed PostgreSQL database.
   	Database Account	Enter the database account for the self-managed PostgreSQL database. For the required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After you enter the source database information, you can click Test Connectivity next to Database Password to verify that the information is correct. If the information is correct, the message Passed is displayed. If the message Failed is displayed, click Diagnose next to the Failed message and adjust the source database information based on the prompts.
   Destination Database	Instance Type	Select RDS Instance.
   	Instance Region	Select the region where the destination RDS instance is located.
   	RDS Instance ID	Select the ID of the destination RDS instance.
   	Database Name	Enter the name of the destination database in the RDS instance. The name can be different from the name of the source database. Note Before you configure the task, you must create the corresponding database and schema in the destination RDS instance. For more information, see Before you begin.
   	Database Account	Enter the database account for the destination RDS instance. For the required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After you enter the destination database information, you can click Test Connectivity after Database Password to verify that the entered information is correct. If the information is correct, a Passed message is displayed. If a Failed message is displayed, click Diagnose after Failed and adjust the destination database information based on the prompts.

6. After completing the configuration, click Set Whitelist and Next in the lower-right corner of the page.

   If the source or destination database is an Alibaba Cloud database instance, such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of the DTS servers in the corresponding region to the instance's IP address whitelist. If the source or destination database is a self-managed database on an ECS instance, DTS automatically adds the IP addresses to the security group rules of the ECS instance. You must also ensure that the self-managed database does not restrict access from the ECS instance. If your database cluster is deployed on multiple ECS instances, you must manually add the IP addresses of the DTS servers in the corresponding region to the security rules of each of the other ECS instances. If the source or destination database is a self-managed database in an on-premises data center or hosted on another cloud, you must manually add the regional DTS server IP addresses to your firewall to allow access. For a list of DTS server IP addresses, see IP addresses of DTS servers.

   Warning

   Adding the public CIDR blocks of DTS servers, whether automatically or manually, may introduce security risks. By using this product, you acknowledge and accept these potential risks. You are responsible for implementing basic security measures, including but not limited to using strong passwords, restricting open ports, using authentication for internal API calls, regularly reviewing and restricting unnecessary network segments, or connecting through private networks such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types and the objects to migrate.

   Parameter	Description
   Migration Types	For a full data migration, select both Schema Migration and Full Data Migration. For a migration with minimal downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. This topic covers full data migration only. Select Schema Migration and Full Data Migration. Note To ensure data consistency, do not write new data to the self-managed PostgreSQL database during the migration.
   Migration Objects	In the Available Objects box, select the objects to migrate, and then click the icon to move them to the Selected Objects box. Note You can select objects at the schema, table, or column level. By default, destination object names match the source. To rename a destination object, use the object name mapping feature. For more information, see Object name mapping. If you use the object name mapping feature, other objects that depend on the renamed object might fail to migrate.
   Edit Mapped Name	To rename a migration object in the destination instance, use the object name mapping feature. For more information, see Object name mapping.
   Retry Duration for Failed Connections	The default retry duration is 12 hours. You can also specify a custom duration. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note DTS charges fees during reconnection attempts. We recommend that you set a retry duration based on your business needs, or release the DTS instance promptly after the source and destination databases are released.

8. After you complete these settings, click Pre-check and Start in the lower-right corner of the page.

   Note

   • DTS performs a precheck before the task starts. The task can start only after it passes the precheck.

   • If the precheck fails, click the icon next to the failed item to view details.

     • Fix the issues based on the details, and then run the precheck again.

     • For warnings that do not require a fix, you can select Ignore or Ignore and Rerun Precheck to run the precheck again.

9. After the task passes the precheck, click Next.

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

11. Click Buy and Start to start the migration task.
    Note Do not manually stop the task, as this can result in incomplete data. The task stops automatically upon completion.
12. Switch your workloads to the destination RDS instance.

Next steps

For security purposes, delete these database accounts from both the source and destination databases after the migration is complete.