MySQL to PostgreSQL migration scripts available here
Hello everyone I finally managed to finish my two scripts. It's kind of dirty coded (I'm not a Perl fan + I was in a hurry), but at least they worked for me. I could successfully transfer the data from an old MySQL Galaxy to my new PSQL Galaxy. Before running the scripts, you should already have run your new Galaxy instance so that the tables are properly created. Please note that the scripts will erase everything the target database might contain. It's aimed to make a 'clone' of the old database, not to merge both. Here is how to do the transfer: Requirements: DBD::mysql and DBD:Pg Perl modules 1: run galaxy-My2Pg.pl with the appropriate arguments (see the comments in the script's first lines) 2: start your Galaxy instance If the galaxy instance starts without throwing a fit, you can probably stop here. (I didn't have that chance though) Otherwise you most certainly have the wrong database version: 3: run sh manage_db.sh upgrade (to get the version right) 4: run galaxy-My2Pg_fix-seq.pl with the appropriate arguments blah, it will set the sequences at the right number since manage_db.sh seems to mess them up. Please note that even if it might work for some other databases, it was only tested on Galaxy, and I decline any responsibility if it doesn't work, throws data trhough the windows or blows your whole office up. You should back your data up beforehand in any case. Cheers, LA
Hi Louise, Sorry if this is a bad recommendation and if you already tried it and it doesn't work for Galaxy, but did you know that the MySQL database dump program mysqldump has a feature to dump the entire database properly for import into PostgreSQL? mysqldump --compatible=postgresql [galaxy db name] > galaxy.sql regards, Leandro 2011/4/11 Louise-Amélie Schmitt <louise-amelie.schmitt@embl.de>
Hello everyone
I finally managed to finish my two scripts. It's kind of dirty coded (I'm not a Perl fan + I was in a hurry), but at least they worked for me. I could successfully transfer the data from an old MySQL Galaxy to my new PSQL Galaxy.
Before running the scripts, you should already have run your new Galaxy instance so that the tables are properly created. Please note that the scripts will erase everything the target database might contain. It's aimed to make a 'clone' of the old database, not to merge both.
Here is how to do the transfer:
Requirements: DBD::mysql and DBD:Pg Perl modules
1: run galaxy-My2Pg.pl with the appropriate arguments (see the comments in the script's first lines)
2: start your Galaxy instance
If the galaxy instance starts without throwing a fit, you can probably stop here. (I didn't have that chance though)
Otherwise you most certainly have the wrong database version:
3: run sh manage_db.sh upgrade (to get the version right)
4: run galaxy-My2Pg_fix-seq.pl with the appropriate arguments blah, it will set the sequences at the right number since manage_db.sh seems to mess them up.
Please note that even if it might work for some other databases, it was only tested on Galaxy, and I decline any responsibility if it doesn't work, throws data trhough the windows or blows your whole office up. You should back your data up beforehand in any case.
Cheers, LA
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Hi Leandro :) Someone already asked so I'll just copy the answer I gave him that time. Here it is: On Fri, 8 Apr 2011 16:33:58 -0400, Kanwei Li <kanwei@gmail.com> wrote: > Hi Louise, > > Have you considered doing a SQL dump and import? Sounds easier to me > than writing a perl script ;) > > -K Oh yes I did... Yeah, that solution sounded so sweet that is was the first thing I tried. But don't be fooled by the apparent easyness, it's another whole ordeal to go through. If it was MySQL to MySQL or PSQL to PSQL, yeah that would be the easiest and fastest way. But the syntax/structure used by both is very different (MySQL is awfully non-standard), and there's no batteries-included way to do the translation, just a few scripts here and there. You have to edit stuff manually anyway. An example: everywhere I see people giving the --compatible=postgresql option of mysqldump as the obvious solution of all problems, but when I tried it and searched about it, I really wondered why they provide this option at all. If you want more information about it you should check this website out: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL When I saw the mess it was, I told myself that since the tables are automatically generated, I might as well make the most of it and only transfer the data. I tried using Kettle: http://pinoytech.org/question/5417386/import-mysql-dump-to-postgresql-databa... but in the end I got job crashes and error message like "something crashed, period" so since I didn't want to waste too much time on trying to make this steam machine work, I'd rather make my own lil' script. Yeah, in the end, it was the easiest way to me, believe me or not. ^^ Cheers ;) L-A Actually I still have issues with my new database, I'm getting errors like: UnmappedInstanceError: Class '__builtin__.NoneType' is not mapped when I try to import datasets. The users work fine though. I really have no clue as to what's going wrong but I hope to find the answer soon. Cheers L-A Le mercredi 13 avril 2011 à 11:32 +0200, Leandro Hermida a écrit :
Hi Louise,
Sorry if this is a bad recommendation and if you already tried it and it doesn't work for Galaxy, but did you know that the MySQL database dump program mysqldump has a feature to dump the entire database properly for import into PostgreSQL?
mysqldump --compatible=postgresql [galaxy db name] > galaxy.sql
regards, Leandro
2011/4/11 Louise-Amélie Schmitt <louise-amelie.schmitt@embl.de> Hello everyone
I finally managed to finish my two scripts. It's kind of dirty coded (I'm not a Perl fan + I was in a hurry), but at least they worked for me. I could successfully transfer the data from an old MySQL Galaxy to my new PSQL Galaxy.
Before running the scripts, you should already have run your new Galaxy instance so that the tables are properly created. Please note that the scripts will erase everything the target database might contain. It's aimed to make a 'clone' of the old database, not to merge both.
Here is how to do the transfer:
Requirements: DBD::mysql and DBD:Pg Perl modules
1: run galaxy-My2Pg.pl with the appropriate arguments (see the comments in the script's first lines)
2: start your Galaxy instance
If the galaxy instance starts without throwing a fit, you can probably stop here. (I didn't have that chance though)
Otherwise you most certainly have the wrong database version:
3: run sh manage_db.sh upgrade (to get the version right)
4: run galaxy-My2Pg_fix-seq.pl with the appropriate arguments blah, it will set the sequences at the right number since manage_db.sh seems to mess them up.
Please note that even if it might work for some other databases, it was only tested on Galaxy, and I decline any responsibility if it doesn't work, throws data trhough the windows or blows your whole office up. You should back your data up beforehand in any case.
Cheers, LA
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
participants (2)
-
Leandro Hermida -
Louise-Amélie Schmitt