Transferring data but not structure between Postgres and MySQL

Question

I am currently migrating a web application from one platform to a different one, and part of this migration involves moving data from a Postgres DB to a MySQL DB. The table structures will not be the same, so I am wondering what are some suggestions on moving table contents, but not structure, from one Db to another? By this I mean moving all row data in a given table, to a different row in a different table in a different database that does not share the same table structure. The app is fairly small, and this will be a one time migration, so I don't mind if the process is manual for each row.

I know just enough SQL to be dangerous, so I have been looking at what tools are available for a noobs. Is this something I could do in Navicat? Any advice would be greatly appreciated.

Answer 1

The approach I would use would be based on the new structure. For every table in the new structure, come up with a SQL query that pulls data from the old structure and drops it into a basic CSV format, then import to the new structure. I'm glossing over things like making sure keys match, ensuring you always get the same representation of data sets with the new structure, etc.

Answer 2

This sort of thing is just what tools like Pentaho Kettle, Talend Studio, and CloverETL are for; the general category of tools is ETL ("Extract; Transform; Load").

Unfortunately, their learning curve can be very steep, so it may well be simpler to just write some queries to extract CSV that you can COPY into the new DB, as John advises.