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. Since schema replication from Sybase to PostgreSQL is not currently supported, you can refer to the data type mapping and DDL suggestions in Sybase Data Migration to PostgreSQL when creating the target tables.

• 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

• If you need a cross-link comparison of Sybase -> PostgreSQL, PostgreSQL -> Sybase, and PostgreSQL -> PostgreSQL permissions and trigger strategy, see PostgreSQL and Sybase Replication Permission Guide.

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

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 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. In the left navigation pane, click Replication > Data Replication.

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

4. On the Source & Target tab, configure the parameters in the following table, and click Next.

   Parameter	Description
   Name	Enter a name for the data synchronization task. To make the task easier to find and manage later, use a meaningful name. Up to 64 characters are supported.
   Source	The data source that contains the objects to synchronize.
   Target	The data source that receives the synchronized objects.
   Target Object Name	Select the case conversion rule for object names after they are migrated from the source to the target. Convert all to Lowercase: Regardless of the naming rule on the source, all target names are lowercase. Consistent with Source: Keep the naming rule of the source. Convert all to Uppercase: Regardless of the naming rule on the source, all target names are uppercase.
   Replication Mode	Select the replication mode.Select Bi-directional here. Uni-directional: A unidirectional data replication task from the source data source to the target data source. Bi-directional: A bidirectional incremental data replication task between the source and target data sources.
   Forward Type	Required when Replication Mode is Bi-directional. Select the forward replication type. Full: Synchronize all objects and data from the source data source, namely full data replication. Incremental: After full synchronization is complete, perform incremental synchronization based on the logs of the source data source.
   Incremental Started	Required only when Type is Incremental. From Started: Use the current replication task start time as the baseline for incremental replication. Customized Time: Select the point in time from which incremental replication starts. You can select a time zone based on the region of your business. If the configured time point is earlier than the current replication task start time and DDL operations occurred during that period, the replication task will fail.
   Forward Incremental Started	Required only when Forward Type is Incremental. From Started: Use the current replication task start time as the baseline for incremental replication. Customized Time: Select the point in time from which incremental replication starts. You can select a time zone based on the region of your business. If the configured time point is earlier than the current replication task start time and DDL operations occurred during that period, the replication task will fail.
   Reverse Type	The replication type from the target data source to the source data source. This parameter is displayed only when Replication Mode is Bi-directional. Only Incremental is supported, and it is selected by default and cannot be disabled.
   Spec	The specification of the replication task. A larger specification provides a higher replication rate. Hover over the icon to view the rate and configuration information of each specification. If you configure the data replication task before purchasing resources, you can select the required specification here. If you purchase resources before configuring the task, the system selects the specification chosen during resource purchase, and you cannot change it in the task configuration.
   Incremental data conflict handling strategy for target table (Required when Incremental is selected)	Runtime error: During incremental replication, report an error when target data already exists and wait for manual intervention. Do not update target data: During incremental replication, do not write data when target data already exists, and continue subsequent tasks. Update target data: During incremental replication, overwrite the target data when target data already exists.

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

   Parameter	Description
   Replication Objects	Select the content to replicate. You can select All Objects to replicate all content in the source database, or select Customized Object, select the content to replicate in the Source Object list, and click > to add it to the Target Object list on the right.

   If you need to create multiple replication links with the same replication objects, you can create a configuration file and import it when creating a task. Click Import Config in the upper-right corner, click Download Template to download the configuration file template, edit the file, and then click Upload to upload it for batch import. Configuration file description:

   Parameter	Description
   source_table_name	The source table name of the object to synchronize.
   destination_table_name	The target table name that receives the synchronized object.
   source_schema_name	The source schema name of the object to synchronize.
   destination_schema_name	The target schema name that receives the synchronized object.
   source_database_name	The source database name of the object to synchronize.
   target_database_name	The target database name that receives the synchronized object.
   column_list	The list of columns to synchronize.
   extra_configuration	Additional configuration information. You can configure the following information here: column_rules: Defines column mappings and value rules. Field descriptions: column_name: Original column name. destination_column_name: Specifies the target column name. column_value: Specifies the column value, which can be an SQL function or a constant value. filter_condition: Specifies row-level data filtering conditions. Only rows that meet the conditions are replicated.

   tip

   • The following is an example of extra_configuration:

     {
       "extra_config":{
         "column_rules":[
           {
             "column_name": "created_time", // Specifies the original column name to be mapped.
             "destination_column_name": "migrated_time", // Maps the target column name to "migrated_time".
             "column_value": "current_timestamp()" // Changes the column value to the current timestamp.
           }
         ],
         "filter_condition": "id != 0" // Only rows whose ID is not 0 are synchronized.
       }
     }

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

6. On the "Mapping" tab, choose different operations based on the selected replication type, and click Save and Pre-Check. If the source and target data sources are updated during mapping configuration, click Refresh Metadata in the upper-right corner of the page to refresh the source and target data source information.

   • Includes Schema: Configure the table name after synchronization to the target data source.

   • Does not include Schema: The system selects the database with the same name in the target data source by default. If no such database exists, you need to manually select the target database. The table names and column names in the target database must match the synchronization objects. If they do not match, you can manually map the table names and column names.

   You can also perform the following operations:

   • Click Mapping & Filtering on the right side of the page to customize the column names after synchronization to the target data source.
   • On the Mapping & Filtering page, enter a comparison expression in the text box below Data Filter as the filtering condition. Only data that meets the filtering condition is synchronized to the target data source. For example, if the filtering condition is set to emp_no>=10005, data whose emp_no column value is less than 10005 is not synchronized to the target data source.
   • Click the icon to the right of "Target Table" to search for a table name and replace it with the target name.
   • Enter a table name in the Search Table text box in the upper-right corner to quickly locate the target table.
   • Click Batch Configuration in the upper-right corner to define common rules in batches, such as table name and column name case conversion, prefix or suffix addition, and replacement, so that mapping configuration can be applied uniformly to a large number of tables and columns.

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

   • You can select Enable data consistency comparison. After the synchronization task is complete, a data consistency comparison task based on the source data source automatically starts to ensure data consistency between both ends. Based on the selected Type, Enable data consistency comparison starts at the following times:

     • Full: Starts after full replication is complete.
     • Full+Incremental, Incremental: Starts when incremental data is consistent with the source data source for the first time and Delay is 0 seconds. You can click View Details and view the synchronization delay on the Details page.

   • If the precheck fails, click Details in the Actions column on the right of the failed check item, troubleshoot the cause, manually fix the issue, and then click Check Again to run the precheck again until it passes.

   • Check items whose Result is Warning can be fixed or ignored depending on the situation.

8. On the Launch page, the Launch Successfully prompt appears, indicating that the synchronization task has started. You can then perform the following operations:

   • Click View Details to view the execution status 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.
   3	More	Pause: Pause writes to the target while the source-side reader keeps running. Only tasks with the status Running can be selected. Pause Incremental Reader: Suspend the source-side reader. Use this only when the task is affecting source-side business workloads. If source logs are cleaned up during the suspension period, complete incremental changes may not be written to the target. 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 (that is, a task still in incremental synchronization). After termination, the task cannot be restarted. Proceed with caution. If the synchronization objects contain triggers, trigger-replication options will pop up for selection. Delete: Delete the task. Once deleted, the task cannot be recovered. 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 monitoring metrics during the full copy process. During full copy, you can also click Flow Control Settings on the right side of the monitoring page to limit the number of rows written to the target data source per 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 number of rows written to the target data source per 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	In bidirectional replication tasks, click to view the replication details from the target data source to the source data source.
   9	More	Show detailed information of the current replication task.

Appendix：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