Migrate incremental data from self-managed PostgreSQL to ApsaraDB RDS for PostgreSQL

Usage notes

• Full data migration increases load on both source and destination databases, which may degrade performance or cause unavailability—especially with low-spec instances, slow SQL queries, missing primary keys, or deadlocks. Evaluate database performance first and migrate during off-peak hours (CPU utilization below 30%).

• If a source table lacks a primary key or unique constraint, duplicate data may occur in the destination.

• During incremental data migration, if you select a schema as the migration object and then create a new table or rebuild a table by using RENAME, run ALTER TABLE schema.table REPLICA IDENTITY FULL; before writing data to the table.
  Note Replace schema and table with the actual schema and table names.
• DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to measure incremental migration latency.
• DTS creates a logical replication slot prefixed with dts_sync_ in the source database. DTS cleans up historical replication slots every 90 minutes to prevent disk space consumption.
  Note When a migration task is released or fails, DTS automatically cleans up the replication slot. If a primary/secondary switchover occurs on the RDS instance, manually clean up the replication slot on the secondary instance.
  You can run SELECT * FROM pg_replication_slots; to view replication slots. A slot with slot_name starting with dts_sync_, slot_type of logical, and active status of true is in use.
• 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.
• DTS automatically retries failed tasks. Stop or release the task before switching applications to the destination instance to prevent source data from overwriting destination data.
• Long-running transactions in the source database can prevent WAL from being cleared, causing WAL files to accumulate and disk space to run out.

Limitations

• Each migration task migrates only one database. To migrate multiple databases, create a separate task for each.
• Database names cannot contain hyphens (-). Example: dts-testdata.
• If a primary/secondary switchover occurs on the source database during migration, incremental data migration cannot resume.
• Replication latency can cause data inconsistency between primary and secondary nodes. Always use the primary node as the data source.
  Note Perform migration during off-peak hours. You can adjust the migration rate based on the source database load. Modify the transfer rate of full data migration.
• The BIT data type is not supported during incremental data migration.
• After switching workloads to the destination database, sequence values do not start from the maximum value in the source database. Before cutover, query the maximum sequence value in the source database and set it as the initial value in the destination database.
• DTS validates data content but does not validate metadata such as sequences. Validate sequence metadata manually.

Billing

Database account permissions

To create a database account and grant permissions, see the following topics:

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

Data migration process

To resolve object dependencies and improve migration success, DTS migrates PostgreSQL schemas and data in the following order.

Prepare for migration

1. Log on to the server where your self-managed PostgreSQL database resides.
2. Modify the postgresql.conf file. Set wal_level to logical.

   # - Settings -
   wal_level = logical                     # minimal, replica, or logical
                                           # (change requires restart)

   Note

   • After modifying the file, run SELECT pg_reload_conf(); or restart the PostgreSQL database for changes to take effect.
   • If you do not need incremental data migration, you can skip this step.
3. Add the CIDR blocks of DTS servers to the pg_hba.conf file of your self-managed PostgreSQL database. Add only the DTS IP address ranges for the region of your destination database. Add DTS server IP addresses to a whitelist.
   Note For details about this file, see the official PostgreSQL documentation on The pg_hba.conf File. Skip this step if the trusted address is already set to 0.0.0.0/0, as shown below.

   # "local" is for Unix domain socket connections only
   local   all             all                                     peer
   # IPv4 local connections:
   host    all             all             0.0.0.0/0               trust
   # IPv6 local connections:
   host    all             all             ::1/128                 md5

4. Optional: To synchronize DDL operations, create triggers and functions in the source database to capture DDL information. Use triggers and functions to implement incremental DDL migration for PostgreSQL.
   Note You can skip this step if you do not need to synchronize DDL operations.

Procedure

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 Create Data Migration Task.

