Validate MySQL to PostgreSQL Migration

If you are looking at website and application development, use of the right database is extremely important. We have two major database management systems, typically known as DBMS. There are times when many developers are looking for the ways to migrate from one database to another. 

While an important task, it can be daunting as it can have errors when migrating. 

However, once the migration is done, the next important step is to validate the MySQL to PostgreSQL Migration. 

How to validate MySQL to PostgreSQL Migration?

Before we begin validating the migration, we need to have a look at the objects that need to be validated. 

The major elements that need validation would include:

Let us find the steps that you need to follow to validate each of these elements –

Table Definitions 

MySQL uncovered the table definition as follows: 

PostgreSQL investigates table definition by running the announcement \d table_name 

You can state that MySQL table definition is changed over legitimately when every segment has broken even with sort, size, and default esteem in the subsequent PostgreSQL table. Here is the table of suitable transformations for each MySQL information sort: 

MySQL and PostgreSQL have comparative information sorts. Some of them are equal while others are most certainly not. At the point when arranging MySQL to PostgreSQL migration it is critical to recall the accompanying table of the right sorts of mapping: 

MySQL has a contention for the number like segments called ‘auto_increment’, which builds the estimation of the field naturally every time when the new line is embedded. PostgreSQL utilizes SERIAL sort and its adjustments for a similar reason: 

Not at all like PostgreSQL, all MySQL number sorts (tinyint, smallint, int, bigint) can have UNSIGNED characteristic. Unsigned determination powers to bring positive numbers just with the bigger upper scope of adequate qualities. Here are the means by which unsigned numbers must be mapped during MySQL to PostgreSQL migration: 

Another test while mapping sorts are to recollect that dissimilar to PostgreSQL, MySQL permits to store ‘0000-00-00’ into date segments. PostgreSQL experts propose to supplant such values by NULLs while moving information from MySQL. In any case, if this approach by one means or another breaks the database rationales, you ought to consider utilizing another mapping for such date sections. 

Contents  

Approval of information changed over should be possible by visual correlation of certain pieces from MySQL and Postgres tables. MySQL permits to extraction of table contents as follows: 

PostgreSQL acknowledges comparative sentence structure of SELECT-question to concentrate piece of information with a couple of particularities: 

SELECT * FROM the_table LIMIT number_of_rows OFFSET start_position; 

Likewise, it is important to confirm that MySQL and PostgreSQL tables have a similar tally of lines. Both DBMS permits to get a number of lines in a table utilizing the accompanying question: 

SELECT COUNT(*) FROM the_table;

Indexes 

This section explores the validation of converting indexes during MySQL to PostgreSQL database migration. MySQL permits listing records as follows: 

PostgreSQL shows data about files at the base of table definition produced by the order: \d table_name 

Foreign Keys

Foreign keys is another important part of validation when migrating a database from MySQL to PostgreSQL. MySQL uncovered outside key data as follows: 

PostgreSQL can remove data about remote keys from administration table “information_schema”: 

SELECT

    tabcon.constraint_name, tabcon.table_name, keycol.column_name, 

    concol.table_name AS foreign_table_name,

    concol.column_name AS foreign_column_name 

FROM 

    information_schema.table_constraints AS tabcon 

    JOIN information_schema.key_column_usage AS keycol

      ON tabcon.constraint_name = keycol.constraint_name

    JOIN information_schema.constraint_column_usage AS concol

      ON concol.constraint_name = tabcon.constraint_name

WHERE constraint_type = 'FOREIGN KEY' AND tabcon.table_name='the_table_name'; 

Views

Lamentably, there is no other approach to confirm the sum total of what perspectives have been changed over appropriately other than looking at SELECT-proclamation of every view in MySQL and PostgreSQL having at the top of the priority list contrasts between SQL lingos of these two DBMS. This would need a complete  knowledge of the programming database. That is exactly why we thought of keeping it outside the purview of this post. 

 In any case, it is anything but difficult to get the rundown of all perspectives in source and goal databases. 

MySQL uncovered rundown of all perspectives in the database utilizing the inquiry: 

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

PostgreSQL can do the same through the inquiry: 

SELECT table_name FROM INFORMATION_SCHEMA.views;

Migrating from MySQL to PostgreSQL is a really difficult task. But, more complex is the task of validating the migration process. When handling your migration process, make sure to assess your MySQL database, plan your migration, export the data, import the data, create the database, and then test it thoroughly. 

When it comes to testing your data, the above discussion and steps should help you arrive at the best possible data migration.