How to Control Results of MySQL to PostgreSQL Migration

Irrespective of the method applied in migrating MySQL database to PostgreSQL server, it is expedient to check and be assured that all database objects have been properly converted. Whether the conversion is done manually or an automated tool is used, data integrity verification is necessary. The procedure is described briefly in this article. The first step after the migration is complete is to ascertain the objects in the destination database that require validation:
  • table definitions
  • data
  • indexes
  • foreign keys
  • views

Table Definitions

In MySQL exposes table definition is as follows:
  • In the command-line tool of MySQL run SQL statement DESC table_name
  • In phpMyAdmin select the table in the left pane and click on ‘Structure’ tab
However, in PostgreSQL table definition is explored by running the statement \d table_name One will be able to affirm that a MySQL table is properly converted when each column in a specified table in the two database types displays the same type, size and default values. Here is the table of correct type conversion from MySQL to PostgreSQL.

Data

The validation of converted data can be achieved by making a visual assessment of certain fragments from MySQL and PostgreSQL tables. MySQL enables exploration of data fragments as follows:
  • In mysql command-line tool, run SQL statement SELECT * FROM table_name LIMIT start_record, number_of_records
  • In phpMyAdmin select the table in the left pane and click on ‘Browse’ tab
PostgreSQL uses similar syntax of SELECT-query to extract fragment of data with a few particularities:
  1. SELECT * FROM table_name LIMIT number_of_records OFFSET start_record
Besides these, it is pertinent to verify that MySQL and PostgreSQL tables have the same number of row count. The following query can be applied on both DBMS to get the number of rows in a table:
  1. SELECT COUNT(*) FROM table_name

Indexes

In MySQL indexes can be listed as follows:
  • In mysql command-line tool, run SQL statement SHOW INDEXES FROM table_name;
  • In phpMyAdmin select the table in the left pane, click on ‘Structure’ tab and all indexes will be displayed immediately after table structure
In PostgreSQL, indexes information is displays at the end of table definition block generated by the statement: \d table_name;

Foreign Keys

MySQL exposes foreign keys information as follows:
  • In MySQL command-line tool, run SQL statement SHOW CREATE TABLE `table name`
  • In phpMyAdmin select the table in the left pane, go to ‘Structure’ tab and click ‘Relations view’ link below the table definition
In PostgreSQL information about foreign keys can be extract from service table "information_schema":
  1. SELECT
  2. tc.constraint_name, tc.table_name, kcu.column_name,
  3. ccu.table_name AS foreign_table_name,
  4. ccu.column_name AS foreign_column_name
  5. FROM
  6. information_schema.table_constraints AS tc
  7. JOIN information_schema.key_column_usage AS kcu
  8. ON tc.constraint_name = kcu.constraint_name
  9. JOIN information_schema.constraint_column_usage AS ccu
  10. ON ccu.constraint_name = tc.constraint_name
  11. WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='table_name';

Views

Regrettably, there is no other means of verifying that all views have been properly converted aside from comparison of SELECT statements from views in MySQL and PostgreSQL respectively. Do bear in mind the disparity in the SQL dialects of the two DBMS. This task places a demand on profound knowledge in database programming, it is therefore beyond the scope of this discourse. However, to get the list of all views in the source and destination database is not complex. In MySQL database, exposes list of all views is achieved by using the query:
  1. SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
In PostgreSQL, the same can be achieved via the query:
  1. SELECT table_name FROM INFORMATION_SCHEMA.views;

Add new comment