Migrate data from a PostgreSQL database to OceanBase Database

This topic describes how to use OceanBase Migration Service (OMS) Community Edition to migrate data from a PostgreSQL database to OceanBase Database.

Background information

In the OMS Community Edition console, you can create a data migration task to migrate data from a PostgreSQL database to OceanBase Community Edition. You can seamlessly migrate existing business data and incremental data from the source database to OceanBase Community Edition by using schema migration, full migration, and incremental synchronization.

PostgreSQL databases support single primary database, single standby database, and primary-standby database modes. When you migrate data from a PostgreSQL database to OceanBase Community Edition, the supported operations vary depending on the type of data source.

Prerequisites

• You have created the corresponding schema in OceanBase Community Edition. OMS Community Edition supports migrating tables and columns. Therefore, you need to create the corresponding schema in OceanBase Community Edition.

• You have created dedicated database users for the data migration task in the source PostgreSQL database and OceanBase Community Edition, and granted the corresponding privileges to these users. For more information, see Create a database user.

• You have created dedicated database users for data migration in the source PostgreSQL database and OceanBase Database Community Edition that serves as the target, and granted required privileges to the users. For more information, see Create a database user.

• If you choose Incremental Synchronization > DDL Synchronization, you need to create the corresponding triggers and a table for recording DDL statements. For more information, see Create a trigger.

Limitations

• Limitations on the source database

  Do not execute DDL operations for changing the schema of a database or table during schema migration or full migration. Otherwise, the data migration task may fail.

• The supported PostgreSQL database versions are V10.x, V11.x, V12.x, V13.x, V14.x, V15.x, V16.x, and V17.x.

• OMS Community Edition allows you to migrate tables with or without a primary key and tables with non-null unique keys from a PostgreSQL database to OceanBase Database Community Edition.

• If a PostgreSQL database has read-only permissions, the data migration task will fail. You can execute the following statement to set the default_transaction_read_only parameter to off to disable the read-only permissions.

  ALTER ROLE <user_name> SET default_transaction_read_only TO off;
  

• If the target is a database, OMS Community Edition does not support triggers in the target database. If a trigger exists, the data migration may fail.

• When you migrate a partitioned table from a PostgreSQL database to OceanBase Community Edition, note the following limitations:

  • The schema of the parent table and child table must be the same.

  • The primary key column/NOT NULL UK column and partitioning column of the parent table and child table must be the same, or the primary key column/NOT NULL UK column must contain the partitioning column.

  • From the perspective of the parent table, the primary key/NOT NULL UK must be unique.

• The REPLICA IDENTITY of the parent table and child table to be migrated from the PostgreSQL database must be set to FULL.

  • If REPLICA IDENTITY is not set to FULL, business data update and delete operations may fail.

  • When you specify a wildcard in the migration object, the PostgreSQL database must subscribe to all tables in the selected database, including the selected tables, unselected tables, and newly added tables. The REPLICA IDENTITY of all tables must be set to FULL. Otherwise, business data update and delete operations may fail.

• OMS Community Edition does not support migrating unlogged tables and temporary tables from a PostgreSQL database.

• Data source identifiers and user accounts must be globally unique in the OMS system.

