PDA

View Full Version : MysQL to Postgres



timgolding
Nov 8th, 2007, 03:35 PM
I have spent months developing a MySQL DB only to find that the sys admin of the project only uses postgres. So now I have to migrate to Postgres. Any ideas for saving time?

ralph l mayo
Nov 13th, 2007, 12:58 AM
So what's the problem? They're basically the same.

id INT NOT NULL PRIMARY KEY auto_increment becomes id SERIAL NOT NULL PRIMARY KEY

you have to use triggers on insert to emulate INSERT ... ON DUPLICATE KEY UPDATE ...

GJay
Nov 13th, 2007, 01:40 AM
postgres won't like any backticks you might have (`s), and if your export from mysql contains anything referencing table-types (myisam, innodb) lose them as postgres doesn't have such a concept

The best thing is to install postgres, try and import the export you've done from mysql and see what doesn't work- there shouldn't be anything (much?) that won't work, once you've got an error-free import.

With postgres, DDL operations ('create table' etc.), are revert-able when inside a transaction, so when doing the imports using 'begin;' and 'commit;
will make the trial+error process a bit easier.