Could you point a way to migrate a PostgreSQL database to a MySQL database?

Hi Experts

Could you point a way to migrate a PostgreSQL database to a MySQL database?

I'm currently using Win7 environment.

Thanks in advance!
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian DayhoffSenior Full Stack DeveloperCommented:
There is no reliable automated tool to do this. Typically you would use a post filtering batch process (Python is common for this) that applies translations based on the known schema. You basically have to write that for each database though.
Brian DayhoffSenior Full Stack DeveloperCommented:
I see that you have tagged this with PHP though. If you are working with PHP, you can access the PostGres database directly by using PDO instead of mysqli or the very old and deprecated mysql_query functions. There's really no reason to port it in the vast majority of cases.
Shaun VermaakTechnical SpecialistCommented:
For a custom or off-the-shelf application? If it is an off-the-shelf application it might offer a method as part of the application.

As mentioned above, here is an example of a conversion tool (not free)
Brian DayhoffSenior Full Stack DeveloperCommented:
I really have to warn you not to do this with any generic automated converter. They have frequent tendency of butchering your schema, corrupting your data, or not converting relations correctly and introducing long term basically unsovable bugs into your platform.

The issue that occurs when you do this, is that an application relies on communication between the database and the server, each of which does their jobs independently and has no knowledge of the inner working of the other. An application will usually not account for relations in the sql, because it assumes the database is set up correctly. Likewise the database will not regard the operations of the server, because it assumes that the application knows how to appropriately push and pull data.

The issue is when you use something that has no knowledge of either to try and change the database, and it gets your relations wrong, encodings wrong, or any number of other things that do not work the same way from one database type to another. PostGreSQL is significantly more feature rich than MySQL, and in order to go from PostGres to MySQL, you have to spoof a number of things that are not supported in MySQL to get them to work. This however does not get communicated directly to your application, which will still operate on it under the assumption that it works as originally set up. This leads to data getting permanently corrupted as it makes several round trips between the application and the database. After 50-100 transactions against data that has incorrect relations, wrong encoding, or is trying to use a native postgres feature that is not present on mysql, you are going to have to throw it away completely because it is entirely irrecoverable.

You have a pretty high chance of introducing critical instability by trying to use a generic solution. If you are doing this for a job, it will probably get you fired. If you are doing this for a client, it will probably get you sued.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Even a tool for automatic conversions exists  I conclude it's better to PHP programatically convert.
Since the inner nature of each DB can differ a lot.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.