Skip to main content

PostgreSQL Migration Synchronization to Doris

NineData data replication supports data synchronization between PostgreSQL and Doris data sources.

Background Information​

The powerful capabilities of Apache Doris make it an ideal choice for building various applications. Enterprises can build applications such as user behavior analysis, AB testing platforms, log retrieval analysis, user profile analysis, order analysis, etc., based on Doris, to achieve efficient management and analysis of large-scale data.

To achieve the above scenarios, enterprises need to incorporate various source data into the data warehouse for in-depth analysis after ETL processing. PostgreSQL, due to its extensive application scenarios and scale, has become one of the core data sources for Apache Doris data integration.

Feature Introduction​

NineData data replication supports high-performance replication of structure, full data, and incremental data between data sources. For MySQL data sources, it also provides bidirectional replication, enabling the quick construction of geo-distributed active-active business architectures.

  • Structure: Supports the replication of object structures between homogeneous and heterogeneous data sources, greatly reducing the barrier to data replication between two sources.
  • Full Data: Achieves row-level concurrent batch replication through intelligent data sharding, effectively ensuring replication performance. The independently developed novel breakpoint resume technology ensures the accuracy of data in tables without primary keys.
  • Incremental Data: Supports incremental data replication of full object types DML|DDL, combined with transaction-level concurrency, hotspot merging, and other technologies to provide robust replication performance while ensuring transaction consistency.
  • Bidirectional Real-time Data Replication (only between MySQL instances): Direct bidirectional replication of data between multiple nodes ensures that all node data remains up-to-date.

With the above features, it is easy and efficient to achieve scenarios such as full|incremental data replication, full|incremental data migration, full|incremental data synchronization, data integration, and seamless migration without downtime, providing enterprises with flexible and reliable data replication solutions.

Prerequisites​

  • The source data source and target data source have been added to NineData. For details on how to add them, please refer to Add Data Source.

  • The source data source is PostgreSQL, and the target data source is Doris.

  • You must have the following permissions for the source and target data sources:

    Replication Type
    		Source Data SourceTarget Data Source
    Structure ReplicationCONNECT、SELECTDDL
    Full ReplicationCONNECT、SELECTTABLE-related permissions (ALTER、DROP、SELECT、INSERT、UPDATE、DELETE)
    Incremental ReplicationSUPERUSERTABLE-related permissions (ALTER、DROP、SELECT、INSERT、UPDATE、DELETE)

  • For incremental replication, please open the postgresql.conf file and configure the following parameters. If you cannot find the location of the file, you can execute the SHOW config_file; SQL command in the psql client to check.

    • The wal_level parameter of the source data source must be logical.
    • The wal_sender_timeout parameter of the source data source is set to 0. This parameter is used to interrupt replication connections that have been stagnant for more than the specified number of milliseconds. The default value is 60000 milliseconds. Setting it to 0 will disable the timeout mechanism.
    • The max_replication_slots parameter of the source data source must be greater than 1. This parameter specifies the maximum number of replication slots that the server can support. The default value is 10.
    • The max_wal_senders parameter of the source data source must be greater than 1. This parameter specifies the maximum number of concurrent connections. The default value is 10.

Usage Limitations​

  • Before performing data synchronization, it is necessary to assess the performance of the source and target data sources, and it is recommended to perform data synchronization during off-peak business hours. Otherwise, the full data initialization will occupy a certain amount of read and write resources of the source and target data sources, leading to increased database load.
  • It is necessary to ensure that each table in the synchronization object has a primary key or unique constraint, and the column names have uniqueness, otherwise, the same data may be synchronized repeatedly.

Operation Steps​

Commercialization Notice

