Category | Description |
Supported versions | 1. Source database Self-built databases: PostgreSQL 10–17. Databases from third-party cloud vendors (Alibaba Cloud RDS for PostgreSQL/AWS RDS for PostgreSQL/Huawei Cloud RDS for PostgreSQL) 10–17. TencentDB for PostgreSQL 10–17 (migration between databases under the same root account/migration between databases under different root accounts). 2. Target database TencentDB for PostgreSQL 10–17. |
Impact on the source database | 1. When Data Transfer Service (DTS) is used to perform full data synchronization, it occupies certain instance resources of the source database, which may cause the load of the source database instance to increase and add pressure to the database. If your database configuration is low, it is recommended to perform this operation during off-peak hours. 2. A lock-free method is adopted by default, and no global lock is added to the source database during synchronization. 3. During data synchronization, DTS uses the account for executing the synchronization task to create postgres.public.identify and {database}.__tencentdb__sync_*.* in the source database to record the task status and configuration information. The target instance uses the {database}.__tencentdb__ schema to record the synchronization status. postgres.public.identify, {database}.__tencentdb__sync_*.*, and {database}.__tencentdb__ schema occupy minimal storage space, approximately 0.01% to 0.1% of the storage space of the source database. Therefore, they have no impact on the performance of the source database and do not consume its resources. 4. Since synchronization tasks rely on the max_replication_slots parameter (each synchronization task configured for a database occupies one replication_slot), you can use the SQL statement select * from pg_replication_slots to view usage. If the sum of the number of configured synchronization databases and replication_slots is greater than max_replication_slots, creating a synchronization task will fail. The system will release replication_slots only after the user actively terminates the task (the system will not release replication_slots unless the failed task ends). |
Synchronization object | 1. Unsupported synchronization objects: ROLE, PROCEDURE, TRIGGER, FUNCTION, RULE, and so on. 2. Synchronization of public objects and permission changes is not supported. 3. During the full synchronization phase, structure initialization does not support foreign key import. Foreign key constraints will be automatically removed before data import. 4. Data synchronization does not support the FOREIGN TABLE type. During the full synchronization phase, export and import do not support TEMPORARY TABLE. During the incremental synchronization phase, only the synchronization of the table structure for UNLOGGED TABLE is supported. 5. During the incremental synchronization phase, updates to SEQUENCE objects in the source database are synchronized to the target database, but refreshing the last_value of SEQUENCE is not supported. Users may need to verify the synchronization results and manually modify the value in the target database. 6. After objects from the source database are synchronized to the target database, their owner will be changed to the account of the target database. For example, assume the owner of table 1 is A, and account B is used for synchronization in the target database. After table 1 is written to the target database, its owner becomes B. 7. Synchronizing the entire source instance is not supported. If a database creation operation (CREATE DATABASE) is performed at the source during the subsequent synchronization task, it is recommended that you manually create the corresponding database object at the target before starting the synchronization task. 8. If the CREATE TABLE or RENAME TABLE operation is performed at the source during a synchronization task, you should select the entire database where the table resides when selecting synchronization objects during the configuration phase. Otherwise, the newly created or renamed tables will not be synchronized to the target. 9. When synchronization of the full database is selected, there must not be schemas or tables with the same name in the target database. 10. Read-only instances cannot be used as instances in source and target databases. 11. Triggers and stored procedures currently cannot be synchronized during the incremental synchronization phase. 12. You cannot initiate incremental checks on objects other than synchronization objects. 13. Synchronization of temporary tables is currently not supported. |
Data type | 1. Supported data types: Aclitem, Bit, Bool, Box, Bpchar, Bytea, Char, Cid, Cidr, Circle, Date, Float4, Float8, Inet, Int2, Int4, Int8, Interval, JSON, Jsonb, Jsonpath, Line, Lseg, Macaddr, Name, Numeric, Oid, Path, Point, Polygon, Record, Text, Tid, Time, Timestamp, Timestamptz, Unknown, UUID, Varbit, Varchar, XID, Daterange, Int4range, Int8range, Numrange, Tsrange, Tstzrange, Datemultirange, Int4multirange, Int8multirange, and Nummultirange. Tsmultirange, Tstzmultirange, XML, Money, Time with time zone, Tsvector, Tsquery, pg_lsn, and txid_snapshot. 2. Time zone: Fields with time zones (timestamp with time zone) in the source database instance will be automatically converted to the corresponding values in the time zone of the target instance after being synchronized to the target instance. 3. It is recommended to use the UTF-8 character set. Otherwise, display issues may occur on the console page. |
Supported SQL statements | 1. DML INSERT, UPDATE, and DELETE 2. DDL DROP DATABASE and ALTER DATABASE. CREATE SCHEMA and DROP SCHEMA. CREATE TABLE, ALTER TABLE, and DROP TABLE. CREATE VIEW and DROP VIEW. CREATE INDEX and DROP INDEX. CREATE SEQUENCE and DROP SEQUENCE. Note: Currently, statements such as CREATE TABLE table_name AS SELECT are not supported. Policies for filtering using the Where clause in SQL are currently not supported. |
Synchronization feature description | 1. Objects in the target database cannot be deleted during the synchronization task phase. 2. During incremental synchronization, creating a table with foreign key constraints and writing data will cause the task to fail. 3. In full initialization scenarios, if there are unsynchronized partitioned tables and their indexes, it will lead to synchronization failure during full import. It is recommended to add partitioned tables to the synchronization objects. 4. Currently, structure-only synchronization and full-only synchronization are not supported. |
Operation restrictions | 1. Do not perform the following operations during synchronization. Otherwise, they will cause the synchronization task to fail. Do not modify or delete user information (including usernames, passwords, and permissions) and port numbers in the source and target databases. During the incremental synchronization phase, highly concurrent DDL and DML operations may cause data conflicts. It is recommended to reduce the number of DDL operations during the incremental synchronization phase. Do not perform WAL cleanup operations on the source database. During the incremental synchronization phase, do not delete the system libraries and schemas: postgres.public.identify, __tencentdb__sync_*.*, and {database}.__tencentdb__ schema. When both the source and target databases are TencentDB instances, do not modify the network configuration of the database instances. If network changes occur in the source or target database instances, resulting in the deletion of the original VIP, the synchronization task will fail or remain in the Running status (while synchronization has actually stopped). In such cases, the synchronization task cannot be retried. You must manually terminate that task and recreate a synchronization task. 2. Incremental DDL synchronization will not synchronize DDL executed in PL/SQL (anonymous blocks, procedures, and functions). 3. Renaming tables is not supported. 4. Creating tables by using the execute format(create table) method in anonymous blocks is not currently supported.5. The create table operation is not supported between structure initialization and full data export. Otherwise, the error "The table does not exist in the target database" will occur.6. If a plugin with the same name already exists in the target database but resides in a different schema from the plugin in the source database, and if objects in the source database reference functions or types within the plugin, then database and table mapping for the objects of the source database is not supported. 7. At the start of the synchronization task, the system automatically creates a schema prefixed with __tencentdb__ in the source database and a schema prefixed with __dst__tencentdb__ in the target database. Objects under these schemas support the internal operations of DTS. Do not delete them manually. Upon task completion, DTS automatically cleans up these temporary objects without manual intervention.8. The conflict overwriting policy is only used to resolve primary key conflicts in tables. If both a primary key and a unique constraint are defined in a table, an error may be triggered. You need to process data manually before performing synchronization. 9. After the migration task is completed, you can click Complete before proceeding with subsequent operations: Update the last_value parameter of the corresponding sequence in the target database based on the last_value parameter of the sequence in the source database.Synchronize object comments. Clean up the schema for the synchronization task created in the source and target databases. Delete publications, replication slots, and other objects created in the source database. |
Description of data synchronization | 1. When DTS starts a data synchronization task, the REPLICA IDENTITY FULL property for tables awaiting synchronization and created tables will be set (for tasks with incremental DDL enabled). Long-running transactions on tables may cause a timeout of this setting, which in turn leads to a task failure. 2. When DTS starts a data synchronization task, uncommitted long-running transactions in the database instance may cause a timeout in creating a replication slot, which in turn leads to a task failure. |
Source database high availability (HA) | When HA operations on the source database cause migration of the primary node of the source database, the WAL logs will change. This results in failure of the original synchronization task. You need to clean up the synchronized data in the target database and then recreate a synchronization task. |
フィードバック