Bidirectional Replication Between Sybase and PostgreSQL

NineData Data Replication supports copying data from Sybase to PostgreSQL, and simultaneously, new data written to PostgreSQL will be replicated back to Sybase in real-time, achieving real-time consistency between the two ends.

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

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

• Tables have been manually created in the target data source (PostgreSQL), and the table structures must match those in the source data source (Sybase) that need to be replicated.

• 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
  Bidirectional Full and Incremental Replication	sa_role	Database Owner, Table Owner, All Privileges on Schema, Replication Role

• The target data source (PostgreSQL) has already set the session_replication_role parameter to replica. If it has not been set yet, please execute the following statement in the target data source:

  SET session_replication_role = 'replica';

Usage Restrictions

• If your Sybase data source is deployed in a primary-secondary architecture, 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.

Step 1：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.

Step 2: Create a Bidirectional Replication Task Between Sybase and PostgreSQL

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. In the left navigation bar, click .

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

4. On the tab, configure the following parameters and click .

   Parameter	Description
   	Enter the name of the data synchronization task. To facilitate subsequent search and management, use a meaningful name. Up to 64 characters are supported.
   	The data source where the synchronization object is located.
   	The data source that will receive the synchronization object.
   	Select .
   	The replication type from the source data source to the target data source. Keep the default setting. If you only need incremental replication, you can uncheck .
   	The replication type from the target data source to the source data source. It only supports and is selected by default and cannot be disabled.
   	: Stop the task if data is detected in the target table during the pre-check phase. : Ignore the data in the target table if detected during the pre-check phase and append other data. : Delete the data in the target table if detected during the pre-check phase and reinsert it.

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

   Parameter	Description
   	Select the content to be copied. You can choose to copy all content of the source database or select and add the items to the list by selecting them from the list and clicking >.

   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 automatically selects the database with the same name in the target data source. If it does not exist, you need to manually select the target database, then click . If there are updates to the source and target data sources during the mapping configuration, you can click the button in the upper right corner of the page to refresh the information.

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

   tip

   • You can check to automatically start a data consistency comparison based on the source data source after the synchronization task is completed, ensuring data consistency between the two ends. Depending on your selection of , the start timing of is as follows:

     • : Starts after the full replication is completed.
     • + , : Starts when the incremental data first matches the source data source and is 0 seconds. You can click to view the synchronization delay on the page.

   • If the pre-check fails, click in the column next to the target check item to troubleshoot the failure reason. After manual repair, click to re-execute the precheck until it passes.

   • Items in with can be repaired or ignored depending on the specific situation.

8. On the page, when prompted with , the sync task begins to run. At this point, you can perform the following operations:

   • Click on to view the execution status of each stage of the sync task.
   • Click on 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：Pre-Check Item List

Check Item	Description