NineData’s data replication product has been commercialized. You can still use 10 replication tasks for free, with the following considerations:

  • Among the 10 replication tasks, you can include 1 task, with a specification of Micro.

  • Tasks with a status of do not count towards the 10-task limit. If you have already created 10 replication tasks and want to create more, you can terminate previous replication tasks and then create new ones.

  • When creating replication tasks, you can only select the you have purchased. Specifications that have not been purchased will be grayed out and cannot be selected. If you need to purchase additional specifications, please contact us through the customer service icon at the bottom right of the page.

  1. Log in to NineData Console.

  2. Click on > in the left navigation bar.

  3. On the page, click on the in the upper right corner.

  4. On the tab, configure according to the table below and click on .

    Parameter
    		Description
    Enter the name of the data synchronization task. To facilitate subsequent search and management, please use meaningful names as much as possible. Up to 64 characters are supported.
    The data source where the synchronization object is located.
    The data source that receives the synchronization object.
    Choose the content that needs to be replicated to the target data source.
    • : Only synchronize the database table structure of the source data source, without synchronizing the data.
    • : Synchronize all objects and data of the source data source, that is, full data replication.
    • : After the full synchronization is completed, perform incremental synchronization based on the logs of the source data source.
    （Required when is selected）
    • : Stop the task when the same name table is detected during the pre-inspection phase.
    • : Send a prompt and continue the task when the same name table is detected during the pre-inspection phase. During structural replication, ignore the table with the same name. If you also perform data replication, the data will be appended to the table with the same name without overwriting the existing data.
    • : Send a prompt and continue the task when the same name table is detected during the pre-inspection phase. During structural replication, delete the table with the same name in the target database and re-replicate the table structure based on the source database. If you also perform data replication, the data will be written after the table structure replication is completed.
    • （Optional when both structure and data replication are performed）: Send a prompt and continue the task when the same name table is detected during the pre-inspection phase. During structural replication, retain the table structure in the target database, and clear the data in the table with the same name at the beginning of data replication, then replicate from the original table again.
    （Required when is not selected）
    • : Stop the task when data is detected in the target table during the pre-inspection phase.
    • : Ignore the data when it is detected in the target table during the pre-inspection phase, and append the other data.
    • : Delete the data when it is detected in the target table during the pre-inspection phase, and re-enter it.

  5. On the tab, configure the following parameters, and then click on .

    Parameter
    		Description
    Select the content that needs to be replicated. You can choose to replicate all content in the source database, or you can choose , select the content that needs to be replicated in the list, and click > to add it to the right list.

  6. On the tab, choose different operations based on the selected replication type.

    • Including : Configure the table name, Key, and Distribute Key after the target table is synchronized to the target data source, and click . The default Data Model and Key definition used after different types of tables are replicated to Doris are as follows.

      PostgreSQL Table TypeDoris Data ModelDoris Key DefinitionDoris Distribute Key Definition
      Table with Primary KeyUnique ModelAll primary key columns, in the order defined by the source database.All primary key columns.
      Table without Primary Key but with Unique KeyUnique ModelAll unique key columns, in the order defined by the source database.All unique key columns.
      Table without Primary Key and without Unique KeyDuplicate ModelDefault to select the first 2 columns in the table.Keep the same as Key.
      tip

      You can click on on the right side of the page to customize the column name after synchronization to the target data source. In addition, you can also set , and only data that meets the filtering conditions will be synchronized to the target data source.

    • Not including : The system defaults to selecting the same name database in the target data source. If it does not exist, you need to manually select the target library. The table name and column name in the target library need to be consistent with the synchronization object. If they are not consistent, you can also manually map the table name and column name.

  7. On the tab, wait for the system to complete the pre-inspection, and after the pre-inspection is passed, click on .

    tip
    • You can check . After the synchronization task is completed, automatically start the data consistency comparison based on the source data source to ensure that the data on both sides is consistent. Depending on the you selected, the timing of starting is as follows:
      • : Start after structural replication is completed.
      • +、: Start after full replication is completed.
      • ++、: Start when the incremental data is consistent with the source data source for the first time and is 0 seconds. You can click on to view the synchronization delay on the page. sync_delay
    • If the pre-inspection does not pass, you need to click on in the column on the right side of the target inspection item to investigate the cause of the failure, manually repair it, and then click on to re-execute the pre-inspection until it passes.
    • If the is , you can repair or ignore it according to the specific situation.

  8. On the page, it prompts , and the synchronization task starts to run. At this time, you can perform the following operations:

    • Click on to view the execution of each stage of the synchronization task.
    • Click on to return to the task list page.

