Migrate data between PolarDB for PostgreSQL clusters-PolarDB(PolarDB)-阿里云帮助中心

Prerequisites

You have created the source and destination PolarDB for PostgreSQL database clusters. For more information, see Create a database cluster.
You have set the wal_level parameter to logical for the source and destination PolarDB for PostgreSQL clusters. For more information, see Set cluster parameters.
The storage space of the destination database must be greater than the storage space used by the source database.

Precautions

Note

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
During full and incremental synchronization, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. If you run cascade update or delete operations on the source database while the task is running, data inconsistency may occur.

Type	Description
Source database limits (PolarDB for PostgreSQL 11)	In a PolarDB for PostgreSQL cluster, tables to be synchronized from the source database must have primary keys or non-empty unique indexes. If the source database has long-running transactions and the cluster has an incremental synchronization task, the write-ahead log (WAL) before the transaction commit may not be cleared. This can cause the WAL to accumulate and lead to insufficient disk space in the source database. To ensure the synchronization task runs correctly and to prevent logical subscription interruptions from a primary/secondary switchover, enable Logical Replication Slot Failover for the PolarDB for PostgreSQL engine. For more information, see Logical Replication Slot Failover.
Other limits	Initial full data synchronization consumes read and write resources on both the source and destination databases. This increases the database load. Before you synchronize data, evaluate the performance of the source and destination databases. Synchronize data during off-peak hours. Initial full data synchronization runs concurrent INSERT operations. This causes table fragmentation in the destination database. After the initial full data synchronization is complete, the tablespace of the destination cluster is larger than that of the source cluster. During DTS synchronization, do not write data to the destination database using any method other than DTS. Otherwise, data inconsistency between the source and destination databases will occur. If a table is synchronized by both forward and remote sync tasks, and the forward sync task synchronizes both full and incremental data for the table, the remote sync task only synchronizes the incremental data for that table. During synchronization, DTS creates a replication slot with the prefix `dts_sync_` in the source database to replicate data. Using this replication slot, DTS can get incremental logs from the source database from the last 15 minutes. Note When a synchronization task is released or fails, DTS automatically cleans up the replication slot. If a primary/secondary switchover occurs on a self-managed PostgreSQL instance, log on to the secondary database to manually clean it up. DTS validates data content but does not support the validation of metadata such as sequences. You must validate metadata yourself. After you switch your business to the destination, new sequences do not increment from the maximum value of the corresponding sequences in the source database. Before the switch, query the maximum value of the corresponding sequence in the source database. Then, set that value as the initial value for the corresponding sequence in the destination database. Use the following command to query the sequence values in the source database: `do language plpgsql $$ declare nsp name; rel name; val int8; begin for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S' loop execute format($_$select last_value from %I.%I$_$, nsp, rel) into val; raise notice '%', format($_$select setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1); end loop; end; $$;`

Billing

Synchronization type	Link configuration fee
Schema synchronization and full data synchronization	Free of charge.
Incremental data synchronization	This is a paid feature. For more information, see Billing overview.

Supported SQL operations for synchronization

Operation type	SQL operation
DML	INSERT, UPDATE, DELETE
DDL	Important For data synchronization tasks created before September 9, 2022, create a trigger and a function in the source database to capture DDL information before you configure the synchronization task. For more information, see Use triggers and functions to implement incremental DDL migration for PostgreSQL. Data of the BIT type cannot be synchronized during the incremental data synchronization phase. If the source database account is a privileged account, the synchronization task supports the following DDL operations: CREATE TABLE, DROP TABLE ALTER TABLE (including RENAME TABLE, ADD COLUMN, ADD COLUMN DEFAULT, ALTER COLUMN TYPE, DROP COLUMN, ADD CONSTRAINT, ADD CONSTRAINT CHECK, and ALTER COLUMN DROP DEFAULT) TRUNCATE TABLE (The source PolarDB for PostgreSQL database engine must be PostgreSQL 11 or later) CREATE INDEX ON TABLE Important DTS does not synchronize additional information in DDL statements, such as CASCADE or RESTRICT. DTS does not synchronize DDL statements from a session that runs the `SET session_replication_role = replica` command. If multiple SQL statements that are committed in a single transaction in the source database contain both DML and DDL statements, the DDL statements are not synchronized. If multiple SQL statements that are committed in a single transaction in the source database contain DDL statements for objects not selected for synchronization, the DDL statements are not synchronized.

Configuration steps

Navigate to the migration task list page for the destination region using one of the following methods.
From the DTS console
1. Log on to the Data Transmission Service (DTS) console.
2. In the navigation pane on the left, click Data Migration.
3. In the upper-left corner of the page, select the region where the migration instance is located.
From the DMS console

