Migration guide

Migrating from MySQL to PostgreSQL

Move MySQL to PostgreSQL in our cloud, your cloud account or your data center: pgloader for the load, Debezium for live changes.

  • Operated by AceMQ
  • 160+ customers
The migration in phases

To migrate from MySQL to Postgres, pgloader reads a MySQL database and loads it into PostgreSQL in one command, converting types and building indexes. It does not migrate views or triggers, and it is a one-time load. To keep MySQL live until the switch, add change capture such as Debezium's MySQL connector, which reads the binlog.

How to connect MySQL to PostgreSQL and move the data

To connect MySQL to PostgreSQL and copy a database across, point pgloader at both connection strings. One command reads the MySQL schema and data, casts the types and loads PostgreSQL. It needs only network access to both servers. Commands checked against vendor documentation on 15 Nov 2026.

Illustrative: each phase is planned and rehearsed with your team before traffic moves.

First create an empty target database, for example with createdb appdb. The pgloader documentation shows the simplest form, with the MySQL source first and the PostgreSQL target second (pgloader docs, checked 15 Nov 2026):

pgloader: one command
createdb appdb
pgloader mysql://myuser@myhost/dbname pgsql://pguser@pghost/appdb

The MySQL password can come from the MYSQL_PWD environment variable instead of the connection string, and the host and port default to localhost and 3306. For more control, a command file sets the casting rules and materializes views as tables. The clauses below are the ones the pgloader reference lists, with placeholder names:

my.load, run with: pgloader my.load
LOAD DATABASE
     FROM mysql://myuser@myhost/dbname
     INTO postgresql://pguser@pghost/appdb
 WITH include drop, create tables, create indexes, reset sequences
 CAST type date drop not null drop default using zero-dates-to-null,
      type year to integer
 MATERIALIZE VIEWS my_view;

"include drop" drops matching tables in the target first, so it belongs on a test database, not a populated one. pgloader installs from apt (apt-get install pgloader) or runs as a Docker image, and the documentation now recommends the v4 Java build, which needs Java 21 or later (pgloader install docs, checked 15 Nov 2026).

  1. Read the pgloader report: the read, imported and errors columns per table show what failed to load.
  2. Compare row counts on both sides for every table, and sample rows with dates, text and binary columns.
  3. Rewrite the views and triggers pgloader leaves behind (see what pgloader leaves behind).
  4. Check that each serial column's sequence sits at the table's maximum. If it does not, set it with setval, for example SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders)) (PostgreSQL docs, checked 15 Nov 2026).
  5. Point a test copy of the application at PostgreSQL and run its test suite before any cutover.

Four ways to export or convert MySQL to PostgreSQL

pgloader, a DBeaver transfer, a tab-separated file loaded with psql, and AWS DMS all work. They differ in how much of the schema they convert and whether MySQL can stay live while the load runs.

RouteHow it worksSource
pgloaderOne command or a command file; converts the schema, loads data, builds indexes.No: a one-time loadpgloader docs
DBeaver data transferSelect tables, choose Export Data, pick Database as the target type, then map each source table to a target container and table. Tables that do not exist are created during the transfer. Available in the Community, Enterprise and Ultimate editions.No: a copy at a point in timeDBeaver docs
Tab file and psql \copySELECT ... INTO OUTFILE writes a file on the MySQL server host; psql \copy loads it from the client. Target tables are created first.No: a copy at a point in timeMySQL docs, PostgreSQL docs
AWS DMSA full load, then ongoing changes read from the MySQL binary log. The MySQL source needs binlog_format set to ROW and binlog_row_image set to FULL for change capture. Schema conversion is a separate tool.Yes, with change captureAWS DMS docs

Sources: pgloader, DBeaver, MySQL, PostgreSQL and AWS documentation, linked in each row; checked 15 Nov 2026.

The tab-file route works because both databases default to the same conventions: the MySQL documentation lists a tab between fields, a backslash escape and \N for NULL as the defaults, and the PostgreSQL COPY text format uses a tab, backslash escapes and \N for NULL by default. Run it on one table first.

