close
Tech

Validating MySQL to PostgreSQL Migration

Untitled

 

Verification of the resulting database is the most significant phase of the entire migration process. The first step of the verification procedure isidentifyingthe range of objects that must be checked. Here is list of the most important database entries that have to be migrated properly:

  1. Table structures with all necessary attributes
  2. Data
  3. Indexes and foreignkeys
  4. Views

TableStructures

There are two ways to expose table definitions inMySQL:

  • run SQL query DESC table_namein MySQL command line prompt

or

  • highlight the table in the left pane and click’Structure’ link in phpMyAdmin

PostgreSQL allows to browse table definitionsvia \d table_name query.

Table definition is considered as properly converted when every column in resulting PostgreSQL table has the same type, size and default value as in the original MySQLtable.

The Data

Conversion of thedata is validatedby comparison of random fragmentsof source MySQL and destination PostgreSQL tables. MySQL allows to extractfragments of data as follows:

  • run SQL query: SELECT * FROM table_name LIMIT start_record, number_of_recordsin MySQL command line prompt

or

  • highlight the table in the left pane and select ‘Browse’ tab in phpMyAdmin

PostgreSQL also provides SELECT-query to extract the fragment of data. It has similar syntaxwith MySQL one specified above:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

Besides data comparison, it is also importantto check that source and destination tables have equalnumber of rows. Both MySQL and PostgreSQL allow to get number of rows using thequery:

SELECT COUNT(*) FROM table_name

The Indexes

There are two ways to get information about indexes in MySQL:

  • run SQL query SHOW INDEXES FROM table_namein MySQL console

or

  • highlight the corresponding table in left pane of phpMyAdmin, click ‘Structure’ link and allindexes will appear right after the table definition

PostgreSQL listsindexes at the bottom of table DDL generated through the following statement: \d table_name

The Foreign Keys

MySQL offers two options to expose information about foreign keys:

  • run SQL-query: SHOW CREATE TABLE `table name`from MySQL command prompt

or

  • highlight the corresponding table in left pane of phpMyAdmin,select ‘Structure’ and click the ‘Relations view’ linkbelow table definition

PostgreSQL stores information about foreign keys inthe service table “information_schema”. It can be extracted via the following query:

SELECTtc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS fk_table_name,ccu.column_name AS fk_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

Theonlyway to validate conversion of viewsis to compare SELECT-statements of every view in source MySQL and destination PostgreSQL databases with respect to differences between SQL dialects of these two DBMS. Thistaskrequires extensive skills of database programming.

At the sametime, the views from source and the destination databases can be easily listed.MySQL lists all views through the following query:

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

PostgreSQL allows to list all views in the database using this query:

SELECT table_name FROM INFORMATION_SCHEMA.views; More articles about PostgreSQL can be found here: https://www.convert-in.com/docs/sql2pgs/contents.htm

admin

The author admin