Note
The actual operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode console and Customize the layout and style of the DMS console.
1. Log on to the Data Management (DMS) console.
2. In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Migration.
3. To the right of Data Migration Tasks, select the region where the migration instance is located.

Click Create Task to configure the source and destination databases.

Note

Set Database Type to PolarDB for PostgreSQL for both databases.

Category	Configuration	Description
None	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not need to be unique.
Source Database (PolarDB for PostgreSQL 11)	Select Existing Database Instance	Select whether to use an existing instance as needed. If you use an existing instance, the database information is automatically filled in. If you do not use an existing instance, enter the database information below.
	Database Type	Select PolarDB for PostgreSQL.
	Connection Type	Select Cloud Instance.
	Instance Region	Select the region where the source PolarDB for PostgreSQL cluster resides.
	Across Alibaba Cloud accounts	This example synchronizes data within the same Alibaba Cloud account. Select No.
	Instance ID	Select the ID of the source PolarDB for PostgreSQL cluster.
	Database Name	Enter the name of the source PolarDB for PostgreSQL database.
	Database Account	Enter the database account of the source PolarDB for PostgreSQL instance.
	Database Password	Enter the password for the database account.
Destination Database (PolarDB for PostgreSQL 14)	Select Existing Database Instance	Select whether to use an existing instance as needed. If you use an existing instance, the database information is automatically filled in. If you do not use an existing instance, enter the database information below.
	Database Type	Select PolarDB for PostgreSQL.
	Connection Type	Select Cloud Instance.
	Instance Region	Select the region where the destination PolarDB for PostgreSQL cluster resides.
	Instance ID	Select the ID of the destination PolarDB for PostgreSQL cluster.
	Database Name	Enter the name of the destination PolarDB for PostgreSQL database.
	Database Account	Enter the privileged database account for the destination PolarDB for PostgreSQL database. For information about how to create an account and grant permissions, see Create a database account.
	Database Password	Enter the password for the database account.

After the configuration is complete, click Test Connection and Proceed at the bottom of the page.
The DTS task automatically adds the IP addresses of the DTS servers in the corresponding region to the whitelist of the Alibaba Cloud database instance.

Warning
Automatically or manually adding the public IP address CIDR blocks of DTS servers may create security risks. Using this product, you acknowledge and accept these potential risks. You must implement basic security measures. These measures include, but are not limited to, using strong passwords, limiting open ports for each CIDR block, using authentication for internal API calls, and regularly reviewing and restricting unnecessary CIDR blocks. You can also connect using a private network, such as a leased line, VPN Gateway, or Smart Access Gateway.

Configure task objects and advanced settings.

Configuration	Description
Synchronization Type	Incremental Synchronization is selected by default. You must also select Schema Synchronization and Full Synchronization. After the precheck is complete, DTS performs a full data initialization of the selected objects from the source instance to the destination cluster. This data serves as the baseline for subsequent incremental data synchronization. Note You must select all synchronization types.
Synchronization Topology	One-way Synchronization: Establishes only a one-way synchronization link from the source database to the destination database. This includes links for historical data and incremental data. Two-way Synchronization: Establishes a two-way synchronization link between the source and destination databases. This enables incremental synchronization from the source to the destination and remote sync from the destination back to the source. Note Reverse synchronization supports only data synchronization, not Data Definition Language (DDL) operations.
Processing Mode for Existing Destination Tables	Precheck and Report Error: Checks for tables with the same names in the destination database. If none exist, the check passes. If tables with the same names are found, the precheck reports an error and the task does not start. Note If you cannot easily delete or rename the tables with the same names in the destination database, you can map them to different names in the destination database. For more information, see Map table and column names. Ignore and Continue: Skips the check for tables with the same names in the destination database. Warning If you select Ignore and Continue, data inconsistency may occur, which poses risks to your business. For example: If the schemas are consistent and a record in the destination database has the same primary key or unique key value as a source record: During full synchronization, DTS keeps the record in the destination cluster. The source record is not synchronized to the destination database. During incremental synchronization, the source record overwrites the destination record. If the table schemas are inconsistent, data initialization may fail, only some columns of data may be synchronized, or the synchronization may fail. Proceed with caution.
Case-sensitivity of Object Names in Destination Database	You can configure the case-sensitivity policy for the names of synchronized databases, tables, and columns in the destination instance. By default, DTS Default Policy is selected. You can also choose to make it consistent with the default policies of the source or destination database. For more information, see Case-sensitivity of object names in the destination database.
Source Objects	In the Source Objects box, click the object to synchronize, and then click to move it to the Selected Objects box. Note The granularity for selecting synchronization objects is database, table, and column. If you select tables or columns as synchronization objects, other objects such as views, triggers, and stored procedures are not synchronized to the destination database.
Selected Objects	To change the name of a single synchronization object in the destination instance, right-click the object in the Selected Objects section. For more information, see Map object names. To change the names of multiple sync objects in the destination instance, click Batch Edit in the upper-right corner of the Selected Objects section. For more information, see Map library, table, and column names. Note To select SQL operations to synchronize at the database or table level, right-click the object to synchronize in Selected Objects. In the dialog box that appears, select the required SQL operations. For supported operations, see Supported SQL operations. To set a WHERE clause to filter data, right-click the table to synchronize in Selected Objects. In the dialog box that appears, set the filter condition. For more information, see Set filter conditions.