Tab file route, one table
-- MySQL: writes on the server host, inside the secure_file_priv directory
SELECT * FROM orders INTO OUTFILE '/path/in/secure_file_priv/orders.tsv';

-- psql, on the machine that holds the file; the PostgreSQL table exists already
\copy orders FROM 'orders.tsv'

Two limits apply. SELECT ... INTO OUTFILE needs the FILE privilege and creates the file on the server host, and the file name cannot already exist (MySQL docs, checked 15 Nov 2026). And AWS DMS recommends turning off foreign key checks during its full load on a PostgreSQL target, for example with session_replication_role set to replica, and updating sequences after replication stops (AWS DMS docs, checked 15 Nov 2026).

Which tools do what

pgloader is the documented one-command route from MySQL to PostgreSQL. It is a one-time load, so a move that must keep MySQL live pairs it with change capture.

ToolWhat it doesBoth sides liveSource
pgloaderLoads data from a MySQL connection into PostgreSQL, converting the schema and building indexes. A default set of casting rules is provided and can be overridden.No: a one-time loadpgloader docs
Debezium MySQL connectorReads the MySQL binlog and publishes row changes as events through Kafka Connect. A JDBC sink connector can write them to PostgreSQL.YesDebezium docs
Community conversion listThe PostgreSQL wiki lists tools and notes for converting from other databases, including MySQL.ReferencePostgreSQL wiki

Sources: pgloader, Debezium and PostgreSQL documentation, linked in each row; checked 30 Oct 2026.

How the tools were chosen: pgloader is the loader its own documentation describes for MySQL, and Debezium is the documented change-capture route with a MySQL connector and a PostgreSQL-capable sink. Other tools are compared in the design review if you already use one.

What pgloader leaves behind

pgloader moves tables and data, and its documentation lists what it does not. Views and triggers are rewritten by hand, and the default casting rules change some column types.

Views and triggers are not migrated.
The pgloader documentation lists both as limitations of the MySQL command (pgloader docs). They are rewritten in PostgreSQL syntax.
Casting rules change types.
By default, an auto_increment int becomes serial, or bigserial for larger precision, and a tinyint of precision 1 becomes boolean. MySQL zero dates convert to NULL (pgloader docs). Each rule is checked against the application, and rules can be overridden.
SQL differences.
MySQL's INSERT ... ON DUPLICATE KEY UPDATE has no direct equivalent in PostgreSQL, where INSERT ... ON CONFLICT does the job (PostgreSQL docs). Application queries are reviewed for dialect differences.
Server-side code.
Stored routines and events the application relies on are reviewed and rewritten, because they are not part of a table load.
Keep the live gap closed.
pgloader loads a point in time. Changes made on MySQL after the load need change capture or a final stop-and-reload.

Sources: pgloader and PostgreSQL documentation, linked above; checked 30 Oct 2026.

The migration in phases

The schema and data are loaded into a test PostgreSQL cluster first, with MySQL untouched. Live changes are then captured from the point of the load, so both databases stay in step until the switch.

  1. Review

    Tables, types, views, triggers, stored routines, character sets, data size, and the SQL the application sends.

  2. Test load

    pgloader loads a copy into PostgreSQL. Casting rules are tuned and the views and triggers are rewritten.

  3. Load and capture

    Change capture starts from the binlog position, pgloader runs the bulk load, and the captured changes are applied after it.

  4. Verify

    Row counts and sampled data are compared, and the application is tested against PostgreSQL.

  5. Cut over

    Writes to MySQL stop, the last changes apply, sequences are set, and the application connects to PostgreSQL.

The timeline and runbook for MySQL to PostgreSQL migrations are written in the design step, from your data volume, traffic and change windows, and you approve them before the cutover is scheduled.

Cutover and rollback

With pgloader alone, the outage equals the final load. With change capture, writes pause only while the last changes apply, and MySQL stays available as the fallback until PostgreSQL has run normally.

  1. The change stream is caught up and the verification queries match.
  2. Application writes to MySQL stop, briefly, and the last changes apply.
  3. Sequences are set to the latest values and the application connects to PostgreSQL.
  4. MySQL stays unchanged until PostgreSQL has served normal traffic.

