Validating PostgreSQL to MySQL Migration

The most important stage of migration from PostgreSQL to MySQL is validating the destination database. This article explores validation process for the following database objects:

  • Table definitions
  • Data
  • Indexes
  • Foreign keys
  • Views

Table Definitions

To explore table definition in PostgreSQL run SQL statement d table_name

Table definition can be obtained in MySQL in the two ways:

  • The standard mysql console client exposes it by running SQL statement DESC table_name
  • phpMyAdmin exposes related information for highlighted table in the left pane on ‘Structure’ tab

PostgreSQL table is migrated to MySQL properly if every column has equal type, size and default value compared to the resulting table. The appropriate types mapping goes below:

PostgreSQL MySQL
ARRAY LONGTEXT
BIGINT BIGINT
BIGSERIAL BIGINT auto_increment
BOOLEAN BIT
BYTEA LONGBLOB
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
CIDR VARCHAR(43)
DATE DATE
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION DOUBLE
FLOAT(p) FLOAT(p)
INET VARCHAR(43)
INT, INTEGER INT
INTERVAL TIME
JSON LONGTEXT
LINE LINESTRING
MONEY DECIMAL(19,2)
NUMERIC(p,s) DECIMAL(p,s)
REAL FLOAT
SERIAL INT IDENTITY
SMALLINT SMALLINT
TEXT LONGTEXT
TIME(p) TIME(p)
TIMESTAMP DATETIME
TSVECTOR LONGTEXT
TSQUERY LONGTEXT
UUID VARCHAR(36)
VARCHAR(n) VARCHAR(n)
XML LONGTEXT

Data

Validation of migrated data should begin from verifying that PostgreSQL and MySQL tables has the same count of rows. Both database management systems allow to extract number of table rows via the following query:

SELECT COUNT(*) FROM table_name

Further validation can be done through manual comparison of random fragments of Postgres and MySQL tables. PostgreSQL allows extract fragment of data starting from the specified offset as follows:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

MySQL provides similar SELECT-query for the same purpose:

SELECT * FROM table_name LIMIT start_record, number_of_records

Indexes

Index is migrated properly if in the target database it has the same column list and properties as in the original one. PostgreSQL exposes information about indexes at the bottom of table definition generated by the command: d table_name

MySQL lists indexes via one of the following options:

  • via SQL statement SHOW INDEXES FROM table_name;
  • In phpMyAdmin highlight the table in the left pane, go to ‘Structure’ tab and all indexes will be listed right after table structure

Foreign Keys

Criteria of correct migration for foreign keys is the same as for indexes. 

PostgreSQL extracts foreign keys data from service table “information_schema” using the query:

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’;

MySQL allows to extract foreign keys data using one of these options:

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

Views

Validation of migrated views requires comparing SELECT-statement of each view in PostgreSQL and MySQL having in mind differences between SQL dialects of these two DBMS. 

PostgreSQL exposes list of all views in the database using the query:

SELECT table_name FROM INFORMATION_SCHEMA.views;

MySQL can do the same via the query:

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

Syntax of queries in PostgreSQL and MySQL is similar, yet not equal. PostgreSQL uses double quotes for delimited identifiers, while MySQL uses back-ticks for the same purpose. 

Also, it is necessary to convert PostgreSQL functions and operators missing in MySQL into proper equivalents:

  • String concatenation operator || must be replaced by MySQL function CONCAT
  • All PostgreSQL casting operators like $1::int must be converted into MySQL CAST ($1 AS $type) calls
  • PostgreSQL function TO_CHAR ($date, $format) must be replaced by MySQL equivalent DATE_FORMAT ($date, $format). Format specifiers are translated as follows:
PostgreSQL MySQL Description
YYYY %Y 4-digit year 
YY %y 2-digit year
Mon %b Abbreviated month, e.g. Jan
Month %M Month name, e.g. January, February
MM %m Month number (1 – 12)
HH or HH12 %h Hour (1 – 12)
HH24 %H Hour (0 – 23)
MI %i Minutes (0 – 59)
SS %s Seconds (0 – 59)
  • All occurrences of CHR($a) must be replaced by CHAR($a)
  • PostgreSQL function RANDOM() is converted into MySQL RAND()

To guarantee correct database migration it is reasonable to automate the process using dedicated software like PostgreSQL to MySQL converter offered by Intelligent Converters. 

Enable registration in settings - general