Migrate from Postgres to MySQL

Introduction

For the sake of you, Easy Project server admins, we have tested a couple of tools for DB migration from Postgres to MySQL.

One is a free open-source, but also older and with a significant disadvantage. The others were premium tools, with results that, in our opinion, make it worth the purchase.

Free option - pg2mysql php script

Disclaimer: This tool was developed by a third party => it is NOT a product of Easy Software, and therefore is NOT GUARANTEED.
The tested tool available at:
http://www.lightbox.ca/pg2mysql.php
We did make our own modifications to the tool to make it work with php 7.4. However, use is still on own risk, and we provide NO GUARANTEES whatsoever.

Steps for migration

1. Download the script and unpack the archive.

wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2  (contains original script from the author)
wget https://www.easyredmine.com/ER/media/knowledge-base/pg2mysql-1.9.zip (contains our modified script for php version 7.4)

tar -xvf pg2mysql-1.9.tar.bz2(in case of original script from the author)
unzip pg2mysql-1.9.zip (in case of our modified script for php version 7.4)

2. Install php

sudo apt install php7.4-cli (in case of our modified script)

3. Create a dump of PostgreSQL database in .sql format, make sure you use "--format p --inserts"

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

4. Switch to pg2mysql-1.9 folder

cd pg2mysql-1.9/

5. Run

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

You will see some lines like

Completed!     30820 lines        5539 sql chunks

Notes:
 - No its not perfect
 - Yes it discards ALL stored procedures
 - Yes it discards ALL queries except for CREATE TABLE and INSERT INTO 
 - Yes you can email us suggestsions: info[AT]lightbox.org
    - In emails, please include the Postgres code, and the expected MySQL code
 - If you're having problems creating your postgres dump, make sure you use "--format p --inserts"
 - Default output engine if not specified is MyISAM"

6. Now file with modified sql dump will be created. We strongly advise you to go through it and replace MyISAM to InnoDB everywhere

7. Now you may restore this dump into clear mysql database.

8. Because (as author notified us) the script doesn't save indexes you have to add them manually to every table (it will work without indexes but it may cause serious performance issues). There is a list of all indexes that should exist in MySQL tables generated by regular Easy Project application. You need to add them manually or via own custom script.

There are other free tools available on the web. However, our test results were not satisfactory on those ones.

Premium tool - DB Convert for MySQL & PostgreSQL v. 4.3.5

We tested 3 premium tools altogether. DBConvert felt as the most suitable, therefore we are describing the detailed steps for it.

Preparation:

1. Make sure that both MySQL and PostgreSQL databases are accessible from outside (you may allow it temporarily).

2. Make sure you may login to both dbs with user and password

3. Install the tool from - https://dbconvert.com/postgresql/mysql/

Start the convertion:

1. Start the wizard.

2. Choose Source database:
PostgreSQL
Hostname: ip or domain name of the server where postgres db is installed.
Port: if different from default
Username: username with which db you are going to convert is accessible
Password: password for user above.

Click Test connection button. The app will check that it is possible to connect to the source db server with chosen user and password. If it is possible, the list of databases will be uploaded - pick up the correct one.

3. Click "Next". The app will connect to postgres and receive the list of tables available in the database. You may choose all of them to be converted or only one / few of them.

4. Wait a bit and you will be forwarded to the next step - MySQL connection settings.
Hostname: ip or domain name of the server where mysql db is installed.
Port: if different from default
Username: username with which db you are going to convert is accessible
Password: password for user above.

Click Test connection button. The app will check that it is possible to connect to the source db server with chosen user and password. If it is possible, the list of databases will be uploaded - pick up the correct one.

5. On the next step the application will check the source database and give you some recommendations how to solve possible problems.

We've met the next problem:

Table "changes" field "action". In original DB it was type "CHAR(1)". But default value was '', so Mysql supposed it's not valid. You may choose New type. We've chosen CHAR(2) and click Next. The issue was fixed.

Repeat similar actions with any other troubled fields.

6. Click Next one more time. The application will start conversion showing you total and current progress. Wait till conversion is done.

7. Click "Exit". Conversion is done.

The author of this tool provides support for paying customers.

Price: $149 (one time)

Other premium options

We also tested trial versions of these tools. Here are general comments about them:

• pgs2scld (https://www.convert-in.com/pgs2sql.htm)
  No issues with connecting to database (no need to install additional drivers or set up them somehow or anything else). Has trial version (in trial mode copies only 50 records per table, but checks all the lines and shows the same progress as if the whole database was copied). Has internal debugger and shows possible issues with migration. Has lot of different options which can be applied during migration. Has 24/7 support for paid clients. UI only, Windows only.
  Price $49 (one time).
• ESF Database Migration Toolkit (https://www.dbsofts.com/articles/postgresql_to_mysql/)
  No issues with connecting to database (no need to install additional drivers or set up them somehow or anything else). Has trial version (in trial mode copies only 50000 records). Has internal debugger and shows possible issues with migration. Has lot of different options which can be applied during migration. Has 24/7 support for paid clients. UI only, Windows only.
  Price $322 (one time).