5. Configure the source and destination database information.

   Category	Setting	Description
   N/A	Task name	DTS auto-generates a task name. Specify a descriptive name for easy identification. The name does not need to be unique.
   Source Database	Instance type	Select the deployment location of the source database. This example uses Self-managed Database with Public IP. Note If your self-managed database uses a different instance type, additional preparation is required. Preparation overview.
   	Instance region	If you select Self-managed Database with Public IP, you do not need to set Instance Region.
   	Database type	Select PostgreSQL.
   	Hostname or IP address	Enter the public IP address of the source PostgreSQL database.
   	Port	Enter the service port of the source PostgreSQL database. This port must be accessible from the public internet.
   	Database name	Enter the name of the database to migrate.
   	Database account	Enter the account for the source PostgreSQL database. For permission requirements, 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 of the destination RDS instance.
   	RDS instance ID	Select the ID of the destination RDS instance.
   	Database name	Enter the destination database name. This can differ from the source database name. Note The database must already exist in the destination RDS instance. If not, create a database.
   	Database account	Enter the account for the destination RDS instance. For permission requirements, 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. Click Set Whitelist and Next in the lower-right corner.
   If the source or destination is an Alibaba Cloud database instance (such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB), DTS automatically adds the CIDR blocks of the DTS servers in the corresponding region to the instance's whitelist. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds the DTS server CIDR blocks to the ECS instance's security group rules. You must also ensure that the self-managed database does not restrict access from the ECS instance. If the database is a cluster deployed across multiple ECS instances, you must manually add the DTS server CIDR blocks for the region to the security group rules of each additional ECS instance. If the source or destination is a self-managed database in a data center or on another cloud platform, you must manually add the DTS server CIDR blocks for the corresponding region to allow access. For a list of DTS server CIDR blocks, see CIDR blocks of DTS servers.

   Warning

   Adding public IP addresses of DTS servers may introduce security risks. By using this product, you acknowledge and accept these risks. Implement basic security measures such as strong passwords, restricted ports, API authentication, regular network segment reviews, or private connections through Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types and migration objects.

   Setting	Description
   Migration types	To perform only a full migration, select Schema Migration and Full Data Migration. To migrate with minimal downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. This example selects all three. Note If you do not select Incremental Data Migration, avoid writing new data to the source database during migration to ensure data consistency.
   Migration objects	In the Available Objects box, select the objects to migrate and click to move them to the Selected Objects box. Note You can select objects at the schema, table, or column level. By default, migrated object names remain the same in the destination. To rename objects, use the object name mapping feature. Object name mapping. Object name mapping may cause dependent objects to fail migration.
   Edit mapped names	To rename migration objects in the destination, use the object name mapping feature. Object name mapping.
   Source and destination connection retry duration	The default retry period is 12 hours. You can customize it. If DTS reconnects within this period, the task resumes automatically. Otherwise, the task fails. Note You are charged for the DTS instance during retry. Customize the retry period based on your needs, or release the DTS instance promptly after the source and destination instances are released.

8. After you complete the configuration, click Precheck and Start in the lower-right corner of the page.

   Note

   • Before the migration task starts, DTS runs a precheck. 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 as prompted and run the precheck again.

     • If you do not need to fix the warning items, you can select Ignore and then click Ignore Warnings and Rerun Precheck to run the precheck again.

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

10. In the Confirm Settings dialog box that appears, select a Instance Class and select the Data Transmission Service (pay-as-you-go) Service Terms checkbox.

11. Click Buy and Start to begin the migration task.

Stop the migration task

• Full data migration

  Do not manually stop a task during full data migration. Otherwise, the system may fail to migrate all data. Wait until the migration task automatically ends.

• Incremental data migration

  The task does not stop automatically. You must stop it manually.

  1. Wait until the task stage changes to Incremental Data Migration and the status shows Undelayed. Then, stop writing data to the source database for a few minutes. The Incremental Data Migration status may temporarily show a delay.
  2. When the Incremental Data Migration status returns to Undelayed, manually stop the migration task.