Migrate from PostgreSQL to MatrixOne¶
Design database/schema/table naming first to avoid collisions between schemas. PostgreSQL and pg_dump were not executed here; MO target boundaries and a small converter input were checked.
Types and objects¶
PostgreSQL |
Candidate/policy |
Checks |
|---|---|---|
serial / identity / sequence |
Auto-increment or application IDs with correct next value |
Shared sequences, step/default, explicit inserts are not automatically equivalent |
numeric / money |
Distribution-based DECIMAL |
Precision, scale, special values, currency/locale; no float intermediary |
bytea |
VARBINARY/BLOB |
Original bytes; \x representation is not ordinary text |
timestamp / timestamptz |
Local-time versus UTC-instant policy |
Session zone and source range; timestamptz does not retain the original timezone label |
jsonb / array / range / composite / domain / XML |
Explicit JSON/text/relational conversion |
Operators, indexes, NULLs, constraints; no direct-DDL promise |
geometric / PostGIS geometry/geography |
Explicit WKT/WKB + SRID, then MO function checks |
Built-in geometric types and GIS differ |
inet / cidr / MAC / pg_lsn / OID |
Text/binary/dedicated representation |
Preserve network/log-position business semantics |
Convert and copy¶
pg2mysql uses pg2mysql.pl (Perl), not Python. Pin and review it: the tested small input emits DROP DATABASE. Its known type/view/trigger limitations require reviewing output before import to existing targets.
Use real PostgreSQL export formats. Distinguish SQL INSERT from COPY and custom/binary archives; the latter cannot be executed as MySQL SQL. Select consistent snapshots per exporter guidance and record schema, owners/privileges, sequence state, and log start. Global schema/numeric/bpchar substitutions can alter literals or semantics.
For DBeaver/batch tools, configure mappings, transactions/batches, and failures. Specify file NULL/escapes/encoding/time. A 5000-row batch is not a direct-S3 guarantee; PostgreSQL pg_dump has no MySQL net_buffer_length option.
Live migration needs validated replication-slot/LSN/snapshot handoff, retention, DDL, and replay. GUI copying or a mo_cdc mysql:// URI does not supply this pipeline. See migration workflow and heterogeneous acceptance. PostgreSQL sources, logical replication, and production cutover are uncertified.