Migration from MySQL to Postgresql with auto-increments - how?

I'm considering a MySQL to Postgresql migration for my web application, but I'm having a really hard time converting my existing MySQL database to Postgresql.

I tried :

• mysldump with --compati开发者_Go百科ble=postgresql
• migration wizard from EnterpriseDB
• Postgresql Data Wizard from EMS
• DBConvert from DMSoft

and NONE of the above programs do a good job converting my database!

I saw some Perl and Python scripts for converting mysql to postgresql, but I can't figure out how to use them....(I installed ActivePerl and don't understand what I'm supposed to do next to run that script!)

I use Auto Increment fields (as a primary key) all the time, and these are just ignored... I understand that Postgresql does auto-increments in another way (with sequences), but it can't be THAT hard for MIGRATION software to implement that, or is it?

Did anybody have better luck converting a MySQL database with auto-increments as primary keys?

---

I know this is probably not the answer you are looking for, but: I don't believe in "automated" migration tools.

Take your existing SQL Scripts that create your database schema, do a search and replace for the necessary data types (autonumber maps to serial which does all the sequence handling automagically for you), remove all the "engine=" stuff and then run the new script against Postgres.

Dump the old database into flat files and import them into the target.

I have done this several times with sample databases that were intended for MySQL and it really doesn't take that long.

Probably just as long as trying all the different "automated" tools.

---

Why not use an ETL Tool? you dont have to worry about dumps or stuff like that. I have migrated to PostgresSQL and MySQL and have had no problems with the auto increment fields.

You just need to know the connection credentials and thats it. I personally use Pentaho ( it's open source ).

Download Pentaho ETL from http://kettle.pentaho.org/

Unzip and run Pentaho (using .bat file spoon.bat)

Create a new Job:

Create DB connection for source data base (PostgreSQL) - using menu: Tools→Wizard→Create DataBase Connection (F3) Create DB connection for destination data base (Mysql) - using technique described above.

Run the Wizard: Tools → Wizard → Copy Tables (Ctrl-F10).

Select source (left dialog panel), and destination (left dialog panel). Click Finish.

The Job will be generated - Run the job.

If you need any help let me know.

---

Even when you familiar with all "PostgreSQL gotchas", doing every step by hand may take a lot of time, especially when your db is "big". Try some other scripts/tools.

---

I know this is an old question but I just ran into the same problem migrating from MySQL to Postgres. After trying several migration tools out the very best one I could find, which will migrate your database structure as cleanly as possible, was Pgloader https://github.com/dimitri/pgloader/ it will take care of changing the Auto Increment to Postgres sequences no problem and it's super fast.