3

I have to migrate a MySQL database to PostgreSQL. I have tried the mysqldump with --compatible = postresql. I have seen that some poeple use ETL like Talend to make the migration but you must describe the migration for all tables... (I have 39 tables...)

Any other idea ?

Thanks in advance

hotips
  • 533
  • 4
  • 8
  • 19

2 Answers2

3

Create the tables by hand, and then script the data inserts with Perl/PHP/Python/etc, and just run through the data, preferably using PDO (to get around Pg's issues with BLOB data).

Glen Solsberry
  • 1,536
  • 5
  • 28
  • 38
  • Probably the easiest way if `mysqldump` wasn't able to produce anything usable. Leave out all indexes and constraints until AFTER you load your data to make life easier: You won't have to unwind the dependencies & you'll save lots of time if you're not updating indexes on every insert. – voretaq7 May 27 '10 at 15:05
  • Nothing automatic ??? – hotips May 28 '10 at 09:42
1

You can do this with Talend.

This Video describes at first how to do use a dynamic scheme for one table, then how to expand to doing a whole database dynamically.

Below is a screen capture of such a job. It connects to the database, grabs the list of tables from a database, and then iterates through those tables.

Job for migrating a database.

The trick is selecting a dynamic schema for the tables, as seen below.

dynamic table schema

Ps: I work for Talend.

Christopher Klaus
  • 109
  • 4
  • 2
    Welcome to SO! We typically ask that answers be self-contained in case their links go dead or are otherwise unavailable. Can you bring in some of the key points from the video here? – ceejayoz May 13 '18 at 20:38
  • 1
    @ceejayoz added some screen captures based on your reply. Thanks for the suggestion! – Christopher Klaus May 13 '18 at 20:47