Mysql – Database Transfer from PostgreSQL to MySQL

database-designMySQLpostgresql

Is there any method to tranfer the data and schema from a postgresql database to mysql?

Best Answer

Pg_dump will create a plaintext SQL script with the statements needed to re-create the schema and the data. You can then import this script into mysql. You may need to tweak the script in certain places due to postgres/mysql differences.

Related Solutions

Mysql – Visualization of MySQL/PostgreSQL table structure as a graph/UML

A useful little protip - Visio is quite widely available within corporate I.T. suites and Visio Pro or higher can be used to reverse engineer and document a database.

Visio professional has a database reverse engineering tool that will read a database schema and create E-R models from it. Both PostgreSQL and MySQL support ODBC INFORMATION_SCHEMA tables so the ODBC driver for the Visio DB modeller will work with them. Start by creating a database diagram, and then look for 'Reverse Engineer' under the 'Database' menu.

As a bonus, if the physical DB has missing foreign key constraints you can add them back into the model for documentation purposes. For a more complex schema you can also create several diagrams showing different subsystems. I find myself using this to document systems at client sites on a semi-regular basis and it's often the only tool available.

Mysql – How to convert a MySQL database to PostgreSQL

have you tried:

mysqldump --compatible=postgresql dbname > export.sql

then do this to escape quotes

sed "s/\\\'/\'\'/g" export.sql > export1.sql

may need some more massaging so look at mysqldump doco more closely.

Best Answer

Related Solutions

Mysql – Visualization of MySQL/PostgreSQL table structure as a graph/UML

Mysql – How to convert a MySQL database to PostgreSQL

Related Question