Consider creating MySQL to PostgreSQL data migration tool

[questionlp]

I just finished migrating all of the data from a MySQL database to a PostgreSQL database, but ran into quite a few hiccups along the way.

Using the mysqldump method documented in the Migrate MySQL to PostgreSQL guide that lead to quite a few warnings and errors, such as:

invalid command \'t
invalid command \'t
invalid command \'t
invalid command \'t
invalid command \'t
invalid command \'t
ERROR:  syntax error at or near "Ewa"
LINE 1: ...,'iOS','mobile','390x844','en-US','US','US-HI','\'Ewa Beach'...

This is particularly an issue with place names that had embedded inverted single quotes.

Using the CSV export and import method led to a lot of issues where page names had embedded double quotes.

I had to hodgepodge the CSV method along with using DataGrip to import data for the user table due to the following PostgreSQL query error:

ERROR:  syntax error at or near "user"
LINE 1: COPY  user FROM STDIN CSV NULL '\N'

Since PostgreSQL is required with version 3 onwards, a proper migration script or tool should be written where the user provides the source and destination connection strings and the tool handles copying over the data. Otherwise, you may have unhappy self-hosters out there.

[ceviixx]

Hi @questionlp,

I wrote something for my own self-hosted version. Maybe this can help you?
As always, make a backup before the migration and try it on a staging environment first ;)

You can check it out here: https://github.com/ceviixx/umami-m2p

I don't know if there have been any database changes since I wrote the script. (5month ago and tested with 2.18.1)

[questionlp]

That tool is not listed in the Migrating MySQL to PostgreSQL article that I mentioned nor was it something that came up while searching for doing such a migration.

I did take backups and reverted back until I could finish the migration. Still, incorrect or incomplete documentation is worse than no documentation at all.

[ceviixx]

I also found no migration tool for this scenario to switch MySQL to PostgreSQL so I wrote my own.

Hopefully it can help you or inspire 😉

It's not an real migration, I would say an copy from MySQL to PostgreSQL.
MySQL it only exports data and import them to PostgreSQL.