Tech

Validating Database Migration from MySQL to PostgreSQL

When a company realizes need of more powerful yet still free database management system, it may consider migration from MySQL to PostgreSQL.After database migration is completed, it is very important to verify that all database objects have been processed properly. This article explores main steps of the validation procedure (validation means to compare the corresponding entries in source and destination databases).

First step is to recognize what kind of objects must be validatedafter migration is completed:

  1. Table definitions
  2. Data
  3. Indexes
  4. Foreign keys
  5. Views

Table Definitions

In MySQL table definition can be displayed by running SQL query:DESC table_name

PostgreSQL exposes table definition by running the statement:\d table_name

MySQL table definition is migrated properly once every column hasproper type, size, NULL check and default value in the target PostgreSQL table. The tablesbelow illustratecorrect type mapping for MySQL to PostgreSQL migration.

MySQLPostgreSQL
BIGINTBIGINT
BINARY(n)BYTEA
BITBOOLEAN
CHAR(n), CHARACTER(n)CHAR(n), CHARACTER(n)
DATEDATE
DATETIMETIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s), DEC(p,s)DECIMAL(p,s), DEC(p,s)
DOUBLEDOUBLE PRECISION
FLOATREAL
INT, INTEGERINT, INTEGER
MEDIUMINTINTEGER
NUMERIC(p,s)NUMERIC(p,s)
SMALLINTSMALLINT
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOBBYTEA
TINYINTSMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXTTEXT
TIMETIME [WITHOUT TIME ZONE]
TIMESTAMPTIMESTAMP [WITHOUT TIME ZONE]
VARBINARY(n)BYTEA
VARCHAR(n)VARCHAR(n)

MySQL provides attribute ‘auto_increment’ for integer-like columns, that increases the value of the field automatically each time when new row is inserted. PostgreSQL uses SERIAL for the same purpose:

MySQLPostgreSQL
BIGINT AUTO_INCREMENTBIGSERIAL
INTEGER AUTO_INCREMENTSERIAL
SMALLINT AUTO_INCREMENTSMALLSERIAL
TINYINT AUTO_INCREMENTSMALLSERIAL

InMySQLall integer types (tinyint, smallint, int, bigint) can have UNSIGNED attribute. Unsigned specification increases upper range of acceptable values by forcing to take positive numbers only. Since PostgreSQL does not support unsigned numeric types, those types must be converted as follows:

MySQLPostgreSQL
BIGINT UNSIGNEDNUMERIC(20)
INT UNSIGNEDBIGINT
MEDIUMINT UNSIGNEDINTEGER
SMALLINT UNSIGNEDINTEGER
TINYINT UNSIGNEDINTEGER

Let us illustrate migration of CREATE TABLE statement from MySQL to PostgreSQL by following example. Assume, there is MySQL table defined as:

CREATE TABLE tbl1(

id int NOT NULL auto_increment,

added DATETIME,

data BLOB,

length INT UNSIGNED,

PRIMARY KEY(id)

);

PostgreSQL equivalent of this table definition is:

CREATE TABLE tbl1(

id SERIAL NOT NULL,

added TIMESTAMP,

data BYTEA,

length BIGINT,

PRIMARY KEY(id)

);

Data

Validation of migrated data begins with verifying MySQL and PostgreSQL tables have the same number of records. Both database management systems allow to obtain number of rows in a table through the following query:

SELECT COUNT(*) FROM table_name

Next verification that must be done is visual comparison of arandomdata fragmentsin MySQL and Postgres tables. MySQL allows to explore fragment of data as follows:

SELECT * FROM table_name LIMIT start_record, number_of_records

PostgreSQLsupports similar syntax of SELECT-query:

SELECT * FROM table_name LIMIT number_of_records OFFSET start_record

Indexes

Validation of migrated indexes includes comparison count of indexes in MySQL and PostgreSQL tables and checking that source and target indexes are the same.

MySQL allows to list indexes as follows: SHOW INDEXES FROM table_name;

PostgreSQL displays information about indexes at the end of table definition that is output of the statement: \d table_name

Foreign Keys

Verifying migrated foreign keys is very similar to indexes.

