Sybase and PostgreSQL Bidirectional Replication

Use this page to configure bidirectional replication between Sybase and PostgreSQL in NineData. New data written to PostgreSQL is replicated back to Sybase so both sides stay consistent.

Overview

NineData data replication supports schema, full data, and incremental data replication between data sources. For supported data sources, it also supports bidirectional replication for geo-distributed active-active architectures.

• Schema replication: Replicates object structures between homogeneous and heterogeneous data sources.
• Full data replication: Uses data sharding and row-level concurrent batch replication to improve throughput. Breakpoint resume helps preserve data accuracy, including for tables without primary keys.
• Incremental data replication: Replicates DML and DDL changes for supported object types. Row-level concurrency and hotspot merge processing help maintain replication throughput.
• Bidirectional real-time data replication (only between MySQL instances): Replicates changes in both directions between nodes so data can stay current across participating nodes.

Use these capabilities for full or incremental data replication, migration, synchronization, data integration, and low-downtime migration workflows.

Before you begin

• Add the source and target data sources to NineData. For details, see Creating Data Sources.

• The target tables have been created manually in PostgreSQL, and their structures must match the Sybase source tables to replicate. Since schema replication from Sybase to PostgreSQL is not currently supported, use the data type mapping and DDL suggestions in Sybase to PostgreSQL Replication 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

• For a detailed comparison of Sybase -> PostgreSQL, PostgreSQL -> Sybase, and bidirectional replication permissions and trigger behavior, see PostgreSQL and Sybase Replication Permission Guide.

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

  SET session_replication_role = 'replica';

Limitations

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

  

• Check the performance of both data sources before you start. Run replication during off-peak hours so full initialization does not overload the source or target.
• Make sure each synchronized table has a primary key or unique constraint, and keep column names unique to avoid duplicate rows.

Procedure

Step 1: Add a multi-active tag to all data sources involved in replication

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

1. Sign in to the NineData Console.

2. From the navigation menu, 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

1. Sign in to the NineData Console.

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

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure the fields in the 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.
   Datahub Project	Select the target Datahub Project. Data from the source data source will be written to the specified Project.
   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 completes, 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. 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. 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.
   Target Table Exists Data (Required when Full is selected)	Pre-Check Error and Stop Task: Stop the task when data is detected in the target table during the precheck stage. Ignore existing target data and append to it.: When data is detected in the target table during the precheck stage, ignore that data and append other data. Clear target existing data before write: When data is detected in the target table during the precheck stage, delete that data and write it again.
   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 parameters in the table, and click Next.

   Parameter	Description

   To create multiple replication tasks with the same replication objects, import a configuration file. Click Import Config, click Download Template to download the template, edit the file, and then click Upload to upload it and import the objects in bulk. The configuration file uses these fields:

   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. This field supports: 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

   • Example of extra_configuration:

     {
       "extra_config":{
         "column_rules":[
           {
              "column_name": "created_time",
              "destination_column_name": "migrated_time",
              "column_value": "current_timestamp()"
           }
         ],
          "filter_condition": "id != 0"
       }
     }

     In this example, created_time is mapped to migrated_time, the target column value is changed to current_timestamp(), and only rows whose id value is not 0 are synchronized.

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

6. On the "Mapping" tab, configure the mapping that matches the selected replication type, then click Save and Pre-Check. If source or target metadata changes while you configure mappings, click Refresh Metadata to refresh the metadata.

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

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

   Other available actions:

   • Click Mapping & Filtering to customize the column names after synchronization to the target data source.
   • On the Mapping & Filtering page, click Data Filter to configure filtering conditions by using comparison expressions. Only data that meets the filtering conditions 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 to quickly locate the target table.
   • Click Batch Configuration to define common rules in batches, such as table name and column name case conversion, prefix or suffix addition, and replacement. Use this option to apply mapping configuration to many tables and columns at the same time.

7. On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   • Select Enable data consistency comparison to start a data consistency comparison task based on the source data source after synchronization completes. Based on the selected Type, Enable data consistency comparison starts at these times:

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

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

   • Items with Warning in Result can be fixed or ignored if required.

8. On the Launch page, the Launch Successfully message appears, indicating that the synchronization task has started. Then perform these actions:

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

Results

1. Sign in to the NineData Console.

2. In the navigation menu, select Replication > Data Replication.

3. On the Replication page, click the Task ID of the target synchronization task. The task details page displays the following information.

   

   No.	Function	Description
   1	Sync Delay	The synchronization delay between the source and target data sources. 0 seconds means the target has caught up with the source. Switch traffic based on your migration plan.
   2	Configure Alarm	When the task fails, NineData notifies the selected channel through the configured alert.
   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 appear 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)	Shows the progress and details of the full copy. Select Monitor to view monitoring metrics during the full copy process. During full copy, select Flow Control Settings on the monitoring page to limit the number of rows written to the target data source per second. Select Log to view the execution log of the full copy. Select the to view the latest information.
   5	Incremental Copy (Displayed in the incremental copy scenario)	Shows various monitoring indicators of the incremental copy. Select View Threads 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. Select Flow Control Settings to limit the number of rows written to the target data source per second. Select Log to view the execution log of the incremental copy. Select the to view the latest information.
   6	Modify Objects	Shows the modification records of the synchronization objects. Select Modify Objects to configure the synchronization objects. Select the to view the latest information.
   7	Data Comparison	Shows the comparison results between the source and target data sources. If data comparison is not enabled, select Enable Comparison on the page to enable it. Select Re-compare to rerun the comparison for the current source and target data sources. Select Stop to stop the comparison task immediately after it starts. Select Log to view the execution log of the consistency comparison. Select Monitor (only displayed in data comparison) to view the RPS (records per second) trend chart of the comparison. Select Details to view earlier records. In the Actions column in the comparison list, select (displayed only when inconsistencies are found on the Data tab) to view the comparison details between the source and target data sources. In the Actions column in the comparison list, select (displayed when inconsistencies are found) to generate change SQL. Copy this SQL to the target data source and run it to fix the mismatch.
   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	Shows detailed information of the current replication task.

Appendix: Pre-check items

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