Firstly, it is essential to identify which objects should be validated in the destination database soon after the migration process:

  1. The Table Definitions
  2. The Data
  3. The Indexes
  4. The Foreign Keys
  5. The Views

The Table Definitions

MySQL exposes table definition like this: –

  • In MySQL console client, you should run SQL statement as DESC table_name
  • In phpMyAdmin, highlight table that’s given in the left pane and select ‘Structure’

PostgreSQL explores the table definition by running this statement \d table_name

You will know that MySQL table definition is properly converted if every column has an equal type, size and default value in the corresponding PostgreSQL table. You can check out the table of proper conversions of MySQL data type from here.

The Data

Validating the converted data is done through visual comparison of certain fragments from MySQL and Postgres tables. MySQL permits you to explore data fragment in the following manner:

  • In MySQL console, run this SQL statement: SELECT * FROM table_name LIMIT start_record, number_of_records
  • In phpMyAdmin, just highlight the table given in the left pane and select ‘Browse’ tab

PostgreSQL accepts a similar syntax based on SELECT-query to extract the fragment of data with some particularities: –

“SELECT * FROM table_name LIMIT number_of_records OFFSET start_record”

Please note that is also essential to verify that the tables of MySQL and PostgreSQL have has the same number of rows. Both systems allow you to get a number of rows in table format using this query:

SELECT COUNT(*) FROM table_name

The Indexes

MySQL permits listing of indexes in this manner:

  • In MySQL console, run this SQL statement:- SHOW INDEXES FROM table_name;
  • In phpMyAdminconsole, highlight table given in left pane, pick ‘Structure’ tab and every index will be immediately listed right after the table structure

PostgreSQL will show info about indexes at the bottom of table definition which is generated by the following command: \d table_name

The Foreign Keys

MySQL exposes the foreign keys info like this:

  • In MySQL console, run this SQL statement:- SHOW CREATE TABLE `table name`
  • In phpMyAdminpanel, highlight table given in left pane, select ‘Structure’ and click the ‘Relations view’ option which is below the table definition.

PostgreSQL is capable of extracting infoabout the foreign keys from the service table “information_schema”:

SELECTtc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,ccu.column_name AS foreign_column_nameFROM 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_nameWHERE constraint_type = ‘FOREIGN KEY’ AND tc.table_name=’table_name’;

The Views

There are no options to verify every view that has been properly converted besides comparing the SELECT-statement of every view in both MySQL and PostgreSQL keeping in mind the differences between SQL dialects of these two systems. The job demands a deep knowledge of database programming and so it is outside the purview of this article. On the other hand, it is very easy to obtain the list of all the views in source and the destination databases.

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

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE ‘VIEW’;

PostgreSQL can perform the same job with this query:

SELECT table_name FROM INFORMATION_SCHEMA.views;