Data Transmission Service:Synchronize data from a self-managed PostgreSQL database to a PolarDB for PostgreSQL (Compatible with Oracle) cluster

Source database limits

Other limits

Limitation	Detail
session_replication_role	For full or incremental synchronization tasks with foreign keys, triggers, or event triggers, DTS temporarily sets session_replication_role to replica at the session level. This requires a privileged destination database account. If the account does not have this permission, set session_replication_role to replica manually before starting the task. After the task is released, reset it to origin. During this period, cascade update or delete operations on the source may cause data inconsistency.
SERIAL type fields	If a source table contains SERIAL type fields, the source database automatically creates a sequence for those fields. When configuring Source Objects, if you select Schema Synchronization, also select Sequence or synchronize the entire schema to avoid task failures.
One database per task	A single sync task can synchronize only one database. For multiple databases, create a separate task for each.
Unsupported table types	Synchronization of TimescaleDB extension tables and tables with cross-schema inheritance is not supported.
REPLICA IDENTITY FULL requirement	Run ALTER TABLE schema.table REPLICA IDENTITY FULL; on tables before writing data in any of these cases: when the instance runs for the first time; when a new table is created or rebuilt (using RENAME) during schema-level synchronization; or when you modify the synchronization objects. Run this command during off-peak hours and do not hold table locks while running it. If you skip the related precheck item, DTS runs this command automatically during initialization.
Sequence validation	DTS validates data content but does not validate metadata such as sequences. Validate sequences yourself. After switching traffic to the destination, new sequences do not continue from the maximum source sequence value. Update sequence values in the destination before switching. See Update sequence values in the destination database.
Temporary tables in the source	DTS creates the following temporary tables in the source database: public.dts_pg_class, public.dts_pg_attribute, public.dts_pg_type, public.dts_pg_enum, public.dts_postgres_heartbeat, public.dts_ddl_command, public.dts_args_session, and public.aliyun_dts_instance. DTS also adds a heartbeat table named dts_postgres_heartbeat. Do not delete these tables during synchronization. They are removed automatically when the DTS instance is released.
Replication slot	DTS creates a replication slot with the prefix dts_sync_ in the source database. This slot retains incremental logs for the past 15 minutes. DTS attempts to clean up the slot when the sync task fails or the instance is released. If you change the source database account password or remove DTS IP addresses from the source during synchronization, the slot cannot be cleaned up automatically — clean it up manually to prevent disk exhaustion. If a primary/secondary failover occurs, log on to the secondary database to perform the cleanup.
Source performance	Full data synchronization runs concurrent INSERT operations, which consumes read and write resources on both databases. Perform synchronization during off-peak hours. After initialization, the destination tablespace may be larger than the source due to fragmentation.
Writes to destination during sync	Do not write data from sources other than DTS to the destination database during synchronization. Doing so causes data inconsistency.
Instance recovery	If a DTS instance fails, DTS attempts to recover it within 8 hours. During recovery, DTS may restart the instance or adjust its parameters (DTS instance parameters only — source and destination database parameters are not changed).
Online DDL via DMS	For table-level synchronization with no non-DTS writes to the destination, you can use Data Management (DMS) to perform online DDL operations without locking tables. See Change schemas without locking tables. Do not allow other sources to write to the destination while running DMS online DDL — doing so can cause data loss.

During schema synchronization, DTS synchronizes foreign keys from the source to the destination. During full and incremental synchronization, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. Cascade update or delete operations on the source during this period may cause data inconsistency.

All PostgreSQL versions

1. Log on to the server where the source PostgreSQL database is running.

2. Query the number of currently used replication slots:

   SELECT count(1) FROM pg_replication_slots;

3. Edit postgresql.conf and set the following parameters. max_wal_senders and max_replication_slots must each be greater than the sum of the replication slot count from step 2 and the number of DTS instances you plan to create with this source database. Example postgresql.conf configuration:

   Parameter	Required value
   wal_level	logical
   max_wal_senders	Greater than (current replication slots + DTS instances to create)
   max_replication_slots	Greater than (current replication slots + DTS instances to create)

   wal_level = logical
   max_wal_senders = 10
   max_replication_slots = 10

   Restart the database after saving the file for the changes to take effect.

4. Add DTS server CIDR blocks to pg_hba.conf. Add only the CIDR blocks for DTS servers in the same region as the destination database. For the full list, see Add DTS server IP addresses to a whitelist. After saving pg_hba.conf, reload the configuration:

   SELECT pg_reload_conf();

   Alternatively, restart the database. Skip this step if pg_hba.conf already allows connections from 0.0.0.0/0. For reference, see The pg_hba.conf file.

   

