Skip to Content
Data TransmissionSource is PostgresSQLPostgreSQL

PostgreSQL

UDTS supports PostgreSQL as a data transfer source/target, supporting versions 9.4 to 16.x.

Prerequisites

Required Permissions

TypeSource DatabaseTarget Database
FullSELECTOwner permission of the database to be migrated, or rolcreatedb permission
Full+IncrementalSELECT,REPLICATIONOwner permission of the database to be migrated, or rolcreatedb permission

Function Limitations

  • A “Full+Incremental” task can only synchronize one database. If multiple databases need synchronization, a separate task must be created for each database.
  • Synchronization objects only support data tables and do not currently support DDL statement synchronization. If synchronization of DDL statements is needed, manually execute the corresponding DDL operations on the target database and then restart the data synchronization task.
  • Incremental synchronization does not support the synchronization of GEOMETRY, TSQUERY, TSVECTOR, TXID_SNAPSHOT data types.
  • Due to database characteristics, migration from a higher version to a lower version is not supported.

Notes

Handling Incremental Synchronization for Tables without Primary Key or Unique Index

  • If the table to be migrated has neither a primary key nor a unique index, execute ALTER TABLE tb_xxx REPLICA IDENTITY FULL, otherwise incremental migration cannot be performed.
  • If the table to be migrated has no primary key but has a unique index such as idx_unq_name, there are two methods:
1. Similar to handling tables without a primary key, execute ALTER TABLE tb_xxx REPLICA IDENTITY FULL. This method is less efficient for replication.
2. Manually specify the unique index for replication, ALTER TABLE tb_xxx REPLICA IDENTITY USING INDEX idx_unq_name, which is more efficient for replication.
  • If the table to be migrated has no primary key but has a unique index, and the unique index used for replication changes during the task’s operation, the task needs to be restarted, or it may fail.

Incremental Synchronization Progress Saving and Replication Slot Explanation

  • During incremental synchronization, UDTS will create a public.udts_pgsync_progress data table in the target database to record synchronization progress and other information. Do not delete it during synchronization, or the task may fail.
  • During incremental synchronization, UDTS will create a replication slot with the prefix udts_ in the source database for data replication.
  • After clients pause the synchronization task, due to the existence of the prefix udts_ replication slot in the source database for data replication, the WAL cannot be cleaned, causing continuous disk usage. If the task needs to be stopped for a long time, it is recommended to delete this slot and the task.
  • After a client deletes a task, the corresponding slot needs to be manually deleted in the source database. The steps are as follows:

Steps:

1. For example, the database migrated by UDTS incrementally is db_service_car.
2. Execute select * from pg_replication_slots, and the results are as follows:

-------------------------------------------------------------------------------------------------------------------------------
| slot_name                               |   plugin        |   slot_type   |   datoid      |   database         |   active   |
-------------------------------------------------------------------------------------------------------------------------------
| udts_dd27ef9195294b49a5d424eda8f399f7   |   test_decoding |   logical     |   4839920     |   db_service_car   |   f        |
| udts_050a1f4b1cc84b4bb6460e5477ec3d79   |   test_decoding |   logical     |   2695822     |   db_service_xxx   |   t        |
-------------------------------------------------------------------------------------------------------------------------------

3. Find the slot_name with the database as db_service_car and prefix udts.
4. Execute select pg_drop_replication_slot('udts_dd27ef9195294b49a5d424eda8f399f7');
5. Execute select * from pg_replication_slots again to confirm deletion.

PostgreSQL Form Filling

Data Source Form

Parameter NameDescription
Address TypeProvides three types of addresses: Intranet, Internet, and Dedicated Line. Intranet requires VPC and subnet information. Internet supports both IP and domain name.
PortPostgreSQL connection port
UsernamePostgreSQL connection username
PasswordPassword for the corresponding PostgreSQL database user
Database NameThe name of the PostgreSQL database to be migrated
Table NameOptional. Name of the PostgreSQL table to be transferred. If not filled, the entire database is migrated. If filled, the specified table is migrated. Refer to Table Name Filling Rules for details.
Maximum RateFor Internet/Dedicated Line, the rate range is 1-256 MB/s, default 40 MBps (i.e., 320 Mbps); for Intranet, the rate range is 1-1024 MB/s, default 80 MBps (i.e., 640 Mbps)

Transfer Target Form

Parameter NameDescription
Address TypeCurrently only supports Intranet
PortPostgreSQL connection port
UsernamePostgreSQL connection username
PasswordPassword for the corresponding PostgreSQL database user
Maximum RateFor Intranet, the rate range is 1-1024 MB/s, default 80 MBps (i.e., 640 Mbps)

Table Name Filling Rules

    1. Table names only support characters from [a-z][A-Z][0-9] and _.
    1. Multiple table names are separated by commas.
    1. If no schema is added to the table name, the default public. schema is appended.
    1. Filling in table names as public.tb_test01,schema01.tb_test02 indicates only migrating these two tables: public.tb_test01,schema01.tb_test02.
RedisElasticSearch