tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Transfer Service
    Documentation
    Download PDF

    Synchronization from PostgreSQL to PostgreSQL

    Last updated: 2024-04-19 11:05:27
    Download PDF
      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.

      Contact Us
      Technical Support

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

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy