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 <kan...@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-database
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.schm...@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:
>
> http://lists.bx.psu.edu/
>
___________________________________________________________
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:
http://lists.bx.psu.edu/
