Database migration from MySQL 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:
- Table definitions
- Data
- Indexes
- Foreign keys
- Views
Table of Contents
Table Definitions
There are two options to explore MySQL table definition. When using MySQL command line client, just run the statement: DESC table_name. In case of using popular MySQL database management tool phpMyAdmin, highlight the table in the left pane and go to ‘Structure’ tab.
In order to explore PostgreSQL table definition, run the statement \d table_name
Correct conversion of table definition from MySQL to PostgreSQL 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 column has ‘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 in source and destination tables. There are two options to review data fragment in MySQL. When using MySQL command line client run, just run: SELECT * FROM table_name LIMIT start_record, number_of_records.
In case of using phpMyAdmin database administration tool, 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 from MySQL to PostgreSQL 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 query SHOW INDEXES FROM table_name;
- To do the same in phpMyAdmin, highlight the table name on the left, go to ‘Structure’ tab and all index definitions go right after the table structure
PostgreSQL lists all 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 from MySQL to PostgreSQL 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 query SHOW CREATE TABLE `table name`
- To do the same from phpMyAdmin, 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 validate converted 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 MySQL to PostgreSQL migration at the official site of Intelligent Converters, software company specializing in database migration and synchronization for years.