5. In the destination cluster, create the databases and schemas that correspond to the objects you plan to synchronize.

PostgreSQL 9.4.8 to 10.0 (additional steps)

If your source PostgreSQL version is between 9.4.8 and 10.0, you must also install the ali_decoding extension.

Step 1: Compile and install PostgreSQL from source

1. Log on to the server where the source database is running.

2. Download the PostgreSQL source code from the PostgreSQL official website matching your database version.

3. Compile and install PostgreSQL:

   Important

   The OS version and GNU Compiler Collection (GCC) version must be compatible. If sudo ./configure fails with a readline error, run sudo ./configure --without-readline instead. If you use a different PostgreSQL installation method, compile ali_decoding in a test environment with the same OS and GCC version.

   sudo ./configure
   sudo make
   sudo make install

Step 2: Install the ali_decoding extension

1. Download ali_decoding.

2. Copy the ali_decoding directory into the contrib directory of the PostgreSQL installation from Step 1.

   

3. Replace the contents of the Makefile in the ali_decoding directory with the following:

   # contrib/ali_decoding/Makefile
   MODULE_big = ali_decoding
   MODULES = ali_decoding
   OBJS    = ali_decoding.o
   
   DATA = ali_decoding--0.0.1.sql ali_decoding--unpackaged--0.0.1.sql
   
   EXTENSION = ali_decoding
   
   NAME = ali_decoding
   
   #subdir = contrib/ali_decoding
   #top_builddir = ../..
   #include $(top_builddir)/src/Makefile.global
   #include $(top_srcdir)/contrib/contrib-global.mk
   
   #PG_CONFIG = /usr/pgsql-9.6/bin/pg_config
   #pgsql_lib_dir := $(shell $(PG_CONFIG) --libdir)
   #PGXS := $(shell $(PG_CONFIG) --pgxs)
   #include $(PGXS)
   
   # Run the following commands to install the source code.
   ifdef USE_PGXS
   PG_CONFIG = pg_config
   PGXS := $(shell $(PG_CONFIG) --pgxs)
   include $(PGXS)
   else
   subdir = contrib/ali_decoding
   top_builddir = ../..
   include $(top_builddir)/src/Makefile.global
   include $(top_srcdir)/contrib/contrib-global.mk
   endif

4. From the ali_decoding directory, compile and install the extension:

   sudo make
   sudo make install

5. Copy the generated files to the specified directories.

   

6. In the destination cluster, create the databases and schemas that correspond to the objects you plan to synchronize.

Step 1: Go to the Data Synchronization page

Use either the DTS console or the DMS console.

Step 2: Create a task

Click Create Task to open the task configuration page.

Step 3: Configure the source and destination databases

Step 4: Test connectivity

Click Test Connectivity and Proceed at the bottom of the page.

Make sure DTS server CIDR blocks are added to the security settings of both the source and destination databases. See Add DTS server IP addresses to a whitelist. If the source or destination is a self-managed database with an access method other than Alibaba Cloud Instance, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 5: Configure synchronization objects

In the Configure Objects step, configure the following:

Click Next: Advanced Settings and configure the following:

Click Next Step: Data Verification to configure data verification. See Configure a data verification task.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

To view the API parameters for this task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before proceeding.

DTS runs a precheck before the task can start. If any item fails:

• Click View Details next to the failed item, fix the issue, then click Precheck Again.

• If an alert item can be safely ignored, click Confirm Alert Details > View Details > Ignore > OK, then click Precheck Again. Ignoring alert items may cause data inconsistency.

Step 7: Purchase and start the instance

1. Wait for the Success Rate to reach 100%, then click Next: Purchase Instance.

2. On the purchase page, configure the following:

   Section	Parameter	Description
   New Instance Class	Billing Method	Subscription: Pay upfront for a fixed term. More cost-effective for long-term use. Pay-as-you-go: Billed hourly. Suitable for short-term use. Release the instance when no longer needed to reduce costs.
   	Resource Group Settings	The resource group for the sync instance. Default: default resource group. See What is Resource Management?.
   	Instance Class	DTS provides instance classes with different synchronization speeds. Select based on your requirements. See Instance classes of data synchronization instances.
   	Subscription Duration	Available when Subscription is selected. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years.

3. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start, then click OK in the dialog box.

The task appears in the task list. Monitor its progress there.