PostgreSQL

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

Prerequisites

For incremental synchronization, users need to enable data logs, wal_level needs to be set to logical, max_replication_slots needs to be greater than 1
The table to be migrated must have a primary key or a unique index, otherwise duplicate data may be generated. For details, refer to Incremental Synchronization for Tables without Primary Keys or Unique Indexes.

Required Permissions

Type	Source Database	Target Database
Full	SELECT	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE
Full+Incremental	SELECT, REPLICATION	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE

Function Limitations

A "full + incremental" task can only synchronize one database. If multiple databases need to be synchronized, a task needs to be created for each database.
The synchronization object only supports data tables and does not temporarily support the synchronization of DDL statements. If you need to synchronize DDL statements, you need to manually execute the corresponding DDL operations in the target database, and then restart the data synchronization task.
Incremental synchronization does not support the synchronization of data types such as GEOMETRY, TSQUERY, TSVECTOR, TXID_SNAPSHOT.
Due to the characteristics of the database itself, it does not support migration from a higher version to a lower version.

Attention

Incremental Synchronization for Tables without Primary Keys or Unique Indexes

If the table to be migrated does not have a primary key or a unique index, you need to execute ALTER TABLE tb_xxx REPLICA IDENTITY FULL, otherwise, incremental migration cannot be performed.
If the table to be migrated does not have a primary key but has a unique index such as idx_unq_name, there are two ways to handle this:
1. The same as the processing method without a primary key, execute ALTER TABLE tb_xxx REPLICA IDENTITY FULL, this method has a relatively low replication efficiency.
2. Manually specify the unique index for replication, ALTER TABLE tb_xxx REPLICA IDENTITY USING INDEX idx_unq_name, this method has a higher replication efficiency.
If the table to be migrated does not have a primary key but has a unique index, and the unique index used for replication is changed during the task execution, the task needs to be restarted, otherwise, the task may fail.

Incremental Synchronization Progress Saving and Replication Slot Description

During incremental synchronization, UDTS will create a data table named public.udts_pgsync_progress in the target database to record synchronization progress and other information. Please do not delete it during the synchronization process, otherwise it will cause task abnormalities.
During incremental synchronization, UDTS will create a replication slot prefixed with udts_ in the source database for data replication.
When the customer pauses the synchronization task, the existence of the replication slot prefixed with udts_ in the source database for data replication will cause the wal to continuously occupy the disk due to it not being cleaned up. If you need to stop the task for a long time, it is recommended to delete this slot and the task.
After the customer deletes the task, you need to manually delete the corresponding slot in the source database. The operation steps are as follows:

Operation Steps:

Assume the database migrated incrementally using UDTS is db_service_car.
Execute select * from pg_replication_slots, the return result is 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        |
-------------------------------------------------------------------------------------------------------------------------------

Find the slot_name with the prefix udts and the database is db_service_car.
Execute select pg_drop_replication_slot('udts_dd27ef9195294b49a5d424eda8f399f7').
Execute select * from pg_replication_slots again to confirm deletion.∂∂

PostgreSQL Form Filling

Data Source Form

Parameter Name	Description
Address Type	Provides three types of addresses: private network address, public network address, and dedicated line address. For private network address, VPC and subnet information need to be filled in. Public network address supports both IP and domain names.
Port	PostgreSQL connection port
Username	PostgreSQL connection username
Password	Password for the corresponding user in the PostgreSQL database
Database Name	Name of the PostgreSQL database to be migrated
Table Name	Name of the PostgreSQL table to be transferred, optional. If not filled in, the entire database will be migrated; if filled in, the specified table will be migrated or filtered. Refer to Form Table Name Filling Rules for details.
Maximum Speed	The speed range for public network/dedicated line is 1-256 MB/s, default is 40 MBps (i.e., 320 Mbps); the speed range for private network is 1-1024 MB/s, default is 80 MBps (i.e., 640 Mbps)

Transfer Target Form

Parameter Name	Description
Address Type	The target currently only supports intranet
Port	PostgreSQL connection port
Username	PostgreSQL connection username
Password	Password for the corresponding user of PostgreSQL database
Maximum Speed	The speed range of the intranet is 1-1024 MB/s, default is 80 MBps (i.e., 640 Mbps)

Form Table Name Filling Rules

1. Table names only support characters [a-z][A-Z][0-9] and _.
1. Multiple table names are separated by commas.
1. The public. schema is automatically appended if the table name does not include a schema.
1. If you enter public.tb_test01, schema01.tb_test02 as the table names, it means only public.tb_test01, schema01.tb_test02 these two tables will be migrated.
1. The rules for filling in table names support filtering. Starting with a ! indicates that the current table will be filtered out during the migration process. For example, !public.tb_filter01 means that public.tb_filter01 will be filtered out during the migration process.

Redis ElasticSearch