Clever Geek Handbook

Moving data from one database to another with a different data structure

How to transfer data from mysql database to postgres database?

Scenario: two similar applications. The user wants to switch from one application to another. But he kept certain information in his previous appilaction which uses the mysql database. When he switches his application, he has to move his data from his old application to a new application that uses postgres.

Both databases differ from each other in different structure-to-table relationships, the number of tables, and the columns below them.

But how do you transfer a date from one database to another?

Is there any tool for doing this? If so, can someone suggest such a tool.

+2


source to share


6 answers


This may not be what you want, but this page includes

"downloadable Magic drupal-mysql2pgsql.pl Script"

and a discussion of the difficulties of data migration.



This might be inappropriate as it can only migrate Drupal databases (and you didn't specify which applications are involved)

It is best to export / archive your MySQL data in plain text form, which can be massaged into PostgreSQL format and imported. This will require some scripting / editing.

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

+2


source


I would do it like this:

(Using only database tools)

  • create a new database in MySQL and populate it with empty tables, the relationship is exactly the same as I should go to (postgres)
  • copy the data from the original MySQL database to the new one using statements SELECT

    (since the new tables are a mix of the old one, I suppose), or there might be some stored procedures ...
  • then use a dump tool that will generate SQL scripts for my new database to create elsewhere. For MySQL the tool mysqldump

    , and it has the ability to generate postgres compatible scripts: --compatible=postgres

    and many other very interesting options.
  • run these scripts on my new database
  • check inconsistencies, correct ...

(Using custom app + ORM (ex: C #, NHibernate) - longer execution time):



  • create my new database in my new DBMS
  • create an OR mapping for the source database
  • create an OR mapping for the new database
  • import raw data using my ORM
  • transform my data to fit into my new database and insert with my ORM

(Using a well-known tool):

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

+1


source


The problem you are describing is one of the most common problems in the datawarehouse world, where you need to take data from application databases (usually the classic relational data model), and also consolidate and centralize into a data warehouse with different structures (most of the time with the model measurement data). What ETL (Extract Transform Load) jobs do.

You can use them as SQL scripts, run manually or automatically. What you need to install is the transformation logic and then implement it as SQL scripts.

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

+1


source


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

+1


source


There is a whole industry of tools for these scenarios. Google for "ETL" and probably also the names of the database vendors you deal with, and you are likely to find more solutions than you can estimate in a reasonable amount of time.

0


source


I would suggest the same path as the others, with a slight difference. Use an ETL tool for this. Most ETL tools will do the job you described. If you don’t want to spend money to buy an "ETL" tool, my vote goes to Talend Open Source ETL tool. I've used it and it's pretty powerful. Documentation and examples are available at http://www.talend.com .

0


source






More articles:


All Articles