Migrating 1200 databases from MySQL to Postgres

I wrote an open source tool that migrates between any 2 relational databases.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;seanharr11&#x2F;etlalchemy" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;seanharr11&#x2F;etlalchemy</a><p>This includes automatic migration of the schema, constraints, indexes and obviously data. It leverages fast bulk loading tools like mysqlimport, and PostgreSQL&#x27;s COPY FROM. It doesn&#x27;t need any config. Just plug and play.<p>The tool uses SQLAlchemy to abstract column types, and handles various column type conversions between any flavor of SQL.<p>The one thing it needs are tests, and scalability support for tables bigger than a few GB!

I&#x27;m working on migrating an app right now (just one MySQL database) but can highly recommend [pgloader](<a href="http:&#x2F;&#x2F;pgloader.io&#x2F;" rel="nofollow">http:&#x2F;&#x2F;pgloader.io&#x2F;</a>). It has a bunch of built-in and configurable rules to cast different column types to PostgreSQL and the developer has been very responsive in helping me make weird column types work, too.

I like the approach and think something like this is fun but i don&#x27;t understand the businsess case at all.<p>I would have migrated MySQL 5.5 to 5.6 first, than cleaned the shit out of it, normalized everything, added a few indezes and suddently realize that there is not much left to do.<p>I like to work with postgresql but if you have 1,2k dbs which are more or less not claned up and crappy, what are they used anyway?

One thing author did not touch on: since the tables were denormalized-ish in the original MySQL DB, did his application lose significant performance by having to perform the joins for every single query in the renormalized PostGres instance? Or were the DB&#x27;s small enough and indexed properly enough so it didn&#x27;t really matter?<p>Might have been worthy of testing this to see if it was worth it to materialize certain views and refresh them every 15-30 minutes or so.

One thing that Postgres lacks is accent insensitive collations. Having clients with databases in Spanish, this is one of the reasons I wouldn&#x27;t consider migrating to Postgres. I know I can use the unaccent extension, but I consider it a poor substitute to proper collations. I guess this isn&#x27;t a problem for most people because it&#x27;s never mentioned.

If you are migrating to AWS RDS, I recommend AWS Data Migration service. I migrated my live database (~50GB) from Mysql to Postgres (both RDS) with zero downtime.<p>I used AWS Schema Conversion Tool for initial PG schema. I customized the generated schema for my specific needs.

A few of these steps could be solved with this tool, including schema&#x2F;index migrations, and even initial (fast) data transfer: <a href="https:&#x2F;&#x2F;github.com&#x2F;seanharr11&#x2F;etlalchemy" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;seanharr11&#x2F;etlalchemy</a>

I&#x27;m not against the idea of using postgres, but I have a sneaky suspicion, that you could have gotten that 30% speed benefit from MySQL, if you did some performance work on it. Or in other words, I don&#x27;t feel that migrating from one relational database to another similar relational database was worth that much trouble.

I went through a similar process a few years ago (4k dbs into a single db, all mysql). You&#x27;ll get something working fairly quickly and then spend days debugging data anomalies that have crept in over the years.<p>We ended up with a frankenstein mixture of bash scripts, a sequence of sql transformations, some python and some php to decode some of the data that had been stored in pickled &#x2F; php encoded packages.<p>It&#x27;s not something I want to repeat any time soon :-)

One of the main things I ran into when migrating from MySQL to postgres was that the default text columns (TEXT, VARCHAR, etc) behave differently when searching. MySQL matches text case insensitive, while postgres matches it case sensitive. This resulted in searches for content suddenly not returning data. Luckily there&#x27;s an official CITEXT extension for postgres [1], which matches text case insensitive and adds the correct indices for doing so efficiently.<p>[1] <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.1&#x2F;static&#x2F;citext.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.1&#x2F;static&#x2F;citext.html</a>

I&#x27;m not entirely sure what the technical reason for this change was. Of course, cleaning up your DBs and whatnot is a good thing. But why did they move it to Postgres instead of simply clean?

Would love to do this to the monstrosity I inherited in new job but it&#x27;s a sispheyan task.<p>One day.

Is it an universally accepted truth now that it&#x27;s generally better to use Postgres then to use MySql?<p>Would anybody here use MySql for a new project? If so, why?

&gt; Now is when one of the main reasons to take Postgres as our new database server. Postgres allows the use of Views(include link) supporting INSERT, UPDATE and DELETE meanwhile the relation between views and tables are one to one, ....isn’t it awesome?<p>Just a heads up, I think you missed a link in there

When talking about replaying live activity on the test system:<p>&gt;We took the third option because the other two options were quite intrusive and they might interfere at the normal use of our app because in both cases they were going to be in between our users request and our app<p>One of those two rejected options was goreplay. It is passive, like tcpdump. Right from the docs:<p>&quot;Gor is not a proxy: you do not need to put 3-rd party tool to your critical path. Instead Gor just silently analyzes the traffic of your application and does not affect it anyhow.&quot;

Now that is what I call a proper migration. Great content! I was wondering how could somebody end up with corrupt data, I guess it was due to MySQL not strict enforcing the types probably.

<a href="https:&#x2F;&#x2F;github.com&#x2F;the4thdoctor&#x2F;pg_chameleon" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;the4thdoctor&#x2F;pg_chameleon</a>

I tried to migrate from Firebird to Postgres once and I failed, it needs more administration than Firebird.

What was the reason to first migrate and then repair data? Repair scripts could&#x27;ve been ran against the old setup too and you would&#x27;ve had less stuff to migrate.