Move data from one database to other with different data structure

Question

How to move data from suppose mysql database to postgres database?

Scenario: Two similar application. A user wants to switch from one application to other. But he had maintained certain data information in his previous appilaction which uses mysql database.When he switch his appliaction he has to move his data from his old application to new application which uses postgres.

Both database are different with different structure and table and table relationships, Number of tables and also columns under them.

But how to move the date from one database to other?

Is there any tool to do this? If so can anyone suggest such tool..

Answer 1

It may not be what you require, but this page includes a

"downloadable Magic drupal-mysql2pgsql.pl Script"

and a discussion of the difficulties of migrating the data.

It may be inappropriate because it may only migrate Drupal databases (and you didn't say which applications are involved)

Your best bet may be to export/backup your MySQL data in a simple text form which can be massaged into PostgreSQL format and imported. This would require some scripting/editing.

Which, of course, is why you asked for a tool for the job.

Answer 2

I would do it like this:

(Using only database tools)

• create a new database in MySQL and populate it with empty tables, relations exactly like the one I need to migrate to (postgres)
• copy data from the original MySQL database to the new one using SELECT statements (as new tables are a mix of the old onew I suppose), or may be some stored procedures...
• then use a dumping tool that will generate sql scripts for my new database to create elsewhere. For MySQL the tool is mysqldump and it has an option to generate postgres compatible scripts:--compatible=postgres and many other very interesting options.
• run those scripts on my new database
• check for inconsistencies, correct...

(Using a custom made application+ORM (ex: C#, NHibernate) -- more longer to implement):

• create my new database on my new DBMS
• create a O-R mapping for the original database
• create a O-R mapping for the new database
• import original data using my ORM
• transform my data to fit into my new database & insert it with my ORM

(Using a known tool):

Sorry, but I have no idea if there is a tool that can do this!