Skip to main content

PostgreSQL Migration Synchronization to StarRocks

NineData data replication supports the structure, full data, and incremental data synchronization from PostgreSQL to StarRocks data sources.

Background Information​

Apache StarRocks is a high-performance, real-time analytical database based on the MPP architecture, known for its ultra-fast and easy-to-use features. It has now become a popular data warehouse product in China, in addition to ClickHouse and Doris.

StarRocks is built based on the Doris version and has been developing as an independent branch since 2021. Currently, many enterprises choose to build reporting analysis systems, enterprise data warehouses, and ad-hoc query analysis based on StarRocks. Based on these platform capabilities, applications such as user behavior analysis, AB testing platforms, log retrieval analysis, user profile analysis, and order analysis can be constructed.

As a data warehouse, enterprises need to process various source data through ETL before warehousing for data analysis. Relational databases, due to their extensive application scenarios and scale, have also become the core data source for StarRocks data integration.

NineData provides end-to-end data replication capabilities to help enterprises quickly achieve real-time replication of PostgreSQL to StarRocks, enhancing the value of data applications.

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 and target data sources have been added to NineData. For instructions on how to add them, please refer to Adding Data Sources.

  • The source database type is PostgreSQL.

  • The target database is StarRocks, with versions 3.1, 3.0, 2.5, 2.4, 2.3, 2.2, 2.1, 2.0.

  • 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 view it.

    • 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.

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

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

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 certain read and write resources of the source and target data sources, leading to increased database load.
  • It is recommended to ensure that each table in the synchronization object has a primary key or unique constraint, and the column names are unique, 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 the 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 .

    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 structure replication, ignore the same name table. If you also perform data replication, the data will be appended to the same name table 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 structure replication, delete the same name table 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 structure replication, retain the table structure in the target database, and clear the data in the same name table 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, then click .

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

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

    • Including : Configure the name of the target database table after synchronization to the target data source, and click .

      tip

      You can click on on the right side of the page to customize the name of the column name synchronized to the target data source. In addition, you can set , and only data that meets the filtering conditions will be synchronized to the target data source. Taking the test data employees table as an example, set the filtering condition to emp_no>=10005, then data with emp_no column values less than 10005 will not 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 database. The table name and column name in the target database 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 .

    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 the structure replication is completed.
      • +、: Start after the 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 to view the synchronization delay on the page. sync_delay
    • If the pre-inspection does not pass, you need to click in the column on the right side of the target inspection item, troubleshoot the cause of the failure, manually repair it, and then click 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 running. At this time, you can perform the following operations:

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

View Synchronization Results​

  1. Log in to the 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.

    pg2sr_result

    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 optional.
    • 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.
    4Structure Replication (Displayed in scenarios including structure replication)Show the progress and details of structure replication.
    • Click on Logs on the right side of the page: View the execution logs of structure replication.
    • Click on 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 in scenarios including full replication)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. The unit is rows/second.
    • Click on Logs on the right side of the page: View the execution logs of full replication.
    • Click on refresh on the right side of the page: View the latest information.
    6Incremental Replication (Displayed in scenarios including incremental replication)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 for replication operations, 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 starts.
      • : 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 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 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. 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 the comparison between the current source and target data.
    • Click on Logs on the right side of the page: View the execution logs of consistency comparison.
    • Click on Monitoring (only displayed when data comparison is displayed): View the trend chart of comparison RPS (number of records compared per second). Click on Details to view earlier records.
    • Click on details in the Operation column on the right side of the comparison list (displayed when inconsistent): View the comparison details of the source and target ends.
    • Click on sql in the Operation column on the right side of the comparison list (displayed when inconsistent): 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: PostgreSQL and StarRocks Data Type Mapping Table​

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

CategoryPostgreSQL Data TypeStarRocks Data Type
NumericSMALLINTSMALLINT
INTEGERINT
BIGINTBIGINT
DECIMALDECIMAL
REALDOUBLE
DOUBLEDOUBLE（Decimal point, up to 16 digits stored）
SMALLSERIALSMALLINT
SERIALINT
BIGSERIABIGINT
MONETARYMONEYDECIMAL
CharacterVARCHAR(n)
  • Width of (n) < 11000: VARCHAR(3*n)
  • Width of (n) ≥ 11000: STRING
CHAR(n)
  • Width of (n) < 80: CHAR(3*n)
  • Width of (n) ≥ 80: STRING
TEXTSTRING
BinaryBYTEASTRING
Date and TimeTIMESTAMP [(p)] [without time zone]DATETIME（Both PostgreSQL and StarRocks need to set the session-level time_zone, and the values must be consistent）
TIMESTAMP [(p)] with time zoneDATETIME（Both PostgreSQL and StarRocks need to set the session-level time_zone, and the values must be consistent）
DATEDATE
TIME [(p)] [without time zone]VARCHAR
TIME [(p)] with time zoneVARCHAR
INTERVAL [fields] [(p)]VARCHAR(100)
BooleanBOOLEANBOOLEAN
GeometricPOINT、LINE、LSEG、BOX、PATH、POLYGON、CIRCLEVARCHAR
Network AddressCIDR、INET、MACADDR、MACADDR8VARCHAR
Text SearchTSVECTORVARCHAR
XMLXMLVARCHAR
JSONJSONJSON

Introduction to Data Replication

Last updated on