As business systems continue to scale, traditional single-node or primary–standby database architectures increasingly struggle to meet combined requirements for high availability, elastic scalability, and cost efficiency. OceanBase, with its MySQL-compatible mode, delivers clear advantages in distributed architecture, high availability, and storage efficiency, making it a compelling choice for PostgreSQL users seeking to modernize their database architecture.
Based on a real-world migration of a core transaction system in the insurance industry, this article summarizes the key technical differences, risk areas, and mitigation strategies encountered when migrating from PostgreSQL to OceanBase MySQL-compatible mode. The goal is to provide a reusable and practical reference for similar migration projects.
Migration case overview
The migration target in this case is an order-management system for an insurance platform. The database stores core transaction and order data, placing extremely high demands on system stability and data consistency. The migration needed to be completed without service interruption and with fast rollback capabilities.
The migration scope is summarized below:
- Source database: PostgreSQL 13.2
- Data volume: ~8 TB, approximately 500 million rows
- Tables migrated: 30+
- Sharded tables consolidated into a single table: 10+
Note
In the original PostgreSQL system, a single logical table was split into multiple physical tables with identical schemas (for example, table_0, table_1, ..., table_n). During migration to OceanBase MySQL-compatible mode, data from these sharded tables was consolidated into a single target table for unified storage and management.
- Partitioned tables: 5+
- Regular tables: 10+
The migration covers both standard business tables and more complex scenarios such as partitioned tables and the consolidation of previously sharded tables into a single table, which places higher requirements on the completeness and adaptability of the migration solution.
Migration assessment: defining cost and risk boundaries
Before initiating a migration from PostgreSQL to OceanBase MySQL-compatible mode, it is critical to assess object compatibility and transformation risks to minimize uncertainty during execution.
In this case, the OceanBase Migration Assessment (OMA) tool was used to automatically evaluate PostgreSQL database objects, including tables, indexes, views, sequences, and stored procedures. By providing only database connection information, users can generate a comprehensive compatibility assessment report.
Below is a sample OMA assessment report:
SCHEMA : test Assessment Time : 47895 ms
+---- ---------------------------------------------------------------+
| schema: schema | source: sourceDB | target: targetDB |
+-----------------+---------+---------+---------+---------+---------+
| Object Type | pass | convert | failure | total | percent |
+-----------------+---------+---------+---------+---------+---------+
| TABLE | 186 | 0 | 1 | 187 | 99.5% |
| PROCEDURE | 4 | 0 | 0 | 4 | 100.0% |
| TRIGGER | 0 | 0 | 1 | 1 | 0.0% |
| PACKAGE | 1 | 0 | 0 | 1 | 100.0% |
| SEQUENCE | 2 | 0 | 0 | 2 | 100.0% |
| INDEX | 127 | 0 | 0 | 127 | 100.0% |
| VIEW | 9 | 0 | 0 | 9 | 100.0% |
| PACKAGE BODY | 1 | 0 | 0 | 1 | 100.0% |
+-------------------------------------------------------------------+
Note
The report above is provided only to illustrate OMA's assessment capabilities and report format. It does not represent actual production data from this case. Actual results depend on the user's database environment.
This assessment enables teams to quickly understand compatibility at the object level and identify items requiring manual intervention, forming a solid basis for migration planning.
It is important to note that OMA's current PostgreSQL assessment scope is limited to database objects and does not include application-level SQL compatibility analysis. Therefore, in addition to OMA assessment, application SQL compatibility analysis must be performed in parallel to provide stable and predictable inputs for subsequent migration and refactoring.
Overall migration strategy
Prior to execution, this migration emphasized extensive preparation and validation to ensure controllability, rollback safety, and minimal business impact. The overall approach followed a standard four-phase workflow:Preparation → Migration → Validation → Cutover.
Preparation before migration
Pre-migration preparation is the foundation of a successful migration and includes environment readiness, permission configuration, and replication prerequisites.
Environment readiness
In this case, the source, target, and migration tool environments met the following requirements:
Category Requirements Source (PostgreSQL) - Version ≥ 10.x
wal_levelset tologicalto support logical replication- Cluster size: 64C × 6, deployed with dual-active + primary-standby + DR for high availability
Target (OceanBase) - MySQL-compatible tenant created
- Cluster size: 96C × 4, multi-zone deployment for high availability and scalability
Migration Tool (OMS) - OMS v4.2.5 BP1 deployed
- Supports schema migration, full migration, incremental sync, data validation, and reverse synchronization
User permissions and replication prerequisites
- The PostgreSQL migration user must have database access privileges and required logical replication permissions (including REPLICATION).
- On the OceanBase side, a dedicated user with appropriate target-database privileges must be created for OMS.
To ensure UPDATE and DELETE operations are correctly captured during replication, tables must meet replica identity requirements:
- The table has a primary key, or
REPLICA IDENTITYis set toFULL
In practice, it is strongly recommended to enable REPLICA IDENTITY FULL for all migrated tables to avoid data loss during incremental synchronization.
Migration execution flow
This migration followed the standard OMS execution pipeline:
- Schema migration: OMS automatically migrates base table structures and indexes. Objects that OMS does not support (for example, certain partitioned tables) are manually created and adjusted on the target side.
- Full migration: Existing data from PostgreSQL is imported into OceanBase, while incremental logs continue to be captured throughout the process.
- Incremental synchronization: Real-time synchronization based on PostgreSQL WAL logs ensures continuous consistency between source and target during migration.
- Data validation: OMS built-in validation tools are used for multiple rounds of comparison, progressively eliminating discrepancies.
- Cutover and reverse synchronization: Forward cutover is performed during a low-traffic window with minimal business downtime, followed by enabling reverse incremental synchronization to preserve rollback capability.
This workflow has been validated in multiple production environments and supports low-risk cutover with fast rollback. Detailed procedures and parameters can be found in the official documentation: Migrate data from a PostgreSQL database to the MySQL compatible mode of OceanBase Database.
Note
During PostgreSQL-to-OceanBase migration, certain data types may undergo mapping or semantic adjustments, for example, PostgreSQL BIGINT → OceanBase MySQL BIGINT and PostgreSQL BIGSERIAL → OceanBase MySQL BIGINT (with AUTO_INCREMENT semantics).
While these mappings generally do not affect data range, they may influence auto-increment behavior, default values, or application logic and should be validated after migration. For complete mappings, see the appendix: PostgreSQL to OceanBase MySQL-compatible data type mapping.
Adaptation challenges and solutions during migration
This section summarizes key adaptation challenges encountered in real migrations, covering mechanism differences, tooling limitations, and runtime behavior, and provides corresponding mitigation strategies.
Schema, auto-increment, and data type conversion
| Issue | Description | Solution |
|---|---|---|
| Auto-increment not working | Integer auto-increment columns do not increment after migration. | Two root causes were identified:
|
| Abnormal default values for timestamp columns | DEFAULT 'now'::text::... is migrated as a literal string. |
Manually convert defaults to CURRENT_TIMESTAMP(). Automatic conversion will be supported in OMS 4.3.2. |
| Partitioned tables not migrated automatically | PostgreSQL partition definitions differ from MySQL semantics. | Manually create partitioned tables in OceanBase before data migration. |
| DEFAULT USER not supported | PostgreSQL system functions are unavailable in MySQL mode. | Replace with static defaults or equivalent trigger-based logic. |
PostgreSQL vs. MySQL SQL syntax differences
| Issue | Description | Solution |
|---|---|---|
| Function differences | PostgreSQL-specific functions such as uuid_generate_v4() and string_agg() are not supported. |
Replace with equivalent functions supported in OceanBase MySQL-compatible mode. |
| Upsert syntax incompatibility | PostgreSQL ON CONFLICT is not supported. |
Rewrite as INSERT ... ON DUPLICATE KEY UPDATE. |
ON CONFLICT DO NOTHING not supported |
MySQL does not support this syntax. | Use INSERT IGNORE INTO to achieve the same behavior. |
Synchronization-related issues
| Issue | Description | Solution |
|---|---|---|
| Auto-increment conflicts during reverse sync | Writes generated on the target side may conflict when synced back. | Remove auto-increment constraints on PostgreSQL using ALTER TABLE ... DROP IDENTITY; generate primary keys exclusively in OceanBase. |
| Limited automatic DDL sync | Some DDLs are not synchronized automatically by OMS. | Freeze schema changes during migration; manually synchronize DDLs before and after migration. |
Character set and case sensitivity
| Issue | Description | Solution |
|---|---|---|
| Quoted identifier behavior | Queries using "a" behave differently. |
Standardize on lowercase, unquoted identifiers. |
| Case sensitivity mismatch | PostgreSQL is case-sensitive, but OceanBase MySQL-compatible mode is not by default. | Explicitly specify CHARSET=utf8mb4 COLLATE=utf8mb4_bin. |
Additional practical considerations
| Issue | Description | Solution |
|---|---|---|
| Reverse sync for merged tables | OMS cannot infer write-back logic for merged shards. | Handle reverse writes at the application layer. |
| Data loss during incremental sync | UPDATE is translated as DELETE + INSERT. |
Set REPLICA IDENTITY FULL for all tables. |
| JDBC timeout unit mismatch | PostgreSQL uses seconds, but MySQL uses milliseconds. | Align application connection parameters accordingly. |
Post-migration validation and operational results
Data consistency validation
A multi-layer validation strategy was applied:
- OMS full validation for row counts and column values
- Multiple validation rounds to progressively eliminate discrepancies
- Manual sampling for critical business tables
All discrepancies were fully resolved prior to cutover.
Production results
After cutover, the system stabilized with the following metrics:
- Reverse sync latency: < 5 seconds
- CPU utilization: ~7% average, ~17% peak
- Peak QPS: 12,616
- Peak TPS: 12,285
- Storage usage:
- PostgreSQL: ~24 TB
- OceanBase: ~8 TB
- Compression ratio: ~3:1
No slow queries or abnormal transactions were observed.
Reusable migration experience summary
Key success factors
- Thorough pre-migration assessment
- Early adaptation of known differences
- Strict, multi-round data validation
- Smooth cutover with reverse sync safety
- Coordinated application-side changes
Key risks
- Reverse sync unsupported for merged-table scenarios
- Character set and case-sensitivity discrepancies
- Improper handling of auto-increment and sequences
Recommended pre-migration checklist
wal_level=logicalenabled- Migration user has REPLICATION privileges
- All tables set to
REPLICA IDENTITY FULL - OMS heartbeat table permissions configured
- No naming conflicts on the target side
- Auto-increment, defaults, and partitions reviewed
- Application SQL and JDBC parameters adapted
- Reverse incremental sync verified
Conclusion
Migrating from PostgreSQL to OceanBase MySQL-compatible mode is a system-level engineering effort involving close coordination across database, tooling, and application layers. Success depends not on whether migration is technically possible, but on whether differences are fully identified, risks are clearly bounded, and adaptations are made proactively.
By following a standardized migration workflow and addressing key challenges upfront, organizations can achieve zero data loss, minimal service disruption, and rollback-safe cutover, laying a solid foundation for future scalability and performance optimization.
Appendix: PostgreSQL to OceanBase MySQL-compatible data type mapping
| PostgreSQL | OceanBase MySQL-compatible |
|---|---|
| bigint | BIGINT |
| bigserial | BIGINT |
| bit [ (n) ] | BIT |
| boolean | TINYINT(1) |
| box | POLYGON |
| bytea | LONGBLOB |
| character [ (n) ] | CHAR LONGTEXT |
| character varying [ (n) ] | VARCHAR MEDIUMTEXT LONGTEXT |
| cidr | VARCHAR(43) |
| circle | POLYGON |
| date | DATE |
| double precision | DOUBLE |
| inet | VARCHAR(43) |
| interval [ fields ] [ (p) ] | TIME |
| json | LONGTEXT JSON |
| jsonb | LONGTEXT JSON |
| line | LINESTRING |
| lseg | LINESTRING |
| macaddr | VARCHAR(17) |
| money | DECIMAL(19,2) |
| numeric [ (p, s) ] | DECIMAL |
| path | LINESTRING |
| real | FLOAT |
| smallint | SMALLINT |
| smallserial | SMALLINT |
| serial | INT |
| text | LONGTEXT |
| time [ (p) ] [ without time zone ] | TIME |
| time [ (p) ] with time zone | TIME |
| timestamp [ (p) ] [ without time zone ] | DATETIME |
| timestamp [ (p) ] with time zone | DATETIME |
| tsquery | LONGTEXT |
| tsvector | LONGTEXT |
| uuid | VARCHAR(36) |
| xml | LONGTEXT |
| point | POINT |
| linestring | LINESTRING |
| polygon | POLYGON |
| multipoint | MULTIPOINT |
| multilinestring | MULTILINESTRING |
| multipolygon | MULTIPOLYGON |
| geometrycollection | GEOMETRYCOLLECTION |
| triangle | POLYGON |
| tin | MULTIPOLYGON |