Migrate PostgreSQL to MySQL via pg2mysql

One of the most popular option to migrate database from PostgreSQL to MySQL using a free tool is pg2mysql. This is a Perl script that converts script file generated by pgdump on STDIN into script complied with MySQL dump format on STDOUT. Input script must be a plain text file containing INSERT-statements. Binary or COPY-statements are not allowed.

Below is the sequence of steps to migrate a database from PostgreSQL to MySQL using pg2mysql.

1. Download pg2mysql from Github repository
2. Install Perl programming language (any popular distribution is suitable)
3. Create dump file (.sql) for the source PostgreSQL database as follows: pg_dump --format p --inserts DBNAME > /path/to/file.pgdump
4. Convert the resulting dump file into MySQL format as follows: pg2mysql.pl < /path/to/file.pgdump > mysql.sql
5. Create empty MySQL database on the target server
6. Load the resulting dump file into create MySQL database as follows:

Key features of ph2mysql tool:

• CREATE TABLE, INSERT INTO, CREATE INDEX and ALTER TABLE statements are converted from PostgreSQL to MySQL
• Columns using sequence nextval as default values are converted into MySQL auto_increment
• There is option (--insert_ignore) to use INSERT IGNORE statements for loading the data. This feature allows to avoid warnings and errors on non-conforming values but implies the risk of inaccurate or missing data.

Note that pg2mysql does not migrate triggers, views and stored procedures.

For those database specialists who is more familiar with PHP, it is recommended to consider similar tool being developed using this programming language:

https://github.com/ChrisLundquist/pg2mysql

This brief description indicates that ph2mysql tool has a lot of useful features, yet it requires some preparation steps and significant post-processing. If you need more easy-to-use and automated solution, take a look at the special commercial tools such as PostgreSQL to MySQL converter.