Lachie’s Blog

Lachies Latest News Magazine

MySQL to PostgreSQL: Validating Results of Migration

Database migration fromMySQL to PostgreSQL becomes quite popular procedure on the way of scaling corporate data warehouses or building complicated data-driven applications.At the end of migration procedure, it is important to check that all database objects have been converted properly.

This is the list of most important object categories that must be validated in the resulting PostgreSQL database:

  1. Tabledefinitions
  2. Data
  3. Indexes
  4. Foreignkeys
  5. Views

TableDefinitions

There are two options to exploreMySQL table definition:

  • In mysql console client run the queryDESC table_name
  • In phpMyAdmin highlight the table in the left pane and go to ‘Structure’ tab

In order to explorePostgreSQL table definition,run the statement \d table_name

Correct conversion of MySQL table definition into PostgreSQL format means each column has equal type, size and default value in both source and target tables. This is the table of appropriate conversions for each MySQL data type:

MySQL PostgreSQL
BIGINT BIGINT
BINARY(n) BYTEA
BIT BOOLEAN
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
DATE DATE
DATETIME TIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE DOUBLE PRECISION
FLOAT REAL
INT, INTEGER INT, INTEGER
MEDIUMINT INTEGER
NUMERIC(p,s) NUMERIC(p,s)
SMALLINT SMALLINT
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTEA
TINYINT SMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT
TIME TIME [WITHOUT TIME ZONE]
TIMESTAMP TIMESTAMP [WITHOUT TIME ZONE]
VARBINARY(n), VARBINARY(max) BYTEA
VARCHAR(n) VARCHAR(n)
VARCHAR(max) TEXT

If MySQL columnhas ‘auto_increment’ property, it must be converted into PostgreSQL SERIAL type and its modifications that are used for the same purpose:

MySQL PostgreSQL
BIGINT AUTO_INCREMENT BIGSERIAL
INTEGER AUTO_INCREMENT SERIAL
SMALLINT AUTO_INCREMENT SMALLSERIAL
TINYINT AUTO_INCREMENT SMALLSERIAL

Data

Migrated data can be validated by visual comparison of some data fragment insource and destination tables. There are two options to review data fragmentin MySQL:

  • In mysql console client run the query SELECT * FROM table_name LIMIT start_record, number_of_records
  • In phpMyAdmin highlight the table in the left pane and go to ‘Browse’ tab, then navigate to the particular data fragment through the page list

PostgreSQL has similar syntax of SELECT-query to review the particular fragment of data:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

Also, it is necessary to verify that source and destination tables has the equal count of rows. Both MySQL and PostgreSQL provide the same statement to get number of rows in a table:

SELECT COUNT(*) FROM table_name

 

Indexes

Validation of indexes requires to verify that all indexes are converted and each index in the destination database has the same columns and attributes as in the source. MySQL provides two options of listing indexes:

  • In mysql console client run the querySHOW INDEXES FROM table_name;
  • To do the same in phpMyAdmin, highlight the table name on the left, go to ‘Structure’ tab and all index definitionsgo right after the table structure

PostgreSQL listsall indexes at the bottom of table definition generated by the command: \d table_name

Foreign Keys

Similar to indexes, validation of foreign keys includes checking all of them have been migrated and each foreign key has the same list of columns and attributes in source and destination databases. There are two options to explore foreign keys in MySQL:

  • In MySQL console client run the querySHOW CREATE TABLE `table name`
  • To do the same fromphpMyAdmin, highlight table name on the left, then go to ‘Structure’ tab and click ‘Relations view’ link below the table definition

PostgreSQL allows to extract foreign key details from internal table “information_schema” through the following statement:

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

The only way to validateconverted views is to compare SELECT-statement of each view in source and destination databases with respect to differences between SQL dialects of MySQL and PostgreSQL. The task requires deep knowledges in database programming and it is laid outside this article.

Learn more about database migration from MySQL to PostgreSQL at https://www.convert-in.com/docs/sql2pgs/intro.htm

Leave a Reply

Your email address will not be published. Required fields are marked *