Migrate from Postgres to MySQL

Introduction

This guide will show you how to migrate your database from PostgreSQL to MySQL. This is useful for Easy8 server administrators who need to switch their database backend. We tested both free and premium tools and included step-by-step instructions for each.

Target Audience

Administrator

Prerequisites

Before starting, ensure you have:

Access to both the PostgreSQL and MySQL databases
Basic knowledge of SQL and terminal usage
Installed PHP if using the free script
Administrative rights on the system

How to Migrate Using Free Tool (pg2mysql)

Step 1: Download the conversion script

Use one of the following options:

Original script:

wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2
tar -xvf pg2mysql-1.9.tar.bz2

Modified script for PHP 7.4:

wget https://www.easyredmine.com/ER/media/knowledge-base/pg2mysql-1.9.zip
unzip pg2mysql-1.9.zip

Step 2: Install PHP

sudo apt install php7.4-cli

Step 3: Create PostgreSQL dump

sudo -u postgres pg_dump --format p --inserts DBNAME_HERE > /path/to/file.sql

Step 4: Switch to script folder

cd pg2mysql-1.9/

Step 5: Run the conversion

php pg2mysql_cli.php /path/to/pd/dump/file.sql /path/where/to/save/mysql/file.sql

You should see:

Completed!     30820 lines        5539 sql chunks

Step 6: Modify dump file

Open the resulting .sql file and replace all engine definitions from MyISAM to InnoDB.

Step 7: Import into MySQL

mysql -u [user] -p [db_name] < /path/where/to/save/mysql/file.sql

Replace [user] and [db_name] with your actual database credentials.

Step 8: Add missing indexes

The script does not preserve indexes. You must manually add them or use your own script. Refer to a standard Easy8 schema for guidance.

How to Migrate Using Premium Tool (DBConvert)

Preparation

Make both PostgreSQL and MySQL accessible remotely.
Ensure login credentials are available.
Install tool from: https://dbconvert.com/postgresql/mysql/

Migration Process

Launch DBConvert wizard.
Select PostgreSQL as the source, fill in connection details, and test the connection.
Choose the database and tables to convert.
Set destination (MySQL), enter details, and test the connection.
Fix field type issues (e.g., change CHAR(1) to CHAR(2)).
Start the conversion and wait for completion.
Click "Exit". Conversion is done.

Price: $149 (one time)

Other Premium Options

pgs2scld

Trial available (copies 50 records/table)
Internal debugger
Windows only, UI-based
Price: $49
Website: https://www.convert-in.com/pgs2sql.htm

ESF Database Migration Toolkit

Trial available (copies 50,000 records)
Internal debugger
Windows only, UI-based
Price: $322
Website: https://www.dbsofts.com/articles/postgresql_to_mysql/

Conclusion

You have successfully learned how to migrate from PostgreSQL to MySQL using both free and premium tools. We recommend reviewing your output and checking indexes post-conversion to ensure full performance and functionality.