15/08/2022

Thecarpetlibrary.com

All Information Blog

Migrate MySQL to PostgreSQL

Why Migrate? MySQL and PostgreSQL are the two known as particularly popular open up-source RDBMS...

Why Migrate?

MySQL and PostgreSQL are the two known as particularly popular open up-source RDBMS provided with a large amount of tools for administration and enhancement applications. These databases administration methods are ported on all well-known OS and have huge communities of lovers. At the identical time PostgreSQL supplies various benefits in contrast to MySQL that may perhaps be crucial for some jobs:

  • 100% compatibility with ANSI SQL common
  • several indexing designs are supported
  • synchronous and asynchronous replication
  • Frequent Table Expressions (CTE)
  • support for entire outer joins

Nevertheless, PostgreSQL is much more advanced than MySQL and requires extra expert workers for routine maintenance and enhancement. Which is why it is not proposed to migrate straightforward databases assignments from MySQL to PostgreSQL in circumstance there are no strategies to scale it.

Strategies to Migration

There are few simple steps necessary to migrate MySQL database to PostgreSQL.

  1. Definition of every single MySQL tableis extracted in kind of info definition language (DDL) SQL statements. To do this in phpMyAdmin highlight the correct table, go to ‘Export’ tab, pick ‘Custom’, set format possibility to ‘SQL’ and make guaranteed that radio-button ‘Structure’ is picked. In MySQL command line surroundings the pursuing assertion need to be used:

mysql dump -d -h (host) -u (user) –p(password) (databases title) > (dumpifle)

All styles in parenthesis must be replaced by genuine values.

  1. Each of these DDL statements should be translated into PostgreSQL structure and load to the target databases. It is essential to transform every single MySQL column variety into correct PostgreSQL equal.
  2. Then info of each and every MySQL desk is exported into comma separated values(CSV) file. This endeavor can be completed in phpMyAdmin by highlighting desk, navigating to ‘Export’ tab, picking out ‘Custom’ solution, setting structure to ‘CSV’ and activating radio-button ‘Data’.In MySQL console consumer the subsequent assertion need to be employed:

Pick out * INTO OUTFILE (‘table.csv’)

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘

Traces TERMINATED BY ‘n’ FROM (desk)

All patterns in parenthesis must be changed by true values.

  1. If it is necessary the information from CSV data files is remodeled according to PostgreSQL structure and then loaded into the concentrate on databases.
     
  2. On the remaining phase of the migration procedure all sights, stored procedures and triggers are extracted from MySQL databases in form of SQL statements and source code. It can be finished utilizing these SQL-statements in both equally phpMyAdmin and MySQL console environments:

views

Select table_name, watch_definition FROM data_schema.sights

The place table_schema=’(your database name)’

saved methods

Present Treatment Standing The place Db = ‘your database name’

triggers

Present TRIGGERS

  1. Individuals statements and source codes have to be remodeled in accordance to PostgreSQL format and loaded to the place database.

The quick manual previously mentioned illustrates that database migration from MySQL to PostgreSQL is a complicated activity. Guide technique to this process could lead to dropping knowledge integrity due the human issue. Fortunately, there are devoted software equipment to migrate MySQL to PostgreSQL offered in the industry. 1 of this sort of tools having all important features to cope with large and elaborate assignments is provided by Clever Converters,computer software corporation specializing in databases migration and synchronization given that 2001.