9

I want to move from MySQL to PostgreSQL, this is not a live site as yet, going to launch soon and the schema is all ready in MySQL but given the business model I want a better scalable DB, so to convert MySQL to PostgreSQL, do i need to do it manually table by table or are there are GUI tools I can use (or scripts) to automate this conversion for tables/file values?

My app is written in codeingiter PHP.

Improve this question
5
  • 1
    I have no experience in migrating to PostgreSQL so I can't say for sure, but it sounds kind of like a bad idea to switch databases when launch is imminent. Commented Nov 27, 2010 at 1:26
  • 1
    true but better to do the switch now before i go live than later once i have live user data and may run into down times/issues. Commented Nov 27, 2010 at 1:31
  • I hope you have a very good test suite. MySQL tends to be a bit more forgiving/DWIPM (Do What I Probably Mean) than PostgreSQL. Commented Nov 27, 2010 at 2:16
  • It is all manual testing for now. Go page by page and see all data is recorded properly in backend and nothing blows up. Commented Nov 27, 2010 at 2:56
  • Test any queries with a GROUP BY -- PostgreSQL doesn't support MySQL's hidden columns "feature". There are lots of other subtle differences -- changing DB vendors is a very large undertaking. Commented Jan 25, 2011 at 16:50

3 Answers 3

Reset to default
9

The only thing did the job for me was Navicat Premium, You can grab Navicat and use it free for 14 days, which is enough for DB migration...

Just create 2 connections (one source mysql, another target postgresql), and choose Tools -> Data Transfer, and it works!

Other tools I tried: dumping with mysqldump as postgresql compatible - didn't work, as ansi sql - didn't work, tried pgload - didn't work, tried with https://github.com/AnatolyUss/nmig Nmig created tables properly but data was missing.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

you saved the day. Thanks! Everything is very simple and easy
thank you ! this is the only thing wich worked for me on windows
In 2023 this still works and was the easiest for me. Downloading and install Navicat Premium and using the 14 day trial was very simple ... no CC required, no registration required. Just download and install.
2
  1. I agree with Pekka: if you're going to launch soon, the last thing you want to do is switch database systems.
  2. If you're lucky and you're able to export compliant SQL from your current database, Postgres will understand it and little to no extra effort is needed. SQL can be exported to recreate both the structure of your database and the content of its tables.
Improve this answer

Comments

-3

MySQL is a highly scalable database used by some of the largest and most active sites on the interwebs. I'd have some really good testing benchmarks showing Postgres is going to give you a significant advantage before switching over.

Improve this answer

1 Comment

Check out this one: mysqlperformanceblog.com/2006/11/30/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.