Sources: the pgloader and Debezium documentation linked above; checked 30 Oct 2026.

How OSStend runs the move

We run the migration and own the cutover, with both sides live until you switch. Zero downtime is the plan.

Managed PostgreSQL from OSStend is operated by AceMQ engineers, in our cloud, your cloud account or your data center. The engine choice is covered in PostgreSQL vs MySQL, a PostgreSQL source such as RDS follows the RDS to PostgreSQL guide, and the migration hub lists the other moves.

Operated by AceMQCompany figures, as published on acemq.com
160+customers served by AceMQ, which operates OSStend
10+years running open source data and messaging
24/7on-call engineers, not a ticket queue

AceMQ names a customer only with that customer's written permission, so most of this record stays anonymous. Ask for a similar workload in the 45-minute review.

When this move fits, and when it does not

The move fits when the workload needs what PostgreSQL offers and the schema is mostly tables and data. It does not when the application is built around MySQL behaviour and works well as it is.

Contact us if

  • The workload needs PostgreSQL features or extensions that MySQL lacks.
  • Most of the schema is tables and indexes, with few views, triggers or stored routines.
  • The business cannot accept a long outage, so change capture is worth setting up.
Talk to an engineer

Not a fit if

  • MySQL runs the workload well and nothing needs PostgreSQL: keep it, and see managed MySQL.
  • The application relies heavily on MySQL-specific SQL and stored routines.
  • You want to run the move yourselves: pgloader works without OSStend.

Questions about MySQL to PostgreSQL

How do I connect MySQL to PostgreSQL?

With pgloader, give it the MySQL connection URI as the source and the PostgreSQL one as the target in a single command, such as pgloader mysql://myuser@myhost/dbname pgsql://pguser@pghost/appdb. The MySQL password can come from the MYSQL_PWD environment variable (pgloader docs, checked 15 Nov 2026).

How do I export MySQL data to PostgreSQL without a migration tool?

Write each table with SELECT ... INTO OUTFILE, which creates the file on the MySQL server host, then load it with psql \copy into a table that already exists. MySQL's default field format (tab, backslash escape, \N for NULL) matches PostgreSQL's default COPY text format (MySQL docs, PostgreSQL docs, checked 15 Nov 2026).

Can DBeaver migrate MySQL to PostgreSQL?

Yes. DBeaver's data transfer copies tables between different databases: choose Export Data on the MySQL tables, pick Database as the target type, and map each table to a PostgreSQL schema. It is available in the Community, Enterprise and Ultimate editions (DBeaver docs, checked 15 Nov 2026). It is a point-in-time copy, so check indexes, constraints and column types afterward.

Can I convert a mysqldump file to PostgreSQL?

Not directly. In MySQL 8.4 the mysqldump --compatible option accepts only the value ansi, so the dump is still MySQL-dialect SQL that PostgreSQL will not accept as it is (MySQL docs, checked 15 Nov 2026). pgloader reads from the live MySQL server instead of a dump.

Does pgloader migrate views and triggers?

No. The pgloader documentation lists views and triggers as not migrated, so both are rewritten for PostgreSQL (pgloader docs, checked 30 Oct 2026).

Can MySQL be migrated to PostgreSQL with no downtime?

pgloader is a one-time load, so a live move adds change capture. Debezium's MySQL connector reads the binlog and publishes row changes that a sink writes to PostgreSQL, keeping both databases in step until the switch (Debezium docs, checked 30 Oct 2026).

What happens to auto_increment columns?

By default, pgloader casts an int with auto_increment to serial, or bigserial at a larger precision (pgloader docs, checked 30 Oct 2026). Sequence values are checked after the load and set before the switch.

Talk to an engineer

Plan your MySQL move with an engineer

45 minutes with an engineer: the current setup is reviewed against the documented method and the cutover is sketched. No ticket queue.

  • A design sketch for your workload, not a plan menu
  • A zero-downtime cutover path for what you run today
  • A price for that design, after the review
See pricing →

Your request goes to the OSStend team. No newsletter sign-up.

Get a quoteTalk to an engineer
2Engines
3Where should it run?
4What do you need?

Your request goes to the OSStend team. No newsletter sign-up.