How to Control Results of MySQL to PostgreSQL Migration

It does not matter if you migrate MySQL to PostgreSQL server manually or use a tool for this purpose, it is necessary to check that all database objects have been converted properly. This article is a brief guide on this procedure.

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 within the left pane and head to ‘Structure’ tab

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

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

posgresmysql

Data

Validation of data recreated can be done by visual comparison of a certain portion from MySQL and Postgres tables. MySQL permits 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 also accepts similar syntax of SELECT-query to extract portion of data with a few particularities:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

Also, it is essential to authenticate that MySQL and PostgreSQL tables have 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, head to ‘Structure’ tab and all indexes will be listed right after table structure

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

Foreign Keys

MySQL exposes foreign keys information as follows:

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

PostgreSQL can fetch information about foreign keys from service table “information_schema”:

SELECT

    t_c.constraint_name, t_c.table_name, k_c_u.column_name,

    c_c_u.table_name AS foreign_table_name,

    c_c_u.column_name AS foreign_column_name

FROM

    information_schema.table_constraints AS t_c

    JOIN information_schema.key_column_usage AS k_c_u

      ON t_c.constraint_name = k_c_u.constraint_name

    JOIN information_schema.constraint_column_usage AS c_c_u

      ON c_c_u.constraint_name = t_c.constraint_name

WHERE constraint_type = ‘FOREIGN KEY’ AND t_c.table_name=’table_name’;

Views

Sadly, there is no alternative way to validate that all views have been converted properly except comparing SELECT-statement of every view in MySQL and PostgreSQL having in mind variations between SQL dialects of these two database management system. The task needs deep knowledge in database programming and so it stays outside of this article. Though, it is easy to get a 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;