Migrating from MySQL or MariaDB to PostgreSQL

OutsourcingVN is operated by Netbase JSC. This guide assumes PostgreSQL has already been chosen as the destination; whether it fits the application's data at all is the prior question, answered on the PostgreSQL platform page, and the document-shaped alternative is covered on the MongoDB platform page.

Contents

• When does a MySQL or MariaDB to PostgreSQL move pay off?
• What changes in the schema and data types?
• What should you check in SQL and the ORM?
• How should the migration be sequenced?
• Worked scenario: a hypothetical subscription catalogue
• What has Netbase delivered with PostgreSQL and MySQL or MariaDB?
• Where does AI fit in a MySQL to PostgreSQL migration?
• What should you check before committing to the migration?
• What goes wrong in a MySQL to PostgreSQL migration?
• Which questions should you ask a migration partner?
• How this guide is sourced and where it stops
• Common questions
• Plan your database migration

When does a MySQL or MariaDB to PostgreSQL move pay off?

A system failing two or more of the right-hand signals is a migration candidate; the incremental modernization without a rewrite guide covers running the two databases side by side while that case is proven.

What changes in the schema and data types?

Identifier case is the first trap. MySQL's own documentation states that "the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names," controlled by a lower_case_table_names setting that "can only be configured when initializing the server." PostgreSQL takes the opposite default: "quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case," so a CamelCase name that worked by accident on a case-insensitive MySQL install needs an explicit decision on PostgreSQL, not an assumption it carries over unchanged.

The second is the auto-incrementing primary key. MySQL's AUTO_INCREMENT attribute, which "can be used to generate a unique identity for new rows," converts to a PostgreSQL identity column, described as giving the column "an implicit sequence attached to it" so that "in newly-inserted rows the column will automatically have values from the sequence assigned to it." The GENERATED ALWAYS and GENERATED BY DEFAULT variants decide whether an insert may ever supply its own value, a choice AUTO_INCREMENT leaves implicit.

JSON needs its own check: MySQL's documentation states that its JSON type performs "automatic validation of JSON documents" and sorts object keys on storage, a result the manual itself says is "not guaranteed to be consistent across releases," and PostgreSQL's jsonb, covered on the PostgreSQL platform page, stores keys in its own order too, so code depending on either engine's key order needs rewriting. Collations need the same scrutiny: MariaDB defines a collation as "the rules for comparing and sorting" a character set, and records that MariaDB 11.6 changed its own default collation; a converted column's collation decides how values sort on the far side.

What should you check in SQL and the ORM?

Dialect-specific SQL functions, date arithmetic and vendor-specific LIMIT extensions need rewriting against PostgreSQL's own syntax, not a search-and-replace. An application's ORM typically ships a PostgreSQL dialect driver alongside its MySQL or MariaDB one, but switching it is not sufficient on its own: generated queries can still assume MySQL's case-insensitive identifiers or its JSON key order. Laravel is one common application layer here; its Eloquent ORM supports both engines, but a MySQL-specific function still needs its PostgreSQL equivalent written in by hand.

Worked scenario: a hypothetical subscription catalogue

A mid-size subscription retailer runs its catalogue, orders and customer accounts on MariaDB, and wants PostgreSQL's richer JSON indexing for a per-customer preferences field that currently lives in a text column its application parses by hand.

• Milestone one profiles the schema, finds three case-insensitive table names and one AUTO_INCREMENT key referenced from application code, and converts the schema with explicit quoting and identity columns.
• Milestone two bulk-loads the catalogue and order history, then runs a change-sync process so orders placed during testing land on both databases.
• Milestone three reconciles row counts and order totals, finds one timezone difference in a timestamp column, fixes it and re-runs the sync.
• Milestone four cuts over in a scheduled low-traffic window, with MariaDB kept readable for a month before decommissioning.

What has Netbase delivered with PostgreSQL and MySQL or MariaDB?

