How to convert MySQL 8.2 to PostreSQL 16 in Windows (10)?

[Glyndwr Bartlett]

Hi,

I am a novice at dealing with databases and am trying to convert my MySQL (8.2) to PostreSQL (16). I have read volumes on the subject and most of it went over the top of my head. It seems the easiest way is to use pgloader. However, I cannot find any documentation on how to install it on Windows (10). In the documentation (https://pgloader.readthedocs.io/en/latest/install.html) it states that it is possible; however, does not explain how.

Does anyone have a simple step by step explanation of how to install pgloader on Windows (10) and then use it to convert a MySQL (8.2) to PostreSQL (16) please? Especially being very explicit when detailing any command line that is required.

Kind regards,

Glyn

p.s., Why do I want to do this. First, I am being pressure by an Oracle salesman to change to the enterprise version. So, I am afraid that I am going to end up with an expense I cannot afford. Second, I am being advised that PostreSQL is a superior database.

[Ron McLeod]

Glyndwr Bartlett wrote:... Why do I want to do this. First, I am being pressure by an Oracle salesman to change to the enterprise version.

If you are switching because of licensing or commercial reasons, you might want to consider MariaDB.  It is a fork of MySQL, and should be compatible in most respects.

[Glyndwr Bartlett]

Thank you Ron. That may be my second option if I can not get the PosteSQL to work.

Kind regards,

Glyn

[Carey Brown]

I think you mean "PostgreSQL".
Every database has something that it does better than others. If you've been happy with MySQL I'd go with MariaDB - little to no modifications required.

[Glyndwr Bartlett]

Thank you Carey.

[Tim Holloway]

I second MariaDB. A mysqldump of the MySQL database can be directly streamed in to the Mariabd desktop client (whose name is "mysql" ) And you'll never again have to deal with Oracle salespeople or surprise license audits.

As i mentioned elsewhere, mariadb is part of the standard package repository set in most Linux distros, should be available via MacOS brew and is downloadable for Windows.

If you actually want to migrate to PostgreSQL, a mysqldump would also be the first possible step, but there are 2 caveats. First, the "SQL Standard" isn't 100% standard, so you might have to iron out some quirks. Secondly, one or the other of those databases (I forget which) likes to dump in a special bulk-load SQL insert which is not standard SQL at all and will likely give the receiver indigestion.

An alternate approach is to use an ETL utility such as Pentaho DI (Kettle), which can extract, transform and load data from/to any JDBC source as well as a large number of alternative places, including spreadsheets, text files, Amazon S3 and more. If you have an Oracle (non-MySQL) database and the Oracle sales team gets too annoying, you can use this to move your Oracle database to PostgreSQL, which is in many ways quite similar to Oracle, although in their case it's more of from a common distant ancestor than PostgreSQL being an actual clone or fork of Oracle.

[Glyndwr Bartlett]

Thank you Tim.

[Glyndwr Bartlett]

Does anyone have a simple step by step explanation of how to install pgloader on Windows (10) and then use it to convert a MySQL (8.2) to PostreSQL (16) please? Especially being very explicit when detailing any command line that is required.

[Tim Holloway]

Glyndwr Bartlett wrote:Does anyone have a simple step by step explanation of how to install pgloader on Windows (10) and then use it to convert a MySQL (8.2) to PostreSQL (16) please? Especially being very explicit when detailing any command line that is required.

The pgloader utility is designed as a native Linux app. It looks like the only "easy" way to run it on Windows is under WSL (Windows System for Linux). But see: https://github.com/dimitri/pgloader/issues/652

The pgloader utility looks convenient because it can automatically handle an entire database, but apparently it's only able to make a best approximation due to some of the differences between MySQL and PostgreSQL. In that respect, an ETL tool might work better if you're looking for continous integration (as opposed to a 1-shot move). ETL tools can smooth out the warts as part of the Transform part of ETL.

But for minimal effort, just move to MariaDB. Since it's MySQL but open-source, you'll have virtually no problems just dumping and loading directly and no longer can Oracle harass you once you're on MariaDB. https://mariadb.com/kb/en/installing-mariadb-msi-packages-on-windows/

[Carey Brown]

With MariaDB being the obvious choice why are you fixated on PostgreSQL?

[Glyndwr Bartlett]

Hi Tim,

Thank you. I just read about WSL (Windows System for Linux) yesterday. Looks like it is worth trying out.


Hi Casey,

A friend of mine waxes lyrically about it so I though I would give it a go. In my research it does seem like a better database.

Kind regards,

Glyn

[Tim Holloway]

At one time, MySQL was "incomplete", and PostgreSQL was the definite winner if you needed full Enterprise capability. When I first encountered MySQL, it couldn't even handle transaction.

But MySQL is quite mature now and supports just about anything the average user needs, including replication. As far as I know, the WikiPedia is backed by MySQL, as are countless wordpress sites. So is my recipe manager Spring Boot app these days.

Given that, though, a well-grounded developer would be well-served by knowing both MySQL and PostgreSQL. And, for that matter, SQLite, which is used for really light stuff and is the database that comes with Android.

Given a preference, I do prefer PostgreSQL, but I'm not dogmatic about it. It's just personal bias and shouldn't be taken to mean that I think less of MySQL/MariaDB.

Rather than migrate a complex database, you might just want to install PostgreSQL and build some experimental stuff from scratch just to get familiar with it.

PostgreSQL and MySQL/MariaDB can run concurrently on the same machine, by the way. They use different TCP/IP ports.

[Glyndwr Bartlett]

Hi Tim,

Exceedingly well explained! I think it most prudent to take everyone's advice and move my existing project to MariaDB and consider PostreSQL for future projects.

Kind regards,

Glyn