AnalyticDB:Synchronize self-managed PostgreSQL to AnalyticDB for PostgreSQL

Usage notes

• To synchronize data from multiple databases, you must create a separate data synchronization task for each database.

• If you create a new table in the source database during data synchronization and want to synchronize it, run the following command on the table to maintain data consistency:

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

• To ensure that the task runs as expected, primary/secondary switchover is supported only for ApsaraDB RDS for PostgreSQL 11 instances. You must also set the rds_failover_slot_mode parameter to sync. For more information, see Logical Replication Slot Failover.

  Warning

  If you perform a primary/secondary switchover on a self-managed PostgreSQL database or an ApsaraDB RDS for PostgreSQL instance of a different version, the data synchronization task fails.

• If long-running transactions exist in the source database and the task includes incremental data synchronization, write-ahead logging (WAL) records generated before those transactions are committed may accumulate and fill the disk space of the source database.

Billing

Limitations

• Schema synchronization is not supported. DTS does not synchronize the definitions of objects, such as table schemas, from the source database to the destination database.

• You can synchronize only data tables. Append-optimized (AO) tables are not supported as destination tables.

• Data of the following types cannot be synchronized: BIT, VARBIT, GEOMETRY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT.

• If you run a DDL statement on a synchronized object in the source database, you must manually run the same statement in the destination database and then restart the task.

Supported SQL operations

INSERT, UPDATE, and DELETE.

Before you begin

Create databases, schemas, and tables in the destination AnalyticDB for PostgreSQL instance that match the source objects to synchronize. For more information, see SQL syntax.

Procedure

1. Purchase a data synchronization instance. For more information, see Purchase a DTS instance.

   Note

   When you purchase the instance, set Source Database to PostgreSQL, Destination Database to AnalyticDB for PostgreSQL, and Synchronization Topology to One-way Synchronization.

2. Log on to the Data Transmission Service 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.

3. In the navigation pane on the left, click Data Synchronization.

4. At the top of the Data Synchronization page, select the region of your synchronization instance.

5. Find the purchased data synchronization instance and click Configure Synchronization Channel.

6. Configure the source and destination instances.

   

   Section	Parameter	Description
   N/A	Synchronization task name	DTS automatically generates a task name. You can specify a descriptive name. The name does not need to be unique.
   Source instance details	Instance type	This example uses self-managed database on ECS. Note Other instance types require additional preparations. Preparation overview. The following steps use self-managed database on ECS.
   	Instance region	The region that you selected when you purchased the synchronization instance. This value cannot be changed.
   	ECS instance ID	Select the ID of the ECS instance that hosts your self-managed PostgreSQL database.
   	Database type	This parameter is fixed to PostgreSQL.
   	Port	The service port of the self-managed PostgreSQL database.
   	Database name	The name of the database that contains the tables to synchronize.
   	Database account	The account of the self-managed PostgreSQL database. Note The database account must have ownership permissions on the schema.
   	Database password	The password of the database account.
   Destination instance details	Instance type	Fixed to AnalyticDB for PostgreSQL. No configuration is required.
   	Instance region	The region that you selected when you purchased the data synchronization instance. This value cannot be changed.
   	Instance ID	Select the ID of the destination AnalyticDB for PostgreSQL instance.
   	Database name	The name of the destination database.
   	Database account	The initial account of the AnalyticDB for PostgreSQL instance. For more information, see Create and manage users. Note You can also use an account with RDS_SUPERUSER permissions. Manage user permissions.
   	Database password	The password of the database account.

7. In the lower-right corner of the page, click Set Whitelist and Next.

   If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the CIDR blocks of DTS servers to the IP address whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure that the ECS instance can access the database. If the self-managed database is hosted on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database that is deployed in a data center or provided by a third-party cloud service provider, you must manually add the CIDR blocks of DTS servers to the IP address whitelist of the database to allow DTS to access the database. For more information, see Whitelist DTS server IP addresses.

   Warning

   Adding the public IP address blocks of the DTS service, either automatically or manually, may pose security risks. Using this product, you acknowledge that you understand and accept the potential security risks and that you must implement basic security measures. These measures include, but are not limited to, strengthening password security, limiting the ports open to each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. Alternatively, you can connect through a private network using a leased line, VPN Gateway, or Smart Access Gateway.

8. Set the synchronization policy and select the objects to synchronize.

   Section	Parameter	Description
   Synchronization Policy	Initialize Synchronization	By default, Initial Full Data Synchronization is selected. After the precheck, DTS synchronizes existing data from the source to the destination instance as a baseline for subsequent incremental synchronization.
   	Processing Mode for Existing Tables	Clear Data Skips the "Destination table is empty" check during the precheck phase. Clears data from the destination tables before full data synchronization. This mode is suitable for production synchronization after testing is complete. Ignore Errors and Proceed Skips the "Destination table is empty" check during the precheck phase. Appends data directly during full data synchronization. This mode is suitable for scenarios where data from multiple tables is aggregated into a single table.
   	Synchronization Operation Type	Select the operation types to synchronize based on your business needs: Note AlterTable is not supported. Insert Update Delete AlterTable
   Select Objects to Synchronize	N/A	In the Source Objects box, select the tables to synchronize and click the icon to move them to the Selected Objects box. Note You can select objects only at the table level. If you need the column names in the destination table to be different from the source table, use the column mapping feature of DTS. For more information, see Set object names in the destination instance.
   Map Object Names	N/A	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns. Important If you use column mapping without synchronizing the entire table, or if the source and destination table schemas do not match, data in columns that exist only in the source table is lost on the destination side.
   Connection Retry Duration	N/A	If DTS cannot connect to the source or destination instance, it retries for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the source or destination instance within the specified duration, the synchronization task automatically resumes. Otherwise, the task fails. Note You are billed for task run time during connection retries. Customize the retry duration based on your business needs, or release the DTS instance as soon as the source and destination instances are released.

9. After you complete the preceding configurations, click Precheck and Start in the lower-right corner of the page.

   Note

   • Before the synchronization task is officially started, a precheck is performed. The synchronization task can only be started after the precheck passes.

   • If the precheck fails, click the icon next to a specific check item to view the failure details.

     • Follow the prompt to fix the issue and run the precheck again.

     • If you do not need to fix the warning check items, you can also select Ignore, Ignore Warning Items and Rerun Precheck to skip the warning check items and run the precheck again.

10. After Precheck Passed is displayed in the Precheck dialog box, close the dialog box. The synchronization task starts automatically.
11. Wait until the synchronization channel is initialized and the task enters the Synchronizing state.
    The Data Synchronization page displays the status of data synchronization jobs.