How to Control Results of MySQL to PostgreSQL Migration

First, it is important to find out which objects must be validated in the destination database after migration is complete:

1. Table definitions
2. Data
3. Indexes
4. Foreign keys
5. Views

Table Definitions

MySQL exposes table definition as follows:

• In mysql console client run SQL statement DESC table_name
• In phpMyAdmin highlight the table in the left pane and go to 'Structure' tab

PostgreSQL explores table definition by running the statement \d table_name

You can say that MySQL table definition is converted properly when each column has equal type, size and default value in the resulting PostgreSQL table. Here is the table of appropriate conversions for each MySQL data type.

Data

Validation of data converted can be done by visual comparison of certain fragment from MySQL and Postgres tables. MySQL allows to explore data fragment as follows:

• In mysql console client run SQL statement SELECT * FROM table_name LIMIT start_record, number_of_records
• In phpMyAdmin highlight the table in the left pane and go to 'Browse' tab

PostgreSQL accepts similar syntax of SELECT-query to extract fragment of data with a few particularities:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

Also, it is necessary to verify that MySQL and PostgreSQL tables has the same count of rows. Both DBMS allows to get number of rows in a table using the following query:

SELECT COUNT(*) FROM table_name

Indexes

MySQL allows to list indexes as follows:

• In mysql console client run SQL statement SHOW INDEXES FROM table_name;
• In phpMyAdmin highlight the table in the left pane, go to 'Structure' tab and all indexes will be listed right after table structure

PostgreSQL displays information about indexes at the bottom of table definition generated by the command: \d table_name

Foreign Keys

MySQL exposes foreign keys information as follows:

• In MySQL console client run SQL statement SHOW CREATE TABLE `table name`
• In phpMyAdmin highlight the table in the left pane, go to 'Structure' tab and click 'Relations view' link below the table definition

PostgreSQL can extract information about foreign keys from service table "information_schema":

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='table_name';

Views

Unfortunately, there is no other way to verify all views have been converted properly besides comparing SELECT-statement of each view in MySQL and PostgreSQL having in mind differences between SQL dialects of these two DBMS. The task requires deep knowledges in database programming and so it stays outside this article. However, it is easy to get list of all views in source and destination databases.

MySQL exposes list of all views in the database using the query:

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

PostgreSQL can do the same via the query:

SELECT table_name FROM INFORMATION_SCHEMA.views;

Basic differences between MySQL and PostgreSQL queries are listed in this article.