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 Incremental task, with a specification of Micro.

• Tasks with a status of Terminated 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 Spec 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 Replication in the left navigation bar.

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

4. On the Source & Target tab, configure according to the following table and click Next.

   Parameter	Description
   Name	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.
   Source	The data source where the synchronization object is located.
   Target	The data source that receives the synchronization object.
   Replication Mode	Choose the replication method. Uni-directional: A one-way data replication task from the source data source to the target data source. Bi-directional: A two-way incremental data replication task between the source and target data sources.
   Type	Only displayed when Replication Mode is Uni-directional. Full: 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. Incremental: After the full synchronization is completed, perform incremental synchronization based on the log of the source data source.
   Forward Type	The replication type from the source data source to the target data source, only displayed when Replication Mode is Bi-directional. Among them, Incremental is selected by default and cannot be turned off.
   Forward Incremental Started	It needs to be selected when Replication Mode is Bi-directional and Forward Type is only Incremental. From Started: Perform incremental replication based on the current replication task start time. Customized Time: Choose the start time of incremental replication. You can choose the time zone according to your business location as needed.
   Reverse Type	The replication type from the target data source to the source data source, only displayed when Replication Mode is Bi-directional. Only supports Incremental, and it is selected by default and cannot be turned off.
   If target table already exists	Pre-Check Error and Stop Task: Stop the task when data is detected in the target table during the pre-check stage. Ignore the existing data and append : When data is detected in the target table during the pre-check stage, ignore this part of the data and append other data. Clear the existing data before write: 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 Objects tab, configure the following parameters, and then click Next.

   Parameter	Description
   Replication Objects	Choose the content to be copied. You can choose All Objects to copy all content of the source library, or you can choose Customized Object, select the content to be copied in the Source Object list, and click > to add it to the right Target Object 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 Import Config at the top right corner, then click Download Template to download the configuration file template to your local machine. After editing, click Upload 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 can be set here: column_rules: Used to define column mapping and value rules. Field descriptions: column_name: Original column name. destination_column_name: Target column name. column_value: Value to assign, which can be an SQL function or a constant. filter_condition: Used to specify row-level filtering conditions; only rows that meet the criteria will be copied.

   tip

   • An example of the extra_configuration content is as follows:

     {
       "extra_config": {
         "column_rules": [
           {
             "column_name": "created_time", // Original column name to map.
             "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 rows where ID is not 0 will be synchronized.
       }
     }

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

6. On the Mapping 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 Save and Pre-Check. If there are updates in the source and target data sources during the mapping configuration stage, you can click the Refresh Metadata button in the upper right corner of the page to re-obtain the information of the source and target data sources.

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

   tip

   • You can check Enable data consistency comparison. 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 Type, the start time of Enable data consistency comparison is as follows:
     • Full: Start after the full replication is completed.
     • Full+Incremental, Incremental: Start when the incremental data is consistent with the source data source for the first time and Delay is 0 seconds. You can click View Details to view the synchronization delay on the Details page. !sync_delay
   • If the pre-check is not passed, you need to click Details in the Actions column on the right side of the target check item, troubleshoot the cause of the failure, manually fix it and click Check Again to re-execute the pre-check until it passes.
   • The check items with Result as Warning can be repaired or ignored depending on the situation.

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

   • Click View Details to view the execution of each stage of the synchronization task.
   • Click Back to list to return to the Replication task list page.

Viewing Synchronization Results

1. Log in to the NineData Console.

2. In the left navigation bar, click Replication > Data Replication.

3. On the Replication page, click the Task ID 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: Pause the task. Only tasks with the status Running can be selected. Duplicate: Create a new replication task with the same configuration as the current task. Terminate: 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: 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 Monitor 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 Flow Control Settings 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 Log 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 View Threads on the right side of the page to view the operations being executed by the current replication task, including: Thread ID: The replication task is executed in multiple threads, showing the current ongoing thread number. Execute SQL: Details of the SQL statement currently being executed by the thread. Response Time: Response time of the current thread. If this value increases, it means the current thread may be stuck for some reason. Event Time: Timestamp when the current thread was started. Status: Status of the current thread. Click Flow Control Settings 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 Log 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 Modify Objects 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 Enable Comparison on the page. Click Re-compare on the right side of the page to re-initiate the comparison of the current source and target data. Click Stop on the right side of the page to stop the comparison task immediately after it starts. Click Log on the right side of the page to view the execution log of the consistency comparison. Click Monitor (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 Details to view earlier records. In the Actions column on the right side of the comparison list, click (displayed only in case of inconsistency on the Data tab) to view the comparison details between the source and target ends. In the Actions 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 Datasource > Datasource.
3. Click the target data source ID to open the Details page.
4. In the data source details area (which includes information such as data source name, ID, creator, creation time, etc.), click More.
5. Find Multi-Active Tag and click the icon on its right side.
6. Enter the multi-active tag and click OK.
   • 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
Source object existence check	Check whether the table on the target database currently exists on the source database
Source datasource connection check	Check the status of the gateway of the source datasource, database connectable, and verify the username and password
Target datasource connection check	Check the status of the gateway of the target datasource, database connectable, and verify the username and password
Target databse privilege check	Check whether the account privileges of the target database meet the requirements
Source database privilege check	Check whether the account privileges of the source database meet the requirements
Bi-directional multi-active tag check	Check whether the multi-active tag exists
Target database data existence check	Check whether the object to be replicated not empty in the target database
Objects with the same name in the target database existence check	Check whether the object to be replicated already exists in the target database
Cyclic replication check	Check for replication loops