I've seen questions for doing the reverse, but I have an 800MB PostgreSQL database that needs to be converted to MySQL. I'm assuming this is possible (all things are possible!), and I'd like to know the most efficient way of going about this and any common mistakes there are to look out for. I have next to no experience with Postgre. Any links to guides on this would be helpful also! Thanks.
Add a comment
|
3 Answers
One advise is to start with a current version of MySQL, otherwise you will not have sub-queries, stored procedures or views. The other obvious difference is auto-increment fields. Check out:
/Allan
You should not convert to new database engine based solely on the fact that you do not know the old one. These databases are very different - MySQL is speed and simplicity, Postgres is robustness and concurrency. It will be easier for you to learn Postgres, it is not that hard.
1 Comment
Craig Ringer
While I tend to agree, that's really more of a comment than an answer to the question.
pg_dump can do the dump as insert statements and create table statements. That should get you close. The bigger question, though, is why do you want to switch. You may do a lot of work and not get any real gain from it.
1 Comment
Craig Ringer
pg_dump will produce quite PostgreSQL specific output that needs some massaging for anything else to be happy with still. You'd need to hand convert the schema, then do a --data-only --inserts dump - and even then it'll only work if you're using all compatible types, and only if you set MySQL to STRICT / ANSI mode so it understands SQL-standard quoting etc.