Migrating from PolarDB PostgreSQL to OceanBase (ob_oracle): A Comprehensive Guide

1. Background

The customer needs to migrate PolarDB PostgreSQL 11.9 (referred to as polardb_pg ) to OceanBase 4.2.1.10 Oracle tenant (referred to as ob_oracle ). The current OMS does not support this migration, so a manual solution is required.

2. Overview of Steps

Migration Scope

Table structure + full data migration + full data verification.

Task Assignment

Tool dbcat (version 2.1.1) is used for converting objects (tables, views, sequences). The conversion capabilities are limited to these objects; other objects must be handled manually.

User migration – manual creation

Role migration – script processing

Permission migration – script processing

Table structure migration – dbcat tool

View migration – dbcat tool

Sequence migration – dbcat tool

Foreign‑key migration – dbcat + script

Other objects – business side migration

Data migration – DataX

Data verification – script processing

3. User Migration

One PolarDB user may own multiple schemas.

One OceanBase user can own only one schema (username = schema).

Typically, schema_name on the PolarDB side becomes the username on the OceanBase side.

Special cases such as the default PUBLIC role must be renamed.

Roles owned by the PolarDB user also need to be migrated.

4. Permission Conversion

OceanBase permissions are divided into object and system permissions, while PolarDB permissions have a finer granularity.

Conversion Idea

Identical permissions are granted directly without conversion.

PolarDB‑specific permissions are mapped to the closest OceanBase equivalents (e.g., TRUNCATE becomes DROP on the target table).

5. Table‑Structure Conversion

5.1 dbcat Performance Tuning

Adjust JVM options according to the machine memory:

-Xms8G : initial heap size 8 GB.

-Xmx16G : maximum heap size 16 GB.

JAVA_OPTS="$JAVA_OPTS -Xms8G -Xmx16G -XX:+UseG1GC -XX:MetaspaceSize=256M -XX:MaxMetaspaceSize=256M"

5.2 Quotation Issue

Problem

dbcat adds double quotes to database, table, column and constraint names. In OceanBase, quoted identifiers preserve case and may cause "object not found" errors.

Solution

Add the --no-quote flag to dbcat to generate DDL without quotes:

dbcat convert -H 10.186.64.61 -P 5432 -u $pg_user -p $password -D postgres \
  --schema $schema --from pgsql11 --to oboracle420 --no-quote --table '*' \
  -f /dbcat_path > dbcat_all_tables_$(date +%Y%m%d_%H%M%S).log 2>&1

5.3 Foreign Keys

Problem

Foreign‑key DDL is generated together with table DDL; creating foreign keys before data migration can cause failures.

Solution

Extract foreign‑key statements to a separate file and apply them after data migration. Two methods are provided:

For a small number of foreign keys, create them directly after data load.

For many foreign keys, use ENABLE NOVALIDATE to create them without validating existing data.

ALTER TABLE $pg_user.$table_name ADD CONSTRAINT "$fk_name" FOREIGN KEY ("$fk_column") \
  REFERENCES $pg_user.$table_name ("$primary_column") ENABLE NOVALIDATE;

5.4 Tables Containing oid Columns

PolarDB tables with oid type are ignored by dbcat. Convert the column to bigint before conversion.

5.5 Indexes Based on oid

OceanBase does not support oid indexes; remove such index definitions from the converted DDL.

5.6 Boolean Field Conversion

dbcat converts PolarDB boolean to OceanBase NUMBER(1) . To avoid insert errors, change the target column type to CHAR(5) and adjust application SQL (e.g., use 't' instead of t ).

select id from table1 where flag = 't';

6. DataX Data Migration

6.1 DataX Performance Tuning

Adjust JVM options for DataX:

-Xms16g : initial heap 16 GB.

-Xmx32g : maximum heap 32 GB.

DEFAULT_JVM = "-Xms16g -Xmx32g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=%s/log" % (DATAX_HOME)

Key JSON configuration points:

Specify splitPK for tables with primary keys.

Composite primary keys are treated as tables without primary keys.

{
  "job": {
    "setting": {
      "speed": {"channel": 32},
      "errorLimit": {"record": 1000, "percentage": 0}
    },
    "content": [{
      "reader": {
        "name": "postgresqlreader",
        "parameter": {
          "username": "$pg_user",
          "password": "$password",
          "column": ["id", "version"],
          "connection": [{
            "table": ["$schema.$table_name"],
            "jdbcUrl": ["jdbc:postgresql://10.186.64.61:5432/postgres?useUnicode=true&characterEncoding=utf8"]
          }],
          "splitPK": "id"
        }
      },
      "writer": {
        "name": "oceanbasev10writer",
        "parameter": {
          "obWriteMode": "insert",
          "column": ["id", "version"],
          "connection": [{
            "table": ["$table_name"],
            "jdbcUrl": ["jdbc:oceanbase://10.186.64.161:2883/$schema_name?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true"]
          }],
          "username": "SYS@ora_tenant#cluster",
          "password": "$password",
          "writerThreadCount": 10,
          "batchSize": 1000,
          "memstoreThreshold": "0.9"
        }
      }
    }]
  }
}

6.2 Handling Migration Exceptions

DataX may repeatedly retry failing rows without terminating the job. A monitoring script checks the row count of the current table every second; if the count does not change for five minutes, the script kills the corresponding sub‑task and logs the event.

[2025-04-17 10:23:18] ------------------------
[2025-04-17 10:23:24] 开始检查
[2025-04-17 10:23:24] 总表数量: 4454
[2025-04-17 10:23:24] 当前表: $table_name
[2025-04-17 10:23:24] 第 4228 个表
[2025-04-17 10:23:24] （OB数据库）查询记录数: 0
[2025-04-17 10:23:24] 检查表行数和 kill 卡住的表_脚本执行剩余时间: 55小时 11分钟

7. Performance Monitoring

PolarDB PostgreSQL platform monitoring.

OceanBase OCP platform monitoring.

Host resource monitoring.

8. Conclusion

Heterogeneous database migration requires careful comparison of source and target features; validate the solution in a test environment before production.

For non‑incremental migration, stop business operations and lock users on both sides to guarantee data consistency.

References

[1] PolarDB PostgreSQL: https://www.aliyun.com/product/apsaradb/polardbpg

[2] OceanBase Enterprise Edition: https://www.oceanbase.com/product/oceanbase

[3] OceanBase Migration Service (OMS): https://www.oceanbase.com/product/oms

[4] dbcat documentation: https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-1000000000749015

[5] DataX project: https://github.com/alibaba/datax

[6] ob_oracle permissions: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220864

[7] polardb_pg permissions: https://help.aliyun.com/zh/polardb/polardb-for-oracle/warpaman72ax09gk