MySQL exposes information about foreign keys at the end of table definition that is output of the statement:SHOW CREATE TABLE `table name`

PostgreSQL can fetch foreign keys from service table “information_schema” through the following query:

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

Verifying migrated views is the most complicated part of overall validation,since it requires comparing CREATE VIEW statement of every view in MySQL and PostgreSQL databases with respect todifferences between SQL dialects of these two DBMS.

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

SELECT table_name, view_definition FROM information_schema.views WHERE table_schema=’database_name’;

PostgreSQL can do the same via the query:

select viewname, definition from pg_catalog.pg_views where schemaname NOT IN (‘pg_catalog’, ‘information_schema’)

Syntax of queries in MySQL and PostgreSQL is distinguished due to different sets of built-in functions.Therefore, while migrating views every MySQL embedded function must be replaced by PostgreSQL equivalent as it is specified in table below.

MySQLPostgreSQL
ADDATE($date, $interval)($date + $interval)::date
CURTIME()CURRENT_TIME
DAY($a) or DAYOFMONTH($a)EXTRACT(day from date($a))::integer
DATEDIFF($a, $b)$a – $b
DATE_FORMATTO_CHAR with proper format mask
HOUR($a)EXTRACT(hour FROM $a)::int
IFNULL($a,$b)COALESCE($a,$b)
INSTR($a, $b) or LOCATE ($a,$b)POSITION($b in $a)
ISNULL($a)$a IS NULL
LCASE ($a)LOWER($a)
LEFT($str, $len)SUBSTRING($1 FROM 1 FOR $2)
MID($str, $pos, $len)SUBSTRING($1 FROM $2 FOR $3)
MINUTE($a)EXTRACT(minute FROM $1)::int
MONTH($a)EXTRACT(month FROM $1)::int
ORD($a)ASCII($a)
RAND()RANDOM()
RIGHT($str, $len)SUBSTRING($1 FROM length($1) – $2 FOR $2)
SYSDATE()CURRENT_DATE
UCASE($a)UPPER($a)
UNIX_TIMESTAMP()EXTRACT(epoch FROM current_timestamp)
UNIX_TIMESTAMP($ts)EXTRACT(epoch FROM $ts)
UTC_DATE()CAST(NOW() at time zone ‘utc’ AS date)
UTC_TIME()TIMEZONE(‘UTC’, NOW())
WEEK($a)EXTRACT(week FROM ($a))::int
YEAR($a)EXTRACT(year from $1)

Some of MySQL built-in functions that cannot be converted into PostgreSQL by simple text replace. The workaround for such cases is to create those missing functions in the destination database:

CREATE OR REPLACE FUNCTION _group_concat(text, text)

RETURNS text AS $$

BEGIN

  SELECT CASE

    WHEN $2 IS NULL THEN $1

    WHEN $1 IS NULL THEN $2

    ELSE $1 operator(pg_catalog.||) ‘,’ operator(pg_catalog.||) $2

  END

END;

$$ LANGUAGE plpgsql;

DROP AGGREGATE IF EXISTS group_concat(text);

CREATE AGGREGATE group_concat (

     BASETYPE = text,

     SFUNC = _group_concat,

     STYPE = text

);

CREATE OR REPLACE FUNCTION makedate(year int, dayofyear int)

RETURNS date AS $$

BEGIN

SELECT (date ‘0001-01-01’ + ($1 – 1) * interval ‘1 year’ + ($2 – 1) * interval ‘1 day’):: date

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION maketime(int, int, double precision)

RETURNS time AS $$

SELECT time ’00:00:00′ + $1 * interval ‘1 hour’ + $2 * interval ‘1 min’

+ $3 * interval ‘1 sec’

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION strcmp(text, text)

RETURNS int AS $$

BEGIN

SELECT CASE WHEN $1 < $2 THEN -1

WHEN $1 > $2 THEN 1

ELSE 0 END;

END;

$$ LANGUAGE plpgsql;

More articles about MySQL to PostgreSQL migration can be found at: https://www.convert-in.com/docs/sql2pgs/contents.htm

Leave a Reply

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

Back to top button
Close
Close