Migrating 1200 databases from MySQL to Postgres
psyenix.blogspot.com
Migrating 1200 databases from MySQL to Postgres
1–10 of 85 posts
Re: Migrating 1200 databases from MySQL to Postgres
#2Would love to do this to the monstrosity I inherited in new job but it's a sispheyan task.
One day.
Re: Migrating 1200 databases from MySQL to Postgres
#3Would love to do this to the monstrosity I inherited in new job but it's a sispheyan task. One day.
You should see the one here. An old flat file database monstrosity from the 80s, imported into oracle, complete with all of the duplicated data. It's the most non relational, relational database ever.
Re: Migrating 1200 databases from MySQL to Postgres
#4Would love to do this to the monstrosity I inherited in new job but it's a sispheyan task. One day.
You should see the one here. An old flat file database monstrosity from the 80s, imported into oracle, complete with all of the duplicated data. It's the most non relational, relational database ever.
Would a database stored in an XML file shared between 60+ clients over NFS beat that for monstrositiness?
Re: Migrating 1200 databases from MySQL to Postgres
#5One 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's small enough and indexed properly enough so it didn't really matter?
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.
Re: Migrating 1200 databases from MySQL to Postgres
#6A few of these steps could be solved with this tool, including schema/index migrations, and even initial (fast) data transfer:
https://github.com/seanharr11/etlalchemy
Re: Migrating 1200 databases from MySQL to Postgres
#7Earlier quoted context omitted.
You should see the one here. An old flat file database monstrosity from the 80s, imported into oracle, complete with all of the duplicated data. It's the most non relational, relational database ever.
Would a database stored in an XML file shared between 60+ clients over NFS beat that for monstrositiness?
How concurrent is that? Can't imagine how writing to the file is even achieved.
Re: Migrating 1200 databases from MySQL to Postgres
#8I went through a similar process a few years ago (4k dbs into a single db, all mysql). You'll get something working fairly quickly and then spend days debugging data anomalies that have crept in over the years.
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 / php encoded packages.
It's not something I want to repeat any time soon :-)
Re: Migrating 1200 databases from MySQL to Postgres
#9Earlier quoted context omitted.
You should see the one here. An old flat file database monstrosity from the 80s, imported into oracle, complete with all of the duplicated data. It's the most non relational, relational database ever.
Would a database stored in an XML file shared between 60+ clients over NFS beat that for monstrositiness?
Well, this is used now by about 4000 clients. So you be the judge. There is so many indexes, and spaghetti, just to make sure every possible "relationship" is accounted for. Amazing it works at all.
Just using an XML file is pretty crazy though.
Re: Migrating 1200 databases from MySQL to Postgres
#10Would love to do this to the monstrosity I inherited in new job but it's a sispheyan task. One day.
One time I saw a database, with a single table, with a single text column that contained the old database.