View Synchronization Results​

  1. Log in to NineData Console.

  2. Click on > in the left navigation bar.

  3. Click on the of the target synchronization task on the page, and the page description is as follows.

    result2

    No.
    		Function
    		Description
    1Synchronization DelayThe data synchronization delay between the source data source and the target data source, 0 seconds means there is no delay between the two sides. At this time, you can choose to switch the business to the target data source to achieve smooth migration.
    2Configure AlertsAfter configuring the alert, the system will notify you through the method you choose when the task fails. For more information, please refer to Introduction to Operations Monitoring.
    3More
    • Pause: Pause the task, only tasks in the Running status are available.
    • Terminate: End the unfinished or listening (i.e., incremental synchronization) tasks. After terminating the task, it cannot be restarted, please operate with caution. If the synchronization object contains triggers, the trigger replication option will pop up, please choose as needed.
    • Delete: Delete the task, the task cannot be restored after deletion, please operate with caution.
    4Structural Replication (Displayed when structural replication is included)Show the progress and details of structural replication.
    • Click on Logs on the right side of the page: View the execution logs of structural replication.
    • Click on the refresh on the right side of the page: View the latest information.
    • Click on View DDL in the Operation column on the right side of the target object in the list: You can view the SQL replay.
    5Full Replication (Displayed when full replication is included)Show the progress and details of full replication.
    • Click on Monitoring on the right side of the page: View the monitoring metrics during the full replication process. During the full replication process, you can also click on Throttle Settings on the right side of the monitoring metric page to limit the rate of writing to the target data source per second.
    • Click on Logs on the right side of the page: View the execution logs of full replication.
    • Click on the refresh on the right side of the page: View the latest information.
    6Incremental Replication (Displayed when incremental replication is included)Show the monitoring metrics of incremental replication.
    • Click on on the right side of the page: View the operations currently being executed by the current replication task, including:
      • : The replication task is executed by multiple threads, showing the current thread number.
      • : Details of the SQL statement currently being executed by the current thread.
      • : The response time of the current thread, if this value increases, it means that the current thread may be stuck for some reason.
      • : The timestamp when the current thread started.
      • : The status of the current thread.
    • Click on Throttle Settings on the right side of the page: Limit the rate of writing to the target data source per second. The unit is rows/second.
    • Click on Logs on the right side of the page: View the execution logs of incremental replication.
    • Click on the refresh on the right side of the page: View the latest information.
    7Modify ObjectShow the modification records of the synchronization object.
    • Click on Modify Synchronization Object on the right side of the page to configure the synchronization object.
    • Click on the refresh on the right side of the page: View the latest information.
    8Data ComparisonShow the results of data comparison between the source data source and the target data source. Including Structural Comparison and Data Comparison. If you have not enabled data comparison, please click on Enable Data Comparison on the page.
    • Click on Re-compare on the right side of the page: Re-initiate comparison of the current source and target data.
    • Click on the date to view the list of all comparison results, and click on the target list item to switch to the details of that comparison result.
    • Click on Logs on the right side of the page: View the execution logs of consistency comparison.
    • Click on Monitoring (only displayed during data comparison): View the trend chart of comparison RPS (number of records compared per second). Click on Details to view records from earlier.
    • Click on details in the Operation column on the right side of the comparison list: View the comparison details of the source and target ends.
    • Click on sql (displayed when inconsistent) in the Operation column on the right side of the comparison list: Generate a change SQL, you can directly copy this SQL to the target data source for execution, and modify the inconsistent content.
    9ExpandShow the detailed information of the current replication task, including Replication Type, Replication Object, Start Time, etc.

Appendix 1: PostgreSQL and Doris Data Type Mapping Table​

During the data replication process, PostgreSQL data types are mapped to corresponding Doris data types.

CategoryPostgreSQL Data TypeDoris Data Type
NUMERICSMALLINTSMALLINT
INTEGERINT
BIGINTBIGINT
DECIMALDECIMAL
REALDOUBLE
DOUBLEDOUBLE（up to 16 decimal places）
SMALLSERIALSMALLINT
SERIALINT
BIGSERIALBIGINT
MONETARYMONEYDECIMAL
CHARACTERVARCHAR(N)To avoid data loss, use the following conversion rules:
  • If the width of VARCHAR(N) in the source is less than 11000: use VARCHAR(3*N).
  • If the width of VARCHAR(N) in the source is greater than 11000: use STRING.
CHAR(N)To avoid data loss, use the following conversion rules:
  • If the width of CHAR(N) in the source is less than 80: use CHAR(3*N).
  • If the width of CHAR(N) in the source is greater than 80: use STRING.
TEXTSTRING
BINARYBYTEASTRING
DATE AND TIMETIMESTAMP [(P)] [WITHOUT TIME ZONE]
  • Doris version >= 1.2.0: DATATIME (without fsp) or DATATIMEV2 (with fsp).
  • Doris version < 1.2.0: DATATIME
TIMESTAMP [(P)] WITH TIME ZONE
  • Doris version >= 1.2.0: DATATIME (without fsp) or DATATIMEV2 (with fsp).
  • Doris version < 1.2.0: DATATIME
DATEDATE
TIME [(P)] [WITHOUT TIME ZONE]VARCHAR
TIME [(P)] WITH TIME ZONEVARCHAR
INTERVAL [FIELDS] [(P)]INT
BOOLEANBOOLEANBOOLEAN
GEOMETRICPOINT、LINE、LSEG、BOX、PATH、POLYGON、CIRCLEVARCHAR
NETWORK ADDRESSCIDR、INET、MACADDR、MACADDR8VARCHAR
TEXT SEARCHTSVECTORVARCHAR
XMLXMLVARCHAR
JSONJSON
  • Doris version >= 1.2.0: JSONB.
  • Doris version < 1.2.0: STRING.
### Appendix 2: Pre-inspection Item List
Inspection ItemInspection Content
Check the compliance of object namesCheck whether the library name and table name comply with the Doris naming convention
Source Data Source Connection CheckCheck the gateway status of the source data source, whether the instance is reachable, and the accuracy of the username and password
Target Data Source Connection CheckCheck the gateway status of the target data source, whether the instance is reachable, and the accuracy of the username and password
Target Library Permission CheckCheck whether the account permissions of the target database meet the requirements
Source Library Permission CheckCheck whether the account permissions of the source database meet the requirements
Target Library Data Existence CheckCheck whether there is data in the target database for the object to be replicated
Target Library Same Name Object Existence CheckCheck whether there is an object with the same name in the target database
Check wal_levelCheck whether the wal_level of the source data source is logical

Introduction to Data Replication

Last updated on