PostgreSQL to MySQL and MariaDB Data Migration

Migrating to MySQL from PostgreSQL: what changes in the target

If the target table does not exist, dbSlice stops and shows a CREATE TABLE translated for MySQL, for your DBA to review and run. By default it creates nothing in your databases. These are the PostgreSQL types that need a decision on the MySQL side:

NUMERIC without precision. It is suggested as DECIMAL(38,10). A declared precision is kept up to the MySQL limit of 65 digits and 30 decimals. Check the real values and set the precision and scale before running the CREATE TABLE.
timestamptz. It arrives in UTC and is suggested as DATETIME(6), which keeps the microseconds but stores no time zone. Keep the UTC convention in the application. MySQL TIMESTAMP would convert values by the session time zone and stops in 2038.
boolean. It becomes TINYINT(1); MySQL and MariaDB have no separate boolean type, and BOOLEAN is an alias.
uuid. It becomes CHAR(36) text. For a smaller key, convert it in a SELECT and store it as BINARY(16) with UUID_TO_BIN on MySQL 8, or use the UUID type on MariaDB 10.7 and later.
text and varchar without length. They become LONGTEXT, which cannot be a primary key or a full index. Give a VARCHAR(n) length to every column used as a key or in an index.
bytea. It becomes LONGBLOB. Binary columns are written with batched INSERTs, not the bulk path.
json and jsonb. They become JSON. On MariaDB, JSON is an alias of LONGTEXT with a validity check, read with the same JSON_VALUE and JSON_EXTRACT functions.
Arrays, enums, interval, inet and other PostgreSQL-only types. They are suggested as LONGTEXT. Convert them in a SELECT on the source, for example SELECT id, array_to_string(tags, ',') AS tags, status::text AS status, … FROM orders. You can also declare a MySQL ENUM for the status, or move arrays to a child table.
Character set. Create the MySQL tables with utf8mb4. The old utf8 (3-byte) set rejects emoji and other 4-byte characters that PostgreSQL stores without trouble.
Lower-case names. PostgreSQL names are usually lower case already, which suits MySQL on Linux, where table names are case-sensitive. Keep the column names the same on both sides, because dbSlice maps columns by name.
serial and identity ids. MySQL accepts explicit values in an AUTO_INCREMENT column, so the ids stay the same without any special permission. MySQL moves the counter past the highest id inserted.
Upsert runs as INSERT … ON DUPLICATE KEY UPDATE. The key column you choose must be the primary key or have a unique index on the target. ON DUPLICATE KEY UPDATE fires on any unique conflict, so without one, rows are inserted again.

What changes on MariaDB

MariaDB takes the same CREATE TABLE and the same load. Differences to know:

JSON is stored as LONGTEXT with a check.
MariaDB 10.7 and later has a native UUID type.
Create the tables with utf8mb4, as on MySQL.

How dbSlice controls the load

Reads PostgreSQL in small batches, ordered by the key, so the live database keeps serving its users.
Matches rows by a key you choose. Modes: Initial load, then Upsert, Insert new, Update only or incremental by column (updated_at or the sequence id).
Fast path for large loads: for Initial load and Insert new, dbSlice uses MySQL's LOAD DATA LOCAL INFILE when your connection profile allows it and the server has local_infile on, a setting your DBA controls.
- Each batch runs in a transaction. If MySQL reports a warning or skips a row, dbSlice rolls the batch back and writes it again with normal INSERTs, so the real error is shown.
- The rollback needs InnoDB tables, the default engine.
Checks before writing with Record Diff and on-screen checks, and proves the result with Compare and the full list of divergent keys.
Masks on the way for development and test copies, keeping keys and foreign keys intact.
Export instead of load: when you only need a file, the same app exports a PostgreSQL table to CSV, JSON, XML or HTML.

Step by step: convert a table from PostgreSQL to MySQL

Connect to PostgreSQL and to MySQL or MariaDB.
Pick the source table (or a SELECT, as in the array example) and the target table.
Choose the key column: the primary key, or a column with a unique index on the target.
Choose the mode: Initial load, then Upsert or Insert new.
Run Record Diff, then the copy, or save it as a job for the CLI and the Windows Task Scheduler.

Moving data between PostgreSQL and MySQL in both directions

The same app moves data the other way: see MySQL to PostgreSQL. For SQL Server, see PostgreSQL to SQL Server, SQL Server to MySQL and SQL Server to MariaDB.

FAQ

Does dbSlice convert functions, triggers or views?

No. It moves data; PL/pgSQL code has to be rewritten for MySQL.

Do I need logical replication or extensions on PostgreSQL?

No. dbSlice reads the tables with ordinary queries.

Does it work with MariaDB?

Yes, with the same steps; see "What changes on MariaDB" above.

Are deletes copied by incremental runs?

No. Run Compare or a reload before the switch.

Is there a free version?

Yes, on the Microsoft Store: it connects, browses, compares and exports to CSV. Writing to a target needs a Desktop or Server license.

Data migration from PostgreSQL to MySQL and MariaDB