tencent cloud

Feedback

Synchronization from PostgreSQL to PostgreSQL

Last updated: 2024-04-19 11:05:27
    This document introduces how to use Data Transfer Service (DTS) to synchronize data from PostgreSQL databases to TencentDB for PostgreSQL.
    The following types of source databases are supported:
    Self-built PostgreSQL databases
    PostgreSQL databases provided by third-party cloud vendors
    TencentDB for PostgreSQL databases

    Prerequisites

    Ensure the source and target databases meet the sync feature and version requirements. For more information, see Databases Supported by Data Sync.
    The account executing the DTS sync task must have the following permissions on the source database: Event Trigger, Replication, CREATE, DROP, ALTER, SELECT, UPDATE, INSERT, and DELETE.
    The account executing the DTS sync task must have the following permissions on the target database: CREATE, DROP, ALTER, SELECT, UPDATE, INSERT, and DELETE.

    Notes

    During full data sync, DTS occupies certain resources of the source database instance. This may increase the load and pressure on the source instance. If your database configuration is low, it is recommended to perform the operation during off-peak hours.
    By default, a lock-free method is used, and no global lock is applied to the source database during the sync process.
    During data sync, DTS uses the account executing the sync task to create postgres.public.identify and {database}.__tencentdb__sync_*.* in the source database to record task status and configuration information. The target instance uses {database}.__tencentdb__ schema to record sync status information. The space occupied by postgres.public.identify, {database}.__tencentdb__sync_*.*, and {database}.__tencentdb__ schema is only about one thousandth to one ten-thousandth of the storage space of the source database. Therefore, it has no impact on the source database performance and does not compete for resources.

    Application Restrictions

    1. Sync of the following objects is not supported: ROLE, PROCEDURE, TRIGGER, FUNCTION, RULE, EXTENSION, etc.
    2. Sync of public objects and permission changes is not supported.
    3. During structure initialization of the full sync stage, foreign keys cannot be imported. The foreign keys are automatically removed before importing.
    4. Data sync does not support foreign tables. The full sync stage does not support exporting or importing temporary tables. The incremental sync stage only supports the sync of unlogged tables.
    5. When the system checks the plugins during the validation stage before the data sync task starts, if a warning is triggered by the first unmatched plugin, the system does not continue checking the remaining plugins.
    6. Sync tasks depend on the max_replication_slots parameter (one replication_slot is occupied by sync task configuration for each database). You can check the used slots by executing the SQL statement select * from pg_replication_slots. If the sum of the number of configured sync databases and replication_slots is larger than max_replication_slots, the attempts to create a new sync task will fail. The user needs to actively end the task so the system can release replication_slots (replication_slots are not released if the failed task is not ended).
    7. When the timestamps with time zones are synced from the source instance to the target instance, they will be automatically converted to the corresponding time in the time zone of the target instance.
    8. Operations such as source database HA causing the primary node of the source instance to migrate will result in changes to WAL logs. As a result, the sync task will fail, and you need to clear the data already synced to the target database and create a new sync task.

    Limits on Operations

    Do not perform the following operations during sync, otherwise the sync task will fail.
    Do not modify or delete user information (including usernames, passwords, and permissions) or port numbers in the source and target databases.
    Highly concurrent DDL and DML operations during the incremental sync stage might lead to data conflicts. It is recommended to reduce DDL operations at this stage.
    Do not perform operations to clear WAL logs on the source database.
    During the incremental sync stage, do not delete system libraries and schemas such as postgres.public.identify, __tencentdb__sync_., and {database}.__tencentdb__ schema.
    When both source and target databases are Tencent Cloud databases, do not change the network settings of the database instances. If the original VIPs are deleted due to changes in the source and target instance networks, the sync task will fail or remain in a running state without actually working. In such cases, the sync task cannot be retried and you must manually end the task and create a new task.

    Supported SQL Operations for Sync

    Operation Type
    SQL Operation Statements
    DML
    INSERT, UPDATE, and DELETE
    DDL
    CREATE DATABASE, DROP DATABASE, ALTER DATABASE, CREATE SCHEMA, DROP SCHEMA, CREATE TABLE,ALTER TABLE, DROP TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX, CREATE SEQUENCE, and DROP SEQUENCE
    Note:
    The CREATE TABLE table_name AS SELECT statement is not supported.
    SQL filters with the WHERE clause are not supported.

    Data Types Supported for Sync

    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, Nummultirange,
    Tsmultirange, Tstzmultirange, XML, Money, Time with time zone, Tsvector, Tsquery, pg_lsn, and txid_snapshot.

    Environment Requirements

    Type
    Environment Requirements
    Source Database Requirements
    The networks of source and target databases must be interconnected.
    The requirements for the source database parameters are as follows:
    Set the wal_level parameter to logical.
    The source database must meet the following requirement: number of databases to be synchronized + replication_slots <= max_replication_slots.
    The source database objects must be case-insensitive.
    Tables in the source database must have a primary key or a composite primary key.
    Target Database Requirements
    The version of the target database must be no earlier than that of the source database.
    The target database must have sufficient storage space. If the Initialization Type is set to Full Data Initialization, the space of the target database must be at least 1.2 times the space occupied by the databases and tables to be synchronized.
    The target database cannot have tables, views, or other objects with the same names as those to be synchronized from the source database.

    Directions

    1. Log in to the data sync purchase page, select the appropriate configuration, and click Buy Now.
    Parameter
    Description
    Billing Mode
    Monthly subscription and pay-as-you-go billing are supported.
    Source Instance Type
    Select PostgreSQL. It cannot be modified after purchase.
    Source Instance Region
    Select the region where the source instance is located. It cannot be modified after purchase.
    Target Instance Type
    Select PostgreSQL. It cannot be modified after purchase.
    Target Instance Region
    Select the region where the target instance is located. It cannot be modified after purchase.
    Specification
    Select a specification based on your business needs. The higher the specification, the better the performance. For more details, see Billing Overview.
    2. After the purchase is completed, return to the data sync task list. The newly created sync task is displayed. The new task must be configured before it can be used.
    3. In the data sync task list, click Configure in the Operations column to enter the task configuration page.
    4. On the sync task configuration page, configure the source instance, target instance, and the accounts and passwords of the instances. After testing connectivity, click Next.
    Given that the deployment forms and connection types of source databases overlap in multiple scenarios, the sync operations are similar across scenarios. Only typical configuration examples are provided below. Refer to these examples when you configure in other scenarios.
    Example 1: Synchronize a local self-built database to TencentDB by using Direct Connect or VPN.
    Configuration Item
    Parameter
    Description
    Task Configuration
    Task Name
    DTS automatically generates a task name. You can configure the name as needed.
    Running Mode
    Supports both immediate execution and scheduled execution.
    Source Instance Settings
    Source Instance Type
    The source instance type selected at the time of purchase. You cannot modify this parameter.
    Source Instance Region
    The source instance region selected at the time of purchase. You cannot modify this parameter.
    Access Type
    Select a type based on your scenario. In this scenario, select Direct Connect or VPN Access. This scenario requires you to select Direct Connect or VPN Access: Configuring VPN-IDC Interconnection. For the preparation required for other connection types, see Overview.
    Public Network: The source database can be accessed by using the public IP.
    Self-Build on CVM: The source database is deployed on Cloud Virtual Machine.
    Direct Connect: The source database can be connected to Tencent Cloud VPC by using Direct Connect.
    VPN: The source database can be connected to Tencent Cloud VPC through VPN Connections.
    TencentDB: The source database is a TencentDB instance.
    CCN: The source database can be connected to Tencent Cloud VPC through Cloud Connect Network.
    VPC-based Direct Connect Gateway/VPN Gateway
    If you use Direct Connect, only VPC-based Direct Connect Gateway/VPN Gateway is supported. Confirm the network type of the gateway. To use VPN Gateway, select a VPN Gateway instance.
    VPC
    Select a VPC and subnet associated with the VPC-based Direct Connect gateway or VPN gateway.
    Host Address
    The IP address or domain name for accessing the source MySQL instance.
    Port
    The port for accessing the source MySQL instance.
    Account
    The source instance account. It must have the required permissions.
    Password
    The password for the source instance account.
    Target Instance Settings
    Target Instance Type
    The target instance type selected at the time of purchase, which cannot be modified.
    Target Instance Region
    The target instance region selected at the time of purchase, which cannot be modified.
    Access Type
    Select a type based on your scenario. For this scenario, select Database.
    Instance ID
    Select the target instance ID.
    Account
    The target instance account, which must have the required permissions.
    Password
    The password for the target instance account.
    Example 2: Synchronize a TencentDB database to another TencentDB database
    Configuration Item
    Parameter
    Description
    Task Configuration
    Task Name
    DTS automatically generates a task name. You can configure the name as needed.
    Running Mode
    Supports both immediate execution and scheduled execution.
    Source Instance Settings
    Source Instance Type
    The source instance type selected at the time of purchase, which cannot be modified.
    Source Instance Region
    The source instance region selected at the time of purchase, which cannot be modified.
    Access Type
    Select a type based on your scenario. For this scenario, select Database. For preparation required for different access types, see the preparation overview.
    Public Network: The source database can be accessed through the public IP.
    Self-Build on CVM: The source database is deployed on Cloud Virtual Machine.
    Direct Connect: The source database can be connected to Tencent Cloud VPC through [Direct Connect](https://www.tencentcloud.com/document/product/216!ac1fc9354c56b77f7143d2b6a7d185ad).
    VPN Access: The source database can be connected to Tencent Cloud VPC through [VPN Connections](https://www.tencentcloud.com/document/product/1037!a419dbfe8173788c2b5605b6583b70d9).
    TencentDB: The source database is a TencentDB instance.
    CCN: The source database can be connected to Tencent Cloud VPC through Cloud Connect Network.
    Instance ID
    The source instance ID. You can view the source instance information on the instance list.
    Account
    The source instance account, which must have the required permissions.
    Password
    The password for the source instance account.
    Target Instance Settings
    Target Instance Type
    The target instance type selected at the time of purchase, which cannot be modified.
    Target Instance Region
    The target instance region selected at the time of purchase, which cannot be modified.
    Access Type
    Select a type based on your scenario. For this scenario, select Database.
    Instance ID
    Select the target instance ID.
    Account
    The target instance account, which must have the required permissions.
    Password
    The password for the target instance account.
    Example 3: Synchronize a PostgreSQL database provided by a third-party cloud provider to TencentDB over the public network.
    Configuration Item
    Parameter
    Description
    Task Configuration
    Task Name
    DTS automatically generates a task name. You can configure the name as needed.
    Running Mode
    Supports both immediate execution and scheduled execution.
    Source Instance Settings
    Source Instance Type
    The source instance type selected at the time of purchase, which cannot be modified.
    Source Instance Region
    The source instance region selected at the time of purchase, which cannot be modified.
    Access Type
    For databases from third-party cloud providers, Public Network is used in general cases. You can also choose VPN Access, Direct Connect, or CCN based on the actual network situation.
    In this scenario, select Public Network. For preparation required for different access types, see Overview.
    Public Network: The source database can be accessed through the public IP.
    Self-Build on CVM: The source database is deployed on Cloud Virtual Machine.
    Direct Connect: The source database can be connected to Tencent Cloud VPC through Direct Connect.
    VPN Access: The source database can be connected to Tencent Cloud VPC through VPN Connections.
    Database: The source database is a TencentDB instance.
    CCN: The source database can be connected to Tencent Cloud VPC through Cloud Connect Network.
    Host Address
    The IP address or domain name for accessing the source instance.
    Port
    The port for accessing the source instance.
    Account
    The source instance account, which must have the required permissions.
    Password
    The password for the source instance account.
    Target Instance Settings
    Target Instance Type
    The target instance type selected at the time of purchase, which cannot be modified.
    Target Instance Region
    The target instance region selected at the time of purchase, which cannot be modified.
    Access Type
    Select a type based on your scenario. For this scenario, select Database.
    Instance ID
    Select the target instance ID.
    Account
    The target instance account, which must have the required permissions.
    Password
    The password for the target instance account.
    5. On the set sync options and objects page, set Data Initialization Option, Data Sync Option, and Sync Object Option. After completing the settings, click Save and Go Next.
    Note:
    If you select only Full Data Initialization for Initialization Type, the system assumes that table structures have already been created in the target database. In this case, the system will not synchronize table structures or verify if tables with duplicate names exist in the source database and target database. Therefore, if you also select Precheck and Report Error in If Target Already Exists, this validation and error-reporting feature will not take effect.
    If you plan to rename a table during sync (for example, renaming table A to table B), you must select the entire database (or the entire instance) where table A is located in Synchronize Object Option. Otherwise, after the renaming operation, data from table B will not be synchronized to the target database.
    Configuration Item
    Parameter
    Description
    Data Initialization Option
    Initialization Type
    Structure Initialization: When you run the sync task, the table structure in the source instance is initialized in the target instance first.
    Full data initialization: When you run the sync task, data in the source instance will be initialized in the target instance first. In scenarios where only Full data initialization is selected, you need to create the table structure in the target database in advance.
    By default, both are selected. You can cancel options based on the actual situation.
    If Target Already Exists
    Precheck and report error: If a table with the same name exists, an error is reported and the process is not continued.
    Ignore and execute: Directly synchronize both full and incremental data to the tables in the target instance.
    Data Sync Option
    Conflict Resolution Method
    Report: If a primary key conflict is detected during sync, an error is reported and the data sync task is paused.
    Ignore: If a primary key conflict is detected during sync, the primary key record in the target database is retained.
    Overwrite: If a primary key conflict is detected during sync, the primary key record in the source database overwrites that in the target database.
    SQL Type
    Supported operations: Insert, Update, Delete, and DDL. Currently, Custom DDL is not supported.
    Synchronize Object Option
    Database and Table Objects of Source Instance
    Select objects to be synchronized. The following objects are supported: databases, tables, and views. Only SEQUENCE is supported as advanced objects.
    6. On the Verify task page, complete the verification and click Start Task after all verification items pass. If the verification task fails, you can refer to Check Item Overview to fix the issue and run the verification task again.
    Failed: Indicates that the verification item failed the check and the task is blocked. You need to fix the problem and run the verification task again.
    Alarm: Indicates that the verification item does not fully meet the requirements. The task can proceed but affects the business to some extent. You need to decide based on the prompt whether to ignore the alarm or fix the issue and continue.
    7. Return to the data sync task list. The task has entered the Running status.
    Note:
    In the Operation column, choose More > Stop to stop a sync task. Make sure data sync is complete before you stop the task.
    8. (Optional) You can click the name of a task to enter the task details page and view the initialization state and monitoring data of the task.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support