Migration tool seems to hang.

I'm looking at migrating a MySQL database with around 200,000 rows in it over to Postgres. I tried using the Migration wizard, and it could seemingly log into both DBs and it supposedly cloned over the structures, but when it gets to copying the data for the really huge table, it just seems to sit there, doing nothing (The status slides back and forth, but otherwise does nothing). I left it that way overnight and no go. I'm not sure what it's doing or how to debug it. Does anyone have any suggestions? The inability to move my data over makes it impossible to explore using Postgres as a replacement for MySQL.

-- Jared

That's strange, how big is the table? What kind of hardware are you on?

Did you try logging into the pg database and doing a count on the table?

--Scott

Total row count in MySQL:
278719
Row count in PG while trying to migrate via wizard (Ran overnight, checked in morning):
0

So ... it's definitely hung. It doesn't seem to have done anything.

Machine:
dual core 2Ghz machine, 2GB ram.

I wound up using Heidi SQL to dump the DB as a SQL script, then using sed to 'fix up' the SQL into something compatible to PG. I then ended up using the psql command to redirect that file in and I was able to populate the database completely in about 10 minutes. So ... something is wrong with the migration wizard when it tries to migrate the DB, but I have no idea what. Kinda frustrating.

But it did at least let me then do some tests on if PG would work for my app ... and it did. My app only uses very generic SQL for its prepared statements, and so was compatible across MySQL and PG. Very nice. I just switched the DB info and the app ran fine.

Now if I could only get the migration wizard to work right! It sucks doing the sed fixups on an SQL dump.

-- Jared

Hi Jared.

Not sure what you had to do with sed (rewrite the INSERT syntax?) but if you want another tool to try, check out pyxmdb on www.gossiplabs.org It's a small Python app, that I wrote, which can import and export data from a large number of DBMS. It use delimited data as the intermediate format, so if you still needed some sed magic done, that shouldn't be an issue. If you can connect to both DBs from a single location (such as if they are both on the same physical server), then you could even skip the delimited text file and pipe ("|") the output from the export to the import. If you run into any problems, let me know and I will do my best to help.

Philip

Few memory for java.exe
Use next:

"C:\Program Files\Java\jre6\bin\java.exe" -Xmx1600m -Xms160m -jar "C:\Program Files\PostgreSQL\EnterpriseDB-MigrationWiz
ard\MigrationWizard.jar"

-Xmx1600m — depend how much memory you have