Sybase Data Migration to PostgreSQL

NineData Data Replication supports copying data from Sybase to PostgreSQL, including both full and incremental replication.

Feature Introduction

NineData data replication supports high-performance replication of structure, full data, and incremental data between data sources. For certain 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 DML and DDL incremental data replication for all object types. By leveraging technologies such as row-level concurrency and hotspot merging, it delivers robust replication performance.
• 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

• Both the source and target data sources have been added to NineData. For how to add, see Creating Data Sources.

• The table has been manually created in the target data source (PostgreSQL), and the table structure must match the structure of the table to be replicated from the source data source (Sybase).

• To perform bidirectional replication, you must add a multi-active tag to all data sources involved in the replication task. For instructions on how to add this tag, see the Appendix.

• The versions of the source and target data sources are listed in the table below.

  Source Data Source	Target Data Source
  Sybase 16.0, 15.7	PostgreSQL 16, 15, 14

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

  Replication Type	Sybase	PostgreSQL
  One-way Full and Incremental Replication	replication_role、sso_role	All Privileges on Schema、All Privileges on All Tables
  Two-way Full and Incremental Replication	sa_role	Database Owner、Table Owner、All Privileges on Schema、Replication Role

Usage Restrictions

• If your Sybase data source is deployed in a primary-secondary architecture and Bi-directional replication is required, you must enter the connection addresses of both the primary and secondary nodes when adding the data source. Otherwise, data backflow from PostgreSQL to Sybase will fail.

  

• Before performing data synchronization, evaluate the performance of both the source and target data sources. It is recommended to perform data synchronization during off-peak business hours. Otherwise, the initialization of full data will occupy some read/write resources of the source and target data sources, causing an increase in database load.

• Ensure that each table in the synchronization objects has a primary key or unique constraint, and column names are unique. Otherwise, duplicate data may be synchronized.

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 in the upper right corner.

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

   Parameter	Description
   	Enter the name of the data synchronization task. For easy searching and management later, try to use a meaningful name. Supports up to 64 characters.
   	The data source where the synchronization object is located.
   	The data source that receives the synchronization object.
   	Choose the replication method. : A one-way data replication task from the source data source to the target data source. : A two-way incremental data replication task between the source and target data sources.
   	Only displayed when is . : Synchronize all objects and data of the source data source, that is, full data replication. The switch on the right is the switch for periodic full replication. For more information, please see Periodic Full Replication. : After the full synchronization is completed, perform incremental synchronization based on the log of the source data source.
   	The replication type from the source data source to the target data source, only displayed when is . Among them, is selected by default and cannot be turned off.
   	It needs to be selected when is and is only . : Perform incremental replication based on the current replication task start time. : Choose the start time of incremental replication. You can choose the time zone according to your business location as needed.
   	The replication type from the target data source to the source data source, only displayed when is . Only supports , and it is selected by default and cannot be turned off.
   	: Stop the task when data is detected in the target table during the pre-check stage. : When data is detected in the target table during the pre-check stage, ignore this part of the data and append other data. : When data is detected in the target table during the pre-check stage, delete this part of the data and rewrite it.

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

   Parameter	Description
   	Choose the content to be copied. You can choose to copy all content of the source library, or you can choose , select the content to be copied in the list, and click > to add it to the right list.

   If you need to create multiple identical replication links, you can create a configuration file and import it when creating a new task. Click at the top right corner, then click Download Template to download the configuration file template to your local machine. After editing, click to upload the configuration file for batch import.

   Parameter	Description
   source_table_name	Name of the source table containing the objects to be synchronized.
   destination_table_name	Name of the target table receiving the synchronized objects.
   source_schema_name	Name of the source schema containing the objects to be synchronized.
   destination_schema_name	Name of the target schema receiving the synchronized objects.
   source_database_name	Name of the source database containing the objects to be synchronized.
   target_database_name	Name of the target database receiving the synchronized objects.
   column_list	List of columns to be synchronized.
   extra_configuration	Additional configuration information, where you can configure the following: Column Mapping: column_name, destination_column_name Column Value: column_value Data Filtering: filter_condition

   tip

   • An example of the extra_configuration content is as follows:

     {
         "column_name": "created_time", // Original column name to be mapped
         "destination_column_name": "migrated_time", // Target column name mapped to "migrated_time"
         "column_value": "current_timestamp()", // Change the column value to the current timestamp
         "filter_condition": "id != 0" // Only synchronize rows where ID is not 0.
     }

   • For a complete example of the configuration file, refer to the downloaded template.

