Feature

Controlling MySQL to PostgreSQL Migration

258369
Written by admin

Whether you migrate database from MySQL to PostgreSQL server manually or use a dedicated software, it is necessary to verify that migration has been done properly. First, it is imperative to determine what kind of database objects have to be validated after migration is finished. It is table definitions, data, indexes, foreign keys and views.

258369

Table Definitions

Table definitions can be validates by comparing source and destination data definition statements (DDL).

  • MySQL console client allows to get table definition via the following SQL statement:DESC table_name
  • In phpMyAdmin highlight the table in the left sheet and go to “Structure” tab

PostgreSQL allows exposing table definition via the command:\d table_name

You can state that MySQL and PostgreSQL tablesare equal when type, size, “nullable” attribute and default values are equal for each column. Here is the table of suitable conversion for each MySQL information type into PostgreSQL:

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 TEXT
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

It is important to remember that unlike PostgreSQL, MySQL has a special attribute ‘auto_increment’, which automatically generates new values for column every time when the new line is embedded. PostgreSQL uses SERIAL type for the same purpose:

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

Also, all MySQL number types (tinyint, smallint, int, bigint) can have UNSIGNED characteristic, while in PostgreSQL they cannot. Here is the list of appropriate conversionsfor each MySQL unsigned type into PostgreSQL:

MySQL PostgreSQL
BIGINT UNSIGNED NUMERIC(20)
INT UNSIGNED BIGINT
MEDIUMINT UNSIGNED INTEGER
SMALLINT UNSIGNED INTEGER
TINYINT UNSIGNED INTEGER


Data

Approval of data conversion results should be possible by visual comparison of certain piece of data from MySQL and Postgres tables.

  • MySQL console client allows to get fragment of data via SQL statement: SELECT * FROM the_table LIMIT start_position, number_of_rows
  • In phpMyAdmin highlight the table in the left sheet and go to “Browse” tab

PostgreSQL uses the following SELECT-query forthe same purpose:

SELECT * FROM the_table LIMIT number_of_rows OFFSET start_position;

Also, it is necessary to check that MySQL and PostgreSQL tables have a similar number of rows. Both DBMS allowto get a row countof table via the following SQL statement:

SELECT COUNT(*) FROM the_table;

There is a challenge of converting data from MySQL to PostgreSQL is that MySQL permits to store ‘0000-00-00’ into date segments. PostgreSQL experts advise to replace such values by NULLs. If this solution breaks the database semantics by one means, you may consider to use another mapping for such date values.

Indexes

  • MySQL console client uses this SQL statementto extract indexes: SHOW INDEXES FROM the_table;
  • In phpMyAdmin highlight the table on the left pane, go to “Structure” tab and all indexeswill be listed directly after table structure

PostgreSQL displaysinformation about indexes at the bottom of DDL produced by the statement: \d table_name

Foreign Keys

  • MySQL console clientdisplays information about foreign keys at the bottom of DDL generated by this SQL query:SHOW CREATE TABLE `the table name`
  • In phpMyAdmin highlight the table on the left pane, go to “Structure” tab and locate ‘Relations’ section underneath the table definition

PostgreSQL can extractforeign keys definitions from service table “information_schema” as follows:

SELECT

tb.constraint_name, tb.table_name, kc.column_name,

cc.table_name AS foreign_table_name,

cc.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS tb

    JOIN information_schema.key_column_usage AS kc

      ON tb.constraint_name = kc.constraint_name

    JOIN information_schema.constraint_column_usage AS cc

      ON cc.constraint_name = tb.constraint_name

WHERE constraint_type = ‘FOREIGN KEY’ AND tb.table_name=’table name’;

Views

There is no other approach to validateviews converted into PostgreSQL format, but comparing SELECT-statement of each view in source and destination databases with respect to differences between SQL dialects of these two DBMS.  The task requires profound familiarities in database programming thus it remains out of scope of this guide. This is how to get list of all views in MySQL and PostgreSQL databases.

MySQL allows to get definitionsof all database views via thisquery:

SHOW FULL TABLES IN `database name` WHERE TABLE_TYPE LIKE ‘VIEW’;

PostgreSQL can do the same through the statement:

SELECT table_name FROM INFORMATION_SCHEMA.views;

About the author

admin

Leave a Comment