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!

Answer 3

There is a whole industry of tools for these scenarios. Google for "ETL" and probably also the database vendor names you are dealing with and you will likely find more solutions than you can evaluate in a reasonable amount of time.

Answer 4

The problem you're describing is one of the most common problems in datawarehouse world, where you need to take data from application databases (usually classic relational data model) and consolidate and centralize in a datawarehouse with different structure (most of the time with dimensional data model). That what ETL jobs (Extract Transform Load) do.

You can have them as SQL scripts, run by hand, or automatically. What you need to establish is the transformation logic, then to implement it as SQL scripts.

There are dedicated ETL tools, but I don't think they add too much value in your case.

Answer 5

I would suggest the same path as the others with a slight difference. Use ETL tool to do that. Most of the ETL tools would do the job you just described. If you don't want to spend any money buying an "ETL" tool, my vote goes to Talend Open Source ETL tool. I've used it and it is pretty powerful. Documentation and examples are available in http://www.talend.com.

Answer 6

I would also advise using ETL tools for this kind of job. Applying different functions will make your migration 100% successful. I recommend using open source tools like Apatar (apatar.com) for business users and Talend (talend.com) for technical users.