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
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.
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):
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:
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).
- Read the pgloader report: the read, imported and errors columns per table show what failed to load.
- Compare row counts on both sides for every table, and sample rows with dates, text and binary columns.
- Rewrite the views and triggers pgloader leaves behind (see what pgloader leaves behind).
- 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).
- 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.
| Route | How it works | MySQL stays live | Source |
|---|---|---|---|
| pgloader | One command or a command file; converts the schema, loads data, builds indexes. | No: a one-time load | pgloader docs |
| DBeaver data transfer | Select 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 time | DBeaver docs |
| Tab file and psql \copy | SELECT ... 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 time | MySQL docs, PostgreSQL docs |
| AWS DMS | A 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 capture | AWS 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.
-- 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.
| Tool | What it does | Both sides live | Source |
|---|---|---|---|
| pgloader | Loads 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 load | pgloader docs |
| Debezium MySQL connector | Reads the MySQL binlog and publishes row changes as events through Kafka Connect. A JDBC sink connector can write them to PostgreSQL. | Yes | Debezium docs |
| Community conversion list | The PostgreSQL wiki lists tools and notes for converting from other databases, including MySQL. | Reference | PostgreSQL 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.
Review
Tables, types, views, triggers, stored routines, character sets, data size, and the SQL the application sends.
Test load
pgloader loads a copy into PostgreSQL. Casting rules are tuned and the views and triggers are rewritten.
Load and capture
Change capture starts from the binlog position, pgloader runs the bulk load, and the captured changes are applied after it.
Verify
Row counts and sampled data are compared, and the application is tested against PostgreSQL.
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.
- The change stream is caught up and the verification queries match.
- Application writes to MySQL stop, briefly, and the last changes apply.
- Sequences are set to the latest values and the application connects to PostgreSQL.
- 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.
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.
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.
Related pages
- PostgreSQLManaged PostgreSQL service and Postgres hostingManaged PostgreSQL service, compared: how to choose between RDS, Aiven, Crunchy Bridge, Neon and a tailored cluster run 24/7 in your cloud or ours.
- ComparisonPostgreSQL vs MySQL for production workloadsPostgreSQL vs MySQL for production workloads: licence, extensions, JSON, replication and when each fits. Sourced and dated, by a team that runs both.
- Migration guideMigrate Postgres off Amazon RDS, step by stepMigrate Postgres off Amazon RDS with logical replication and pg_dump: what to switch on, what replication skips, how cutover works and where the pause falls.
- OverviewZero-downtime migration, owned by our engineersZero-downtime migration guides for Amazon MQ, RDS, Redis, Atlas, Oracle, MySQL, Confluent and Elasticsearch, using each engine's documented method.
- MySQL and MariaDBMySQL hosting: managed MySQL and MariaDB, 24/7MySQL hosting for enterprise workloads: managed MySQL and MariaDB with replicas, point-in-time recovery and failover, run 24/7 in your cloud or data center.
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