Validating Results of MySQL to PostgreSQL Migration

Read Time:2 Minute, 54 Second

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.

Every validation begins from determining the scope of objects must be validated after migration is completed:

Table definitions
Data
Indexes
Foreign keys
Views

Table Definitions

MySQL exposes table definition as follows:

In MySQLcommand prompt execute the statement DESC table_name;
phpMyAdmin requires highlighting the table on the left pane and navigating 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.

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 MySQLcommand prompt execute SQL statement SELECT * FROM table_name LIMIT start_record, number_of_records
phpMyAdmin requires highlighting the table on the left pane and navigating 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 database management systems extract number of table records through the following query:

SELECT COUNT(*) FROM table_name

Indexes

MySQL allows to list indexes as follows:

In MySQLcommand prompt execute SQL querySHOW INDEXES FROM table_name;
phpMyAdmin requires highlighting the table on the left and navigating to ‘Structure’ tab.Then all indexes will be listed right after the table definition.

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`
phpMyAdmin requires highlighting the table on the left and navigatingto ‘Structure’ tab.Then 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 allows extracting all views of the database through the following query:

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

PostgreSQL provides this query for the same purpose:

SELECT table_name FROM INFORMATION_SCHEMA.views;