Since 2020 Netbase has worked as offshore development and managing partner on a multi-tenant cloud ERP SaaS for a US client that is not named; its first phase, from 2020 to 2023, covered CRM, real-time messaging, HR, a knowledge base, custom fields and workflows, work and project management and API integrations, on a stack that includes React and Next.js, Laravel and Strapi, PostgreSQL, MySQL, MariaDB and MongoDB on AWS; see the multi-tenant cloud ERP record. On the migration-method side, for Netztech, Netbase delivered a Magento 1 to Magento 2 migration in 35 working days across 11 extensions, the closest published example of how Netbase scopes and runs a platform migration.

See the Netztech migration record

Where does AI fit in a MySQL to PostgreSQL migration?

The clearest AI use on a move like this sits in the conversion step: reading an unfamiliar legacy schema to draft a first-pass type-mapping table, and generating candidate reconciliation queries from the schema diff for a person to check against real data. None of it decides whether a type mapping is correct or signs off a reconciliation result, since both are judgements about the application's own data. Data and AI platform engineering is the usual route when the migration also feeds a shared analytics or AI platform rather than staying inside one application.

What should you check before committing to the migration?

PostgreSQL's own documentation describes logical replication as "a method of replicating data objects and their changes, based upon their replication identity," a publish-and-subscribe model in which a subscriber takes an initial snapshot and then receives "changes on the publisher since the initial copy...sent continually" — a PostgreSQL-to-PostgreSQL mechanism, so the change sync from a still-live MySQL or MariaDB source runs on a tool that reads the source's binary log instead. MySQL's long-term-support line follows "the Oracle Lifetime Support Policy, which includes 5 years of premier support and 3 years of extended support," while PostgreSQL supports a major version "for 5 years after its initial release." Confirm before scoping a build:

• Which identifiers rely on MySQL's case-insensitive default, since an unquoted name that crosses to PostgreSQL is folded to lower case regardless of how it behaved on the source.
• Which AUTO_INCREMENT columns need GENERATED ALWAYS versus GENERATED BY DEFAULT, since the two accept an explicit insert value differently.
• Which JSON columns depend on MySQL's own key ordering, since PostgreSQL's jsonb orders keys differently.
• Which collations are actually in use on each converted column, not only which character set, since MariaDB's own default collation has changed across versions.
• How long the two databases need to stay in sync before cutover, and who owns watching that replication lag.

What goes wrong in a MySQL to PostgreSQL migration?

• Identifier case assumed to carry over. A name that worked by accident on a case-insensitive MySQL install breaks silently once PostgreSQL's case-folding rule applies to it.
• Application code reading JSON key order. Code relying on MySQL's own key-sorting behaviour breaks once the data moves to jsonb.
• No rollback ever rehearsed. A cutover plan never run backwards discovers the rollback does not actually work during the live cutover.
• Replication lag ignored until cutover. An unmonitored sync can drift far enough behind that the final delta at cutover is larger than anyone rehearsed for.

Which questions should you ask a migration partner?

• How is the schema and type mapping verified against real data, not only the table definitions?
• What replication method keeps the two databases in sync, and how is lag monitored?
• What is the rollback path if reconciliation fails after cutover? Most Netbase projects are fixed-scope contracts agreed after discovery, and rollback is scoped as part of it.

How this guide is sourced and where it stops

The MySQL, MariaDB and PostgreSQL facts are read from each vendor's own documentation on the dates cited. Netbase's evidence covers a cloud ERP stack including all three engines and a named platform migration on a different system; the steps above are editorial guidance, not a duration claim for any one project.

Plan your database migration

Bring your current schema, your most-run queries, and your tolerance for a sync window before cutover, and legacy application modernization scopes the conversion, the copy and sync, and the cutover as separate milestones.

OutsourcingVN is Netbase's own outsourcing-services platform. Submit a project with your current database engine and schema, and a person will reply with the fit.

Related services and solutions

Sources

• MySQL, Identifier Case Sensitivity
• PostgreSQL, Lexical Structure
• MySQL, Using AUTO_INCREMENT
• PostgreSQL, CREATE TABLE
• MariaDB, Character Set and Collation Overview
• MySQL, The JSON Data Type
• PostgreSQL, JSON Types
• PostgreSQL, Logical Replication
• MySQL, The Binary Log
• MySQL, MySQL Releases: Innovation and LTS
• PostgreSQL, Versioning Policy