Click Next: Advanced Settings to configure advanced settings.

Configuration	Description
Data Validation Method	Full validation: Validates all data. Incremental validation: Validates only the incremental data.
Select a dedicated cluster to schedule the task	A DTS dedicated cluster is a cluster of ECS virtual machines of the same specification in a region. It is used to manage and configure DTS migration, synchronization, and tracking tasks. Compared to DTS shared clusters, DTS dedicated clusters offer dedicated resources, better stability, higher performance, and lower costs. For more information, see What is a DTS dedicated cluster?
Configure Alerting	Specifies whether to configure alerting. If the synchronization fails or the latency exceeds the threshold, the alert contact is notified. Do not configure: Does not configure alerting. Configure: Configures alerting. You also need to set the alert threshold and alert contact. For more information, see Configure monitoring and alerting during task configuration.
Retry Time After Source/Destination DB Connection Failure	After the synchronization task starts, if the source or destination database connection fails, DTS reports an error and immediately begins to retry the connection continuously. The default retry duration is 720 minutes. You can also customize the retry time within the range of 10 to 1440 minutes. We recommend setting it to 30 minutes or more. If DTS reconnects to the source and destination databases within the set retry time, the synchronization task automatically resumes. Otherwise, the task fails. Note For multiple DTS instances with the same source or destination, such as DTS instance A and DTS instance B, if you set the network retry time to 30 minutes for A and 60 minutes for B, the shorter time of 30 minutes applies. DTS charges for task runtime during connection retries. We recommend that you customize the retry time based on your business needs, or release the DTS instance as soon as possible after the source and destination database instances are released.
Retry Time After Other Issues in Source/Destination DB	After the synchronization task starts, if a non-connectivity issue occurs in the source or destination database (such as a DDL or DML execution error), DTS reports an error and immediately begins to retry the operation continuously. The default retry duration is 10 minutes. You can also customize the retry time within the range of 1 to 1440 minutes. We recommend setting it to 10 minutes or more. If the related operations succeed within the set retry time, the synchronization task automatically resumes. Otherwise, the task fails. Important The value for Retry Time After Other Issues in Source/Destination DB must be less than the value for Retry Time After Source/Destination DB Connection Failure.
Limit Full Migration Rate	Full migration enables concurrent reads from the source database and concurrent writes to the destination database. This may put some pressure on your databases. If you do not want to put significant load on the databases, you can limit the maximum migration rate.
Limit Incremental Synchronization Rate	Incremental migration enables concurrent reads from the source database and concurrent writes to the destination database. If one database has a high business workload, it may cause significant write pressure on the other. If you do not want to put significant load on the databases, you can limit the maximum migration rate.
Environment Tag	Used to identify the importance of a DTS task. Environment tags do not affect the normal operation of the task.
Configure ETL Feature	Select whether to configure the ETL feature. For more information about ETL, see What is ETL? Yes: Configures the ETL feature. Enter data processing statements in the text box. For more information, see Configure ETL in a DTS migration or synchronization task. No: Does not configure the ETL feature.

Save the task and run a precheck.
- To view the parameters for the API operation that configures this instance, move the pointer over Next: Save Task and Precheck and click Preview OpenAPI Parameters in the tooltip that appears.
- If you do not need to view the parameters, click Next: Save Task and Precheck at the bottom of the page.
  Note
  - Before the synchronization job starts, a precheck is run. The job can start only after it passes the precheck.
  - If the precheck fails, click View Details next to the failed check item. Resolve the issue as prompted and run the precheck again.
  - If a warning is generated during the precheck:
    
    For items that cannot be ignored, click View Details next to the failed check item. Resolve the issue as prompted and run the precheck again.
    
    For items that can be ignored, click Confirm Alert Details, Confirm Shield, OK, and then Rerun Precheck to skip the warning item and run the precheck again. If you choose to ignore a warning item, you risk data inconsistency and other issues.
When the Precheck Success Rate is 100%, click Next: Purchase.

On the Purchase page, select the billing method and link specification for the data synchronization instance. For details, see the following table.

