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). Since schema replication from Sybase to PostgreSQL is not currently supported, you can adjust the target table structure based on business needs. For common data type mappings and DDL suggestions, see Appendix 2.

• 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

• For a detailed comparison of Sybase -> PostgreSQL, PostgreSQL -> Sybase, and the bidirectional replication scenario, including trigger behavior and grant examples, see PostgreSQL and Sybase Replication Permission Guide.

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.

Field Mapping and Field Expressions

• The target field must be an existing column in the PostgreSQL target table.
• In Configuration Mapping > Mapping and Filtering > Field Expression, you can configure field expressions for existing target columns.

For the entry, usage rules, and common writing patterns of field expressions, see ETL Functions.

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. 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. 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.
   Type	Required when Replication Mode is Bi-directional.Select the 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.
   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.
   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 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: 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: Data Type Mapping and Target Table Suggestions

Replication from Sybase to PostgreSQL does not currently support schema replication. The following content is intended as a reference for manual table creation. When creating target tables, you can adjust PostgreSQL column types and constraints based on field semantics, precision requirements, application compatibility, and query patterns.

Data Type Mapping Table

Category	Sybase Data Type	Recommended PostgreSQL Type	Optional Types and Notes
Character	CHAR(n)	CHAR(n) / CHARACTER(n)	Use this when fixed-length semantics need to be preserved.
Character	VARCHAR(n)	VARCHAR(n) / CHARACTER VARYING(n)	The length definition can usually be kept as-is.
Character	TEXT	TEXT	Suitable for long text fields.
Character	UNITEXT	TEXT	If length limits are still required, you can also use VARCHAR(n).
Character	UNICHAR(n)	CHAR(n)	If variable-length storage is preferred, you can also use VARCHAR(n).
Character	UNIVARCHAR(n)	VARCHAR(n)	Commonly used for variable-length Unicode strings.
Character	NCHAR(n)	NCHAR(n) / CHAR(n)	In PostgreSQL, you can also standardize on CHAR(n).
Character	NVARCHAR(n)	VARCHAR(n)	If you do not want to keep a length limit, you can also use TEXT.
Numeric	BIGINT	BIGINT	Suitable for signed 64-bit integers.
Numeric	UNSIGNED BIGINT	NUMERIC	PostgreSQL does not support unsigned integers; use NUMERIC or DECIMAL(p,0) to preserve a larger positive range.
Numeric	INT	INTEGER	Can also be written as INT or INT4.
Numeric	UNSIGNED INT	BIGINT	If the business range is known to fit, you may also downgrade it to INTEGER.
Numeric	SMALLINT	SMALLINT	Can also be written as INT2.
Numeric	UNSIGNED SMALLINT	INTEGER	Used to accommodate a larger positive range.
Numeric	TINYINT	SMALLINT	PostgreSQL does not provide TINYINT.
Numeric	DECIMAL(p,s)	NUMERIC(p,s)	In PostgreSQL, DECIMAL and NUMERIC can be used interchangeably if needed.
Numeric	NUMERIC(p,s)	NUMERIC(p,s)	Precision and scale can usually be kept unchanged.
Numeric	REAL	REAL	Maps to single-precision floating-point.
Numeric	FLOAT	DOUBLE PRECISION	Depending on precision and compatibility requirements, you can also use REAL or FLOAT8.
Numeric	DOUBLE PRECISION	DOUBLE PRECISION	Equivalent to FLOAT8.
Numeric	BIT	BIT(1)	If the field represents Boolean semantics, you can also use BOOLEAN.
Numeric	MONEY	MONEY	If you want unified precision and arithmetic behavior, you can also use NUMERIC(p,s).
Numeric	SMALLMONEY	MONEY	You can also switch to NUMERIC(p,s) based on business needs.
Binary	BINARY(n)	BYTEA	PostgreSQL uses BYTEA for both fixed-length and variable-length binary content.
Binary	VARBINARY(n)	BYTEA	Like BINARY(n), this is typically mapped to BYTEA.
Binary	IMAGE	BYTEA	Suitable for images, files, and other large binary objects.
Date and Time	DATE	DATE	Can usually be kept unchanged.
Date and Time	DATETIME	TIMESTAMP	If you want to be explicit, use TIMESTAMP WITHOUT TIME ZONE.
Date and Time	SMALLDATETIME	TIMESTAMP(0)	If second-level precision is not important, TIMESTAMP is also acceptable.
Date and Time	TIME	TIME	Can also be written as TIME WITHOUT TIME ZONE.
Date and Time	BIGDATETIME	TIMESTAMP(6)	If high precision is not required, TIMESTAMP is also acceptable.
Date and Time	BIGTIME	INTERVAL	If the field stores a time-of-day value, you can use TIME; if it stores a duration, INTERVAL is recommended.

Target Table Design Suggestions

Sybase Structure Feature	Suggested PostgreSQL Design	Notes
IDENTITY column	GENERATED BY DEFAULT AS IDENTITY / GENERATED ALWAYS AS IDENTITY / SERIAL / BIGSERIAL	Since the target table must be created manually, choose the auto-increment strategy based on the PostgreSQL version, writeback behavior, and application compatibility.
Primary key	Manually add PRIMARY KEY	It is recommended that synchronized tables retain a primary key. If that is not possible, keep at least a stable unique constraint.
Unique key	Manually add UNIQUE	Single-column and multi-column unique constraints both need to be created explicitly on the target side.
Nullable unique key	Decide whether to keep NULL based on business semantics	If you need to allow nulls while still enforcing uniqueness on non-null rows, consider a dedicated unique index design in PostgreSQL.
Normal index	Manually add CREATE INDEX	After creating the base table, add indexes based on query patterns as needed.
Foreign key	Manually add FOREIGN KEY	If you want to reduce constraint impact during full initialization, you can also add foreign keys after data initialization.
Naming rules	Preserve or adjust table and column names as needed	If you use reserved words, special characters, or case-sensitive names, it is recommended to use double quotes in PostgreSQL.

Appendix 3: 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