Home » Validate MySQL to PostgreSQL Migration
Technology

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:

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

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: 

  •   In MySQL, support customer-run SQL explanation DESC table_name
  •   In phpMyAdmin highlight the table in the left sheet and go to “Structure” tab

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

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: 

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

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: 

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

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: 

  •   In the MySQL console client, this SQL statement should be used: SELECT * FROM the_table LIMIT start_position, number_of_rows
  •   In phpMyAdmin highlight the table in the left sheet and go to the “Browse” tab

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: 

  •   In MySQL, the command-line client runs SQL statement SHOW INDEXES FROM the_table;
  •   In phpMyAdmin highlight, the table in the left sheet, go to “Structure” tab and all files will be recorded directly after the table structure.
 

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: 

  •   In MySQL command-line run SQL query SHOW CREATE TABLE `the table name`
  •   In phpMyAdmin highlight, the table in the left sheet, go to “Structure” tab and snap ‘Relations see’ interface underneath the table definition.

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. 

About the author

Shelly

Shelly is an avid reader, and the love of reading takes her to content writing eventually. She loves writing on various topics.

Add Comment

Click here to post a comment

All the data shown above will be stored by Techtricksworld.com on https://www.techtricksworld.com. At any point of time, you can contact us and select the data you wish to anonymise or delete so it cannot be linked to your email address any longer. When your data is anonymised or deleted, you will receive an email confirmation. We also use cookies and/or similar technologies to analyse customer behaviour, administer the website, track users' movements, and to collect information about users. This is done in order to personalise and enhance your experience with us.

Pin It on Pinterest