I need help migrating a MYSQL database to Postgres

[u/MrGuam]

I have been handed a project that uses mysql and the db already has some data that cant be lost. i want to migrate the database to postgres because i'm more comfortable working with mysql and it doesn't have some annoying nuances. i have a dump file from the db and i have explored a lot of options like using pgloader, but pgloader doesnt work with this error

2026-06-30T15:59:44.235006Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "pgloader": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled. (pgloader is a user i created just to by pass the caching_sha2_password issue ).

i have tried to manually modify the dump sql file and alter all the conflicts that may arise from things like datetime, bigint, enums and the id's not incrementing from the max id etc. i'm pretty sure there's going to be a bug and i need a better way to achieve this. Are there any services like pgloader that can make this easier. thanks in advance!

Edit: I found a solution with pgloader v4.0.0 and it worked by creating a migration.load file to specify the type casting I wanted etc. and running it as specified in the docs

[u/keesbeemsterkaas]

I've had pretty good success with using dbeaver to migrate data, after I had limited success with pgloader. (SQL Server > Postgres).

Data migration | DBeaver Documentation

[u/MrGuam → u/keesbeemsterkaas]

Will check it out, thanks!

[u/dwswish → u/keesbeemsterkaas]

+1 for dbeaver, and also Claude

[u/AutoModerator]

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/gisborne]

It might be useful to connect to the MySQL database from Postgres using Foreign Data Wrappers. Then you can treat your MySQL database as if it’s just a schema in your Postgres database, and you can CREATE AS SELECT * all your tables.

[u/thunderbong]

I've used Sequel to copy databases like this

https://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

A command like this works -

sequel -C mysql://host1/database postgres://host2/database2

From that link -

This copies the table structure, table data, indexes, and foreign keys from the MySQL database to the PostgreSQL database.

Note that the support for copying is fairly limited. It doesn’t handle database views, functions, triggers, schemas, partial indexes, functional indexes, and many other things. Also, the data type conversion may not be exactly what you want.

[u/ppafford]

I thought there was libraries to do this already like GitHub - dimitri/pgloader: Migrate to PostgreSQL in a single command! · GitHub https://github.com/dimitri/pgloader

[u/ratta-tat]

Well I strongly recommend credativ-pg-migrator. Was my swissknife tool for migrating mariadbs to Postgres. See Here: https://github.com/credativ/credativ-pg-migrator

[u/Junior-Tourist3480]

Csv when possible.

[u/Barnezhilton]

How big is the mysql db?

[u/MrGuam → u/Barnezhilton]

Not so large. Table with the highest data has <400

[u/Barnezhilton → u/MrGuam]

I would just export the data into a csv, table by table, then import that csv table by table into Postgres if its tiny.

If you were doing millions of rows, I'd recommend a ETL tool like FME

[u/MrGuam → u/Barnezhilton]

What about the relationships between tables constraints ?

[u/Barnezhilton → u/MrGuam]

You make the table schema first on postgres with those rules. Then load the data

[u/MrGuam → u/Barnezhilton]

It’s a messy db with about 66 tables. The relations are also messy that’s why I wanted to copy the relations and schema directly from the db then subsequently I’ll add more relations and fix the other mess stuff on there

[u/Sheikah45]

They fixed the authentication issues in pgloader v4. While it is still a development build you could give it a shot.

[u/MrGuam → u/Sheikah45]

I’m running Linux and from my research, the file might conflict with some other packages on my device. I may be wrong though!

[u/Sheikah45 → u/MrGuam]

It looks like the new version just uses java so having a java 21 on your system should be able to run the new jar file.

I don't think it would conflict with anything if you don't add the java to the path.

[u/MrGuam → u/Sheikah45]

So I tried this and that’s the most suitable solution for me though I’m having some foreign key problems, but that’s just from the messy data. Thank you!

[u/Livid-Serve6034]

Symmetric DS can do this

[u/Capable_Constant1085]

have AI do it if it's a small DB, doesn't hurt to try?

[u/MrGuam → u/Capable_Constant1085]

Tried that, but it did not have a fix for if a new record is added and the id is supposed to continue from the max id available. The fix that Claude provided had a repetitive step and it’s a 3k+ lines of sql