• OMS Community Edition supports migrating only databases, tables, and columns with ASCII names that do not contain special characters (|"'`()=;/& and line breaks).

• OceanBase Community Edition does not support specifying array subscripts, while PostgreSQL does. In PostgreSQL, the format is [start subscript: number of array elements]={x,x,x,x}.

  For example, in PostgreSQL, the array subscript is specified as [-1:10]={0,0,0,100,101,102,NULL,NULL,NULL,NULL,NULL,200}. When the data is written to OceanBase Community Edition, the subscript is replaced with 1. If your application depends on the subscript, the following issues may occur.

  • If a string array contains line breaks, an error will be returned.

  • PostgreSQL supports multidimensional arrays. For example, VARCHAR(255)[] can be used to write multidimensional array data, such as:

    • {{a,b},{c,d}}

    • {{{1,a},{2,b}},{{1,c},{2,d}}}

    • ...

    OceanBase Community Edition does not support multidimensional arrays. In this case, OMS Community Edition provides the following two solutions. You can choose one based on your business requirements.

    • Solution 1: Create an array of the same dimension as the data in the target field. For example, if the data in the PostgreSQL database is a two-dimensional array, create a two-dimensional array in OceanBase Community Edition.

    • Solution 2: Store the data in the target field as JSON data.

Considerations

• We recommend that you migrate no more than 1,000 tables at a time to avoid affecting the running of the data migration task.

• If a table has no primary key or all columns have the NOT NULL constraint and are unique keys, duplicate data may be generated during the data migration.

• In the reverse incremental scenario, data migration is performed by full-column matching for UPDATE and DELETE operations. This may cause the following issues.

  • Performance issues.

    Without a primary key index, each UPDATE and DELETE operation must perform a full table scan.

  • Data inconsistency issues.

    In PostgreSQL, the LIMIT clause is not supported for UPDATE and DELETE operations. If multiple rows are matched during full-column matching, the number of rows updated or deleted in the source database may be greater than that in the target database. For example, in a table t1 without a primary key, columns c1 and c2 exist. In the source database, two rows with c1=1 and c2=2 are stored. When one of the rows is deleted from the source database, the where c1 = 1 and c2 = 2 condition is used for matching. In this case, both rows with c1=1 and c2=2 in the target database are deleted. This causes data inconsistency between the source and target databases.

• In the reverse incremental migration from a PostgreSQL database to OceanBase Database Community Edition, if the target database is of a version earlier than V3.2.x and has a global unique index, data loss may occur during the data migration if you update the partitioning key values of the table.

• If you change the unique index of the target database, you must restart the incremental synchronization component. Otherwise, data inconsistency may occur.

• If the clocks of the nodes or the client and server are out of sync, the delay time (incremental synchronization or reverse incremental) may be inaccurate.

  For example, if the clock is earlier than the standard time, the delay time may be negative. If the clock is later than the standard time, the delay time may be positive.

• In the scenario of database and table aggregation:

  • We recommend that you map the relationships between the source and target databases by using an import object or a matching rule.

  • We recommend that you create the table structure in the target database. If you use OMS Community Edition to create the table structure, you must skip failed objects during the structure migration.

• If the table structures of the source and target databases are not completely consistent, data inconsistency may occur. The following scenarios are known to cause this issue:

  • When you manually create a table structure, implicit conversion issues may occur if the table structure exceeds the supported scope of OMS Community Edition. This may cause column types in the source and target databases to be inconsistent.

  • If the data length in the target database is shorter than that in the source database, data truncation may occur. This may cause data inconsistency between the source and target databases.

• If you configure only Incremental Synchronization when you create a data migration task, the local incremental logs of the source database must be retained for more than 48 hours.

  ​​​If you configure Full Migration + Incremental Synchronization when you create a data migration task, the local incremental logs of the source database must be retained for at least 7 days. Otherwise, the data migration task may fail because the incremental logs cannot be obtained, or data inconsistency may occur between the source and target databases.

Data type mapping

Procedure

1. Create a data migration task.

   1. Log in to the OMS Community Edition console.

   2. In the left-side navigation pane, choose Data Migration.

   3. On the Data Migration page, click Create Task in the upper right corner.

2. On the Select Source and Target page, configure the parameters.

   Parameter	Description
   Task name	You must use a combination of Chinese, English, and digits for the task name. The task name must not contain spaces and must be 64 characters in length or less.
   Label	Click the text field and select a target label from the drop-down list. You can also click Manage Tags to create, edit, or delete a label. For more information, see Manage data migration tasks by using tags.
   Source	If you have created a PostgreSQL data source, select the data source from the drop-down list. If not, click New Data Source in the drop-down list, and add the data source in the dialog box on the right. For more information, see Create a PostgreSQL data source. You can select a standalone or primary and standby PostgreSQL data source. In this example, a primary and standby PostgreSQL data source is selected.
   Endpoint	If you have created an OceanBase Community Edition data source, select it from the drop-down list. If you have not created a data source, click New Data Source in the drop-down list and create a data source in the dialog box that appears. For more information, see Create an OceanBase Community Edition data source.
   Scenario types	Include Tables with Unique Key and All Tables. A unique key table is a table that has a primary key or a non-null unique key. All tables include tables with primary keys, unique key tables, and tables without unique keys, but exclude temporary tables.

3. Click Next.

   If you select Tables with Unique Key as the migration type, you need to click Noted in the prompt that appears.

4. On the Select Migration Type page, configure the parameters.

   Migration Type is one of the following values: Schema Migration, Full Migration, Incremental Synchronization, Full Verification, and Reverse Increment.

   Type	Description
   Schema migration	After a schema migration task starts, the OMS Community Edition migrates data object definitions (tables, indexes, constraints, comments, and views, etc.) from the source database to the target database and automatically filters temporary tables. Manual Table Schema Adjustment Required: OMS Community Edition allows you to customize the schema. If you enable this parameter, the schema migration stage only retrieves the schema SQL statements. After you modify them, the migration proceeds. Target Table Storage Type: This parameter is displayed only if the target OceanBase Community Edition is V4.3.0 or later, and the Schema Migration option is selected. The possible values include Default, Row Storage, Column Storage, and Hybrid Row-Column Storage. This parameter specifies the storage type of the target table objects for schema migration or incremental synchronization. For more information, see default_table_store_format. Note When you specify Default, OMS Community Edition automatically adapts other options based on the target parameter configurations. During schema migration, the table objects are written according to the specified storage type.
   Full migration	After a full migration task is started, the OMS Community Edition migrates existing data from the source tables to corresponding target tables. If you select Full Migration, we recommend that you use the ANALYZE statement to collect statistics from the PostgreSQL database before the data is migrated.
   Incremental Synchronization	After an incremental synchronization task starts, the OMS Community Edition synchronizes only the data in the source database that is changed in the last 24 hours (added, modified, or deleted) to the corresponding tables in the target database. Incremental Synchronization includes DML synchronization and DDL synchronization. You can choose one or both. Incremental synchronization from a PostgreSQL database automatically creates a publication and a slot. You need to monitor the disk space for archived logs. The OMS Community Edition automatically updates the confirmed_flush_lsn of a slot every 10 minutes, and the interval period can be modified. By default, archived logs are stored for 48 hours and can be deleted if they are older than 48 hours. The OMS Community Edition automatically notifies you when you can delete these logs. During migration, if you cannot delete the archived logs due to a slot, you must delete the data migration task and then delete the archived logs.
   Full Verification	If you select Full Verification, we recommend that you collect the statistics of both the PostgreSQL database and OceanBase Database Community Edition before you perform the full verification. For details about how to collect the statistics of OceanBase Database Community Edition, see Manually collect statistics. If you select Incremental Synchronization, and not all DML statements are selected in the DML Synchronization option, OMS Community Edition does not support full data verification in this scenario. OMS Community Edition supports full data verification of a unique-key table, which is a table with a primary key or a non-null unique key.
   Reverse incremental migration	After a reverse incremental migration task is started, the task can synchronize the change data that is generated on the target database after a business switch back to the source database in real time. You cannot select Reverse Increment in the following situations: Multiple tables are involved. The schema is migrated from many schemas to one schema.

5. (Optional) Click Next. If you selected Reverse Increment and the Configurl, DRC username, and password for OceanBase Database Community Edition are not configured, the More about Data Sources dialog box appears. This dialog box reminds you to configure the required parameters. For more information, see Create an OceanBase Database Community Edition data source.

   After you add the supplementary information, click Test connectivity. If the test is successful, click Save.

6. Click Next, go to the Select Migration Objects page, and select the migration object and migration scope.

   You can use the Specify Objects and Match Rules options to select a migration object. This topic describes how to select a migration object by using the Specify Objects option. For more information about how to configure matching rules, see Configure matching rules for migration objects.

   Warning

   • The names of the tables and columns to be migrated cannot contain Chinese characters.

   • When the database name or table name contains the "$$" character, the creation of a data migration task will be affected.

   

   1. In the Select Migration Objects area, select Specify Objects.

   2. Select the objects to be migrated from the Source Object(s) list in the Specify Migration Scope area. You can select tables and views from one or more databases as migration objects.

   3. Click > and add it to the Target Object(s) list.

   During the migration of data from a PostgreSQL database to OceanBase Database, you can use OMS Community to create a text import task, rename objects, set row filters, view column information, and add or drop a migration object.

   Operation	Step
   Import objects	In the right-side list under the Specify Migration Scope section, click Import Objects in the upper right corner. In the dialog box that appears, click OK. Note: Importing operations will overwrite previous selections. Proceed with caution. In the Import Migration Objects dialog box, specify objects to be migrated. You can import a CSV file to rename the library and table, and set row filters. For more information, see Download and import migration object configurations. Click Validate. After the object validation is successful, click OK.
   Rename	The OMS Community Edition allows you to change the name of a migration object. For more information, see Rename a database or table.
   Feature	OMS Community Edition supports WHERE conditions to implement row filtering, for more information, see SQL Condition Filtering. You can also check the column information for the migrated object in the View Column area.
   Delete/All Delete	In OMS Standard Edition, you can delete individual objects or all objects selected for migration to the target. Delete an individual migration object You can hover the mouse pointer over the target object in the right-side list in the Specify Migration Scope section and click Remove that appears in the shortcut menu. This action deletes the migration object from the target. Delete all migration objects In the right-side list in the Specify Migration Scope section, click Remove All in the upper-right corner. In the dialog box that appears, click OK. All migration objects are deleted from the target.

7. Click Next. On the Migration Type page, configure the migration options.

   To view or modify parameters of the Full-Import or Full-Verify component, click Configuration Details in the upper-right corner of the Full Migration or Full Verification section. To view or modify parameters of the Incr-Sync or Reverse-Incr component, click Store Configuration Details or Configuration Details of incr Increment in the upper-right corner of the Incremental Synchronization or Reverse Increment section. For more information about the parameters, see Component parameters.

   • Full migration

     On the Select Migration Type page, choose Full Migration so that the following parameters are displayed.

     Parameter	Description
     Concurrency speed	The options are Stable, Normal, Fast, and Custom. The performance and required resources vary with full migration. When you select Custom, you can set Read Concurrency, Write Concurrency, and JVM Memory based on the actual situation.
     Action when records exist in target object	Actions: Ignore and Stop Migration: Select Ignore. If a target table object contains data that conflicts with the data to be written, OMS Community Edition will record the conflict data in the log and keep the original data unchanged for data writing. Notice When Ignore is selected, full validation will use the IN mode to fetch data and will not validate scenarios where data exists in the target object but not in the source. This may affect validation performance. Select Stop Migration. If a target table object contains data, the full migration will fail and the migration will stop. Please address the target object data issues and proceed with the migration again. Notice When you select Ignore and click Restore, OMS Community Edition will continue to migrate table data regardless of the selected action. Be cautious when performing this action.
     Write mode	SQL (INSERT or REPLACE operations) and direct load (parallel import). For more information about direct load, see Parallel import.
     Allow index creation after migration	You can specify whether to allow indexes to be created after the full data migration is completed. Index creation after migration can reduce the time required for full data migration. For information about the limitations and requirements on selecting this option, see the descriptions below the table. Notice This option is available when both Schema Migration and Full Migration are selected on the Migration Type page.

     If you enable index post-processing, you must manually tune the parameters based on the hardware and workload of OceanBase Database Community Edition.

     • If you use OceanBase Community Edition V4.x, use the black command line client to modify the parameters of the sys tenant and the business tenants.

       • Modify parameters of the sys tenant.

         // parallel_servers_target defines the maximum number of parallel queries per server.
         </think>
         // parallel_servers_target defines the maximum number of parallel queries per server.
         // For performance purposes, we recommend that you set this parameter to a value greater than the number of physical CPU cores. This value must not exceed 64 to avoid contention issues in OceanBase Database.
         
         

       set global parallel_servers_target = 64;

       ```
       * Adjust the business tenant parameters.
       ```sql
       // File memory buffer limit
       alter system set _temporary_file_io_area_size = '10' tenant = 'xxx';
       // 4.x Disable flow control.
       

     alter system set sys_bkgd_net_percentage = 100;

     ```
     * If you are using OceanBase Database V3.x in the Community Edition, adjust the parameters of the sys tenant in the CLI tool.
     ```sql
     // parallel_servers_target specifies the maximum number of parallel queries on each server.
     // To improve performance, we recommend that you increase this parameter to a value greater than the number of physical CPU cores, for example, 1.5 times that number. However, the value must not exceed 64 to avoid lock contention issues in OceanBase Community Edition.
     set global parallel_servers_target = 64;
     // data_copy_concurrency specifies the maximum number of concurrent data migration replication tasks that can run concurrently in the system.
     

   alter system set data_copy_concurrency = 200;

   
   * Incremental synchronization
   On the **<UI-TERM key="oms-community.Migration.New.SelectMigrationType">Select Migration Type</UI-TERM>** page, the following parameters appear when **<UI-TERM key="oms-community.Migration.constants.IncrementalMigration">Incremental Synchronization</UI-TERM>** is selected.
   |Parameter|Description|
   |----|----------------------------------|
   | Throughput | Smooth, Normal, Quick, and Custom. Performance varies for incremental synchronization, and the resources required vary. When you select Custom, you can set the values for the read concurrency, write concurrency, and JVM memory as needed. |
   
   |Incremental synchronization start point| <ul><li> If you have selected the **<UI-TERM key="oms-community.Migration.constants.FullMigration">Full Migration</UI-TERM>** option for data migration type, this parameter is not displayed.   <li> If you have not selected the **<UI-TERM key="oms-community.Migration.constants.FullMigration">Full Migration</UI-TERM>** option for data migration type, but selected the **<UI-TERM key="oms-community.New.components.MigrationObjectForm.IncrementalMigration">Incremental Synchronization</UI-TERM>** option, specify the start point for data migration to a timestamp after a certain point. By default, the start point is set to the current system time. For more information, see [Set the start point](../600.data-migration/1600.migration-function-introduction/1000.incremental-synchronization-timestamp.md).  |
   
   |Incremental record retention time|The incremental records in the cache of the OMS Community Edition are retained for the specified period. If a longer period is specified, more disk space is needed by the Store component of the OMS Community Edition.  |
   
   * Full data verification
   On the **<UI-TERM key="oms-community.Migration.New.SelectMigrationType">Select Migration Type</UI-TERM>** page, select **<UI-TERM key="oms-community.Migration.constants.FullVerification">Full Verification</UI-TERM>** to display the following parameters.
   |Parameter|Description|
   
   |----|----------------------------------|
   
   | Concurrency | The options are **<UI-TERM key="oms-community.Migration.constants.Smooth">Stable</UI-TERM>**, **<UI-TERM key="oms-community.Notification.Setting.AlarmChannel.Normal">Normal</UI-TERM>**, **<UI-TERM key="oms-community.New.components.ParameterForm.Quick">Fast</UI-TERM>**, and **<UI-TERM key="oms-community.pages.Audit.Custom">Custom</UI-TERM>**. The resource allocation for the Full-Verification component controls resource consumption during the full verification phase of the task. If you select **<UI-TERM key="oms-community.pages.Audit.Custom">Custom</UI-TERM>**, you can customize parameters such as **<UI-TERM key="oms-community.migration.new.components.communityForm.readConcurrency">Read Concurrency</UI-TERM>**, **<UI-TERM key="oms-community.migration.new.components.communityForm.writeConcurrency">Write Concurrency</UI-TERM>**, and **<UI-TERM key="oms-community.migration.new.components.communityForm.jvmMemory">JVM Memory</UI-TERM>** based on your requirements. |
   
   * Reverse Incremental
   On the **<UI-TERM key="oms-community.Migration.New.SelectMigrationType">Select Migration Type</UI-TERM>** page, select **<UI-TERM key="oms-community.Migration.constants.ReverseIncrement">Reverse Increment</UI-TERM>** to display the following parameters.
   |Parameter|Description|
   |----|----------------------------------|
   | Concurrency | Specifies the **<UI-TERM key="oms-community.New.components.ParameterForm.Smooth">Stable</UI-TERM>**, **<UI-TERM key="oms-community.Notification.Setting.AlarmChannel.Normal">Normal</UI-TERM>**, **<UI-TERM key="oms-community.New.components.ParameterForm.Quick">Fast</UI-TERM>**, and **<UI-TERM key="oms-community.pages.Audit.Custom">Custom</UI-TERM>** mode. The performance and resources needed of incremental synchronization vary. If you select **<UI-TERM key="oms-community.pages.Audit.Custom">Custom</UI-TERM>**, you can set the **<UI-TERM key="oms-community.migration.new.components.communityForm.readConcurrency">Read Concurrency</UI-TERM>**, **<UI-TERM key="oms-community.migration.new.components.communityForm.writeConcurrency">Write Concurrency</UI-TERM>**, and **<UI-TERM key="oms-community.migration.new.components.communityForm.jvmMemory">JVM Memory</UI-TERM>** values based on your requirements. |
   
   * Reverse increment
   
   On the **<UI-TERM key="oms-community.Migration.New.SelectMigrationType">Select Migration Type</UI-TERM>** page, select **<UI-TERM key="oms-community.New.components.MigrationObjectForm.ReverseIncrement">Reverse Increment</UI-TERM>** to display the following parameters.
   
   |Parameter|Description|
   |----|----------------------------------|
   | Concurrency Speed | Valid values: **<UI-TERM key="oms-community.New.components.ParameterForm.Smooth">Stable</UI-TERM>**, **<UI-TERM key="oms-community.Notification.Setting.AlarmChannel.Normal">Normal</UI-TERM>**, **<UI-TERM key="oms-community.New.components.ParameterForm.Quick">Fast</UI-TERM>**, and **<UI-TERM key="oms-community.pages.Audit.Custom">Custom</UI-TERM>**. The performance of incremental synchronization varies, and the resources required for incremental synchronization tasks also vary. If you select **<UI-TERM key="oms-community.pages.Audit.Custom">Custom</UI-TERM>**, you can set **<UI-TERM key="oms-community.migration.new.components.communityForm.readConcurrency">Read Concurrency</UI-TERM>**, **<UI-TERM key="oms-community.migration.new.components.communityForm.writeConcurrency">Write Concurrency</UI-TERM>**, and **<UI-TERM key="oms-community.migration.new.components.communityForm.jvmMemory">JVM Memory</UI-TERM>** as needed. |
   | Incremental Synchronization Start Timestamp | <ul><li> This parameter is displayed only when you select **<UI-TERM key="oms-community.New.components.MigrationObjectForm.ReverseIncrement">Reverse Increment</UI-TERM>** as the migration type. Specify the data to be migrated after a certain point in time. The default value is the current system time. For more information, see [Set the Incremental Synchronization Start Point](../600.data-migration/1600.migration-function-introduction/1000.incremental-synchronization-timestamp.md).   |
   | Incremental Record Retention Time | The duration for which incremental parsing files are cached in OMS Community Edition. The longer the retention time, the more disk space the OMS Community Edition Store component consumes.  |
   
   

8. Click Precheck to start a precheck on the data migration task.

   |Incremental record retention time|The length of time that incremental parsing files are cached by the OMS Community Edition. A longer retention time requires more disk space in the OMS Community Edition Store component. |

   8. On the left of the Precheck tab, click Precheck to check the preconditions for the data migration task.

   In the Precheck section, OMS Community Edition checks whether the database user has read and write permissions and whether the network connection is available. All check tasks are completed before the migration tasks can be started. If a check task fails, an error is returned and the migration cannot be started. Example:

• After you troubleshoot and resolve an issue, reperform the precheck until it succeeds.

  * You can also click **<UI-TERM key="oms-community.components.BatchSelect.BatchActions.Skip">Skip</UI-TERM>** in the Actions column of the error pre-check task, a dialog box appears to prompt you for the effect of skipping this action. After you click **<UI-TERM key="oms-community.Detail.components.UpdateSyncObj.Determine">OK</UI-TERM>** in the dialog box, you can skip the action.
  

References

• Create a PostgreSQL data source

• Create an OceanBase Database Community Edition data source

• Component parameters

• View and modify migration objects

• View details of a data migration task