postgresql
mysql

Migrate PostgreSQL to MySQL

Certified — live-tested path

The converter maps 18 PostgreSQL column types to MySQL. One of them changes kind on the way (uuid becomes CHAR(36)); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.

Start PostgreSQL → MySQL Migration

Free tier · no credit card

PostgreSQL vs MySQL

postgresql

PostgreSQL

the most advanced open-source relational database.

mysql

MySQL

the world's most deployed open-source relational database.

PostgreSQL to MySQL data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

PostgreSQLMySQL
BOOLEANBOOLEAN
INTEGERINT
SERIAL / GENERATED ... AS IDENTITYINT ... AUTO_INCREMENT
NUMERIC(p,s)DECIMAL(p,s)
VARCHAR(n)VARCHAR(n)
VARCHAR (no length)LONGTEXT
TEXTLONGTEXT
TIMESTAMP / TIMESTAMPTZDATETIME / TIMESTAMP
JSONBJSON
UUIDCHAR(36)
ARRAY (int[], text[])JSON
BYTEALONGBLOB

Example: PostgreSQL to MySQL schema conversion

PostgreSQL

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT now()
);

MySQL

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  is_active BOOLEAN DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

MySQL compares text case-insensitively

The converted tables use utf8mb4 with MySQL 8's default collation, utf8mb4_0900_ai_ci, which ignores both case and accents. PostgreSQL compared text exactly. So WHERE email = 'bob@example.com' starts matching Bob@Example.com, and a unique index fails the load if PostgreSQL held both spellings as two rows.

Find those rows before migrating: SELECT lower(email), count(*) FROM users GROUP BY 1 HAVING count(*) > 1, for each unique text column. Where case really matters, a column-level COLLATE utf8mb4_bin restores exact comparison.

timestamptz becomes TIMESTAMP, which ends in 2038

timestamptz maps to MySQL TIMESTAMP(6), which also stores UTC. Values are converted to UTC before they are written, and MySQL reads a TIMESTAMP value in the session's time zone, so the load needs the server's time_zone at '+00:00', the default on most managed services and containers. The range is the harder limit. A MySQL TIMESTAMP stops at 2038-01-19 03:14:07 UTC, and a timestamptz holding a far-future value, an expiry of 9999-12-31 or a contract end in 2040, does not fit.

timestamp without time zone maps to DATETIME(6), which runs to the year 9999. Columns holding sentinels or long-dated values are safer converted to DATETIME, with the application treating them as UTC.

uuid, arrays and jsonb

MySQL has no UUID type, so uuid becomes CHAR(36): thirty-six characters in every index that includes it, against sixteen bytes in PostgreSQL. BINARY(16) with UUID_TO_BIN() is smaller and faster, but every query and every application reading the column has to change with it.

Arrays are serialised to JSON, and jsonb becomes MySQL's json, which is binary too but indexed through generated columns rather than GIN. Containment with @> becomes JSON_CONTAINS(), and unnesting an array becomes JSON_TABLE().

PostgreSQL → MySQL conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

  • Arrays (integer[], text[]) are preserved natively on PostgreSQL targets and serialized to JSON elsewhere.
  • JSONB, UUID, INET and other rich types fall back to portable representations with every downgrade recorded in the fidelity report.
  • SERIAL/identity sequences are recreated and reseeded on the target.
  • AUTO_INCREMENT counters are reseeded to MAX(id)+1 after bulk load, so the first production insert doesn't collide.
  • Oversized VARCHAR definitions from other engines are rewritten to TEXT/LONGTEXT to respect MySQL's row-size limits.
  • Foreign keys are applied after data load with FOREIGN_KEY_CHECKS deferred for speed and ordering safety.

Frequently asked questions

Is PostgreSQL to MySQL migration production-ready in DBShifts?

Yes. PostgreSQL to MySQL is a certified pair: it passes the live certification suite on real PostgreSQL and MySQL servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.

What happens to PostgreSQL types that MySQL doesn't have?

Each gets the nearest MySQL type the converter treats as safe: uuid becomes CHAR(36). Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

Can I keep PostgreSQL and MySQL in sync after the initial migration?

Yes, until you cut over. DBShifts uses logical decoding through a replication slot, which needs wal_level = logical and the wal2json plugin on the server. With zero-downtime mode on, the slot is created before the first row is copied, so it holds every change made during the copy.

Which PostgreSQL data types need a second look when moving to MySQL?

The table above carries a note on 3 of them: timestamptz (timestamp(6)), bytea (LONGBLOB), jsonb (json). Analysis flags the same columns on your own schema before any data moves.

Go deeper

Ready to move PostgreSQL to MySQL?

Set up in under two minutes. Validation and rollback included.

Start Free Migration