We just migrated a medium size project using pgloader with great success and minimal headaches, which seems like a big step up from the last time I had to migrate a project. Highly recommended, and it might be easier than you think!

Frankly, data reliability concerns with modern MySQL / InnoDB are very outdated FUD.

Many of the largest tech companies rely on MySQL as their primary data store. They would not do so if it was unreliable with persistence.

There are many valid reasons to choose Postgres over MySQL, or vice versa -- they have different strengths and weaknesses. But there are no major differences regarding data reliability today, nor have there been for many years now.

I haven't kept up, but I remember having constrains that were accepted by the parser, but ignored by the engine (using InnoDB, I don't remember what specific constrain was concerned).

Is it still the case?

It was the case until very recently. Check constraints are now supported in MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-c...

I believe MariaDB added support for them a couple years earlier, but am not certain.

More broadly, I would agree it's a very painful "gotcha" to have aspects of CREATE TABLE be accepted by the parser but ignored by the engine. However, in MySQL's defense, theoretically this type of flexibility does allow third-party storage engines to support these features if the engine's developer wishes.

Ideally, the engine should throw an error if you try using a feature it does not support, but in a few specific cases it does not (at least for InnoDB). This can be very frustrating, for sure. But at least it's documented. And no database is perfect; they all have similarly-frustrating inconsistencies somewhere.

To be clearer, it's about the defaults regarding strictness (of the acceptance) of data that help to avoid trouble up front, not that mysql randomly corrupts/deletes it later.

Sure, and MySQL 5.7, released 4 years ago, fixed those bad defaults.

It isn't fair to compare Postgres-of-today to MySQL-of-over-4-years-ago.

Ok, looks like that version is in LTS dists now. Perhaps to poster's legacy apps are taking advantage of them.

There were other deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now.

Even if OP is using 5.6 or previous, strict mode has been available as an option for over 15 years, and can be enabled dynamically (no restart required).

I simply don't see any valid argument for avoiding MySQL due to "data reliability" concerns in 2019.

> There were other minor deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now.

Well, I can't really respond to non-specific points from a book I haven't read. I'm happy to respond to any specifics re: data reliability concerns, if you want to cite them. FWIW, I have quite extensive expertise on the subject of massive-scale MySQL (16 years of MySQL use; led development of Facebook's internal DBaaS; rebuilt most of Tumblr's backend during its hockey-stick growth period).

It takes years to build up trust, seconds to lose it. Obsolete documentation to disappear. It's an uphill battle. In the meantime there's postgres, and it's free.

MySQL defaults to InnoDB. Is there a different metric for reliability you had in mind?

Where I work, we chose MySQL back in 2012 due to production quality async replication. I think (but am never sure) that that is now good in Postgres land.

PG has a lot of SQL features I'd love to use and can't. OTOH MySQL's query planner is predictably dumb, which means I can write queries and have good idea about how well (or not) they'll execute.