Category	Parameter	Description
Configuration Information	Billing Method	Subscription: Pay when you create the instance. This is suitable for long-term needs and is more cost-effective than pay-as-you-go. The longer the subscription, the greater the discount. Pay-as-you-go: Billed hourly. This is suitable for short-term needs. You can release the instance immediately after use to save costs.
	Resource Group Configuration	The resource group to which the instance belongs. The default is default resource group. For more information, see What is Resource Management?
	Link Specification	DTS provides synchronization specifications with different performance levels. The synchronization link specification affects the synchronization rate. You can choose based on your business scenario. For more information, see Data synchronization link specifications.
	Subscription Duration	In subscription mode, select the duration and quantity for the subscription instance. You can choose 1 to 9 months for a monthly subscription, or 1, 2, 3, or 5 years for a yearly subscription. Note This option appears only when the billing method is Subscription.

After the configuration is complete, read and select Data Transmission Service (Pay-As-You-Go) Service Terms.
Click Purchase and Start. The synchronization task starts. You can view the task progress on the Data Synchronization page.

Execution steps

Execution overview

Precheck: A preparatory step before the data migration or synchronization process begins. It confirms that all necessary conditions are met, such as network connectivity, system permissions, and data format and integrity.
Pre-processing module: In the data processing flow, this module typically pre-processes the data. It mainly involves creating necessary triggers to capture subsequent data changes.
Incremental data ingestion: This module focuses on collecting only the data that has changed or been added since the last ingestion. This helps improve efficiency by avoiding the processing of unchanged data.
Schema migration: During data migration, this involves transferring the data structure from one system or format to another while maintaining consistency. This is usually an initial migration step to set up the data structure in the new environment. Most of the migration work, such as migrating table schemas, sequences, stored procedures and functions, views, and indexes, is completed in this step.
Full migration: This migrates all data from a database or data warehouse to another system.
Schema migration 2: This is a second round of structural adjustments after the initial schema migration. It is used to fine-tune and optimize the data structure or to resolve issues found during the initial migration. It mainly involves creating foreign keys on tables.
Incremental write: After the full migration is complete, this step identifies and migrates new and changed data from the source system to the destination system.
Post-processing module: After data migration or synchronization is complete, this module performs a series of wrap-up tasks, such as data cleanup, index creation, or performance optimization.
Full validation: After data migration, validation is required to ensure data integrity and accuracy. Full validation compares all migrated data to confirm that it has been copied to the new system correctly.
Incremental validation: Similar to full validation, incremental validation focuses on verifying only the incrementally migrated data. It ensures that any changes made to the data since the last validation are accurately captured and migrated.

Execution example

You can go to the Data Synchronization page of the new DTS console to view the execution details.

Precheck.

Note
This step runs automatically. You do not need to take any action.
Front-end module

Note
This step runs automatically. You do not need to take any action.
Incremental data ingestion.

Note
This step runs automatically. You do not need to take any action.
Schema migration.
Most tables, indexes, and sequences are migrated automatically. The tables t1, empty, and t2 are migrated successfully.

Note
If a task fails during schema migration, you can use the schema revision feature to manually correct the migration SQL statements.
Full migration
The data of all tables in the selected scope is fully migrated. You can view the data traffic and transmission speed during the migration on the Data Synchronization page.
During full migration, you can monitor the data volume of each table. For example, for the t1 table, the estimated number of rows to migrate is 835,117, but the actual number of migrated rows is 835,266.
Schema migration 2
The core of this step is to migrate the foreign key constraints of the tables. After the data is successfully migrated, adding foreign keys is an important operation to ensure data consistency across tables.
Incremental write
This step performs a one-way synchronization of traffic from the source database. In this process, all traffic from the source database is subscribed to, forwarded, processed, and applied at the destination.
Post-processing module
Full validation
The full validation process involves comparing the data in the source and destination databases. In this example, 835,266 records in the t1 table of the source database and the t1 table of the destination database are compared. The data is consistent, so the validation is successful.
Incremental validation
After all preceding tasks are complete, the system automatically enters the incremental synchronization and validation phase. In this phase, the system compares the incremental data differences between the two databases.

Cutover execution

After the migration is complete, deploy the destination database to a staging environment and perform comprehensive business testing. The data in the destination database should be fully synchronized with the source database. A slight delay is normal during the data replay process. The testing duration may vary as needed. However, test for at least one week to ensure everything runs correctly before you perform the cutover.

Before the cutover, plan a specific time window, such as 10 hours. During this time, stop the service on the source database. After the destination database completes data replay, update the database connection string in your application and restart the business system. Then, perform necessary basic business tests. If the tests find no issues, you can make the new service available to users.

Keep the old database for a period. You can safely decommission the old database after you confirm that the destination database is completely stable.

References

Changes in PolarDB for PostgreSQL 14 compared with PolarDB for PostgreSQL 11.