6. On the tab, the system defaults to the database with the same name in the target data source. If it does not exist, you need to manually select the target library, and then click . If there are updates in the source and target data sources during the mapping configuration stage, you can click the button in the upper right corner of the page to re-obtain the information of the source and target data sources.

7. On the tab, wait for the system to complete the pre-check. After the pre-check is passed, click .

   tip

   • You can check . After the synchronization task is completed, it automatically starts the data consistency comparison based on the source data source to ensure the consistency of the data at both ends. According to your choice of , the start time of is as follows:
     • : 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-check is not passed, you need to click in the column on the right side of the target check item, troubleshoot the cause of the failure, manually fix it and click to re-execute the pre-check until it passes.
   • The check items with as can be repaired or ignored depending on the situation.

8. On the page, the prompt is displayed, and the synchronization task starts running. At this time, you can do the following:

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

Viewing Synchronization Results

1. Log in to the NineData Console.

2. In the left navigation bar, click > .

3. On the page, click the of the target synchronization task. The page explanation is as follows.

   

   No.	Function	Description
   1	Sync Delay	The data synchronization delay between the source and target data sources. 0 seconds means there is no delay between the two ends, at which point you can choose to switch your business to the target data source for a smooth migration.
   2	Configure Alarm	After configuring the alarm, the system will notify you in your chosen way if the task fails. For more information, see Operations Monitoring Introduction.
   3	More	: Pause the task. Only tasks with the status Running can be selected. : Create a new replication task with the same configuration as the current task. : End an incomplete or listening task (i.e., in incremental sync). After terminating the task, it cannot be restarted. Please proceed with caution. If the synchronization object contains triggers, the trigger replication options will pop up. Please select as needed. : Delete the task. Once deleted, the task cannot be recovered. Please proceed with caution.
   4	Full Copy (Displayed in the full copy scenario)	Show the progress and details of the full copy. Click on the right side of the page to view various monitoring indicators during the full copy process. During the full copy process, you can also click on the right side of the monitoring indicators page to limit the rate of writing to the target data source per second. The unit is rows/second. Click on the right side of the page to view the execution log of the full copy. Click on the right side of the page to view the latest information.
   5	Incremental Copy (Displayed in the incremental copy scenario)	Show various monitoring indicators of the incremental copy. Click on the right side of the page to view the operations being executed by the current replication task, including: : The replication task is executed in multiple threads, showing the current ongoing thread number. : Details of the SQL statement currently being executed by the thread. : Response time of the current thread. If this value increases, it means the current thread may be stuck for some reason. : Timestamp when the current thread was started. : Status of the current thread. Click on the right side of the page to limit the rate of writing to the target data source per second. The unit is rows/second. Click on the right side of the page to view the execution log of the incremental copy. Click on the right side of the page to view the latest information.
   6	Modify Objects	Show the modification records of the synchronization objects. Click on the right side of the page to configure the synchronization objects. Click on the right side of the page to view the latest information.
   7	Data Comparison	Show the comparison results between the source data source and the target data source. If you have not enabled data comparison, click on the page. Click on the right side of the page to re-initiate the comparison of the current source and target data. Click on the right side of the page to stop the comparison task immediately after it starts. Click on the right side of the page to view the execution log of the consistency comparison. Click (only displayed in data comparison) on the right side of the page to view the RPS (records per second) trend chart of the comparison. Click to view earlier records. In the column on the right side of the comparison list, click (displayed only in case of inconsistency on the tab) to view the comparison details between the source and target ends. In the column on the right side of the comparison list, click (displayed in case of inconsistency) to generate the change SQL. You can directly copy this SQL to the target data source for execution and modify the inconsistent content.
   8	View Reverse	Only displayed in bidirectional replication tasks. Click to view the replication details from the target data source to the source data source.
   9	Expand	Show detailed information of the current replication task.

Appendix: Adding a Multi-Active Tag to All Data Sources Involved in Replication

To prevent data loop replication, you need to add a multi-active tag to all data sources involved in the replication task.

1. Log in to the NineData Console.
2. In the left navigation bar, click > .
3. Click the target data source ID to open the page.
4. In the data source details area (which includes information such as data source name, ID, creator, creation time, etc.), click .
5. Find and click the icon on its right side.
6. Enter the multi-active tag and click .
   • The multi-active tag can contain 1-64 characters.
   • The multi-active tag must be globally unique and cannot duplicate other multi-active tags.

Appendix 2: Pre-Check Item List

Check Item	Description