Posted in:

Migrate Database from PostgreSQL to MySQL

© by PostgreSQL to MySQL

Despite the fact that PostgreSQL is effective and open-source database administration framework, here and there its extensive variety of elements and capacities seem overpowering for specific tasks. For this situation, associations may consider exchanging their database to another DBMS that is all the more simple to utilize. The primary reason for such relocation is to lessen the complexity and cost of database development and administration. 

Here is the arrangement of ventures to relocate database from PostgreSQL to MySQL: 

–     export PostgreSQL tables, indexes, constraints and views definitions from the source database 

–     convert them into MySQL format

–     load the subsequent proclamations to the target MySQL database

–     export PostgreSQL rows into INSERT-statements

–     convert these statements into MySQL format

–     load the resulting SQL code into the target database 

PostgreSQL table definitions can be sent out into DDL script by means of the accompanying summon:

pg_dump –schema-only –no-owner –no-privileges the_db_name > table_definitions.sql

The subsequent script must be rectified before stacking to MySQL as takes after: 

  • replace all PostgreSQL types missing in MySQL by the most accurate equivalents (based on the range of accepted values)
  • replace double quotes around database object names (tables, indexes, views) by backticks 
  • remove default PostgreSQL schema “public”
  • replace all PostgreSQL “SERIAL” sorts by MySQL “INT AUTO_INCREMENT” 

PostgreSQL information can be traded into INSERT-statements by means of the accompanying command line: 

pg_dump –data-only –column-inserts the_db_name > load_data.sql 

Another alternative to relocate database from PostgreSQL to MySQL is to utilize the MySQL Workbench. Here is the succession of vital strides: 

  • Download and install 32- or 64-bit (depending on your system) psqlODBC driver 
  • Open MySQL Workbench and go Database > Migration. Then you should see migration wizard launched
  • Click ‘Start Migartion’ on the ‘Overview’ page. Select PostgreSQL and the source and ODBC (manually entered parameters) as the connection method. 
  • Enter connection details and click ‘Test Connection’ to check that everything is correct. 
  • Enter connection details for MySQL on the ‘Target Selection’ page. Click ‘Test Connection’ to check that everything is correct. 
  • Wait while the tool extracts list of available PostgreSQL schemas. Select schema to migrate and the method of names mapping. 
  • Click ‘Show Selection’ to list all available database objects and choose which to migrate on the ‘Source Objects’ page.  
  • Use ‘View’ to customize how to migrate objects and fix types mapping on the ‘Manual Editing’ page
  • After a few more clicks the migration project will run

As you may see both methodologies require a considerable measure of manual work and get to be distinctly dull process for huge and complex databases. In addition, there is danger of information misfortune or debasement associated with the human variable. To stay away from it and make the database movement from PostgreSQL to MySQL a completely computerized handle, unique database change instrument might be utilized. 

PostgreSQL to MySQL converter is one of such apparatuses gave by Intelligent Converters, a product organization gaining practical experience in database relocation and synchronization for all prominent DBMS since 2001. The database change instrument has every single fundamental element: 

  • All renditions of PostgreSQL and MySQL (including DBaaS such as Azure, Amazon RDS, Google Cloud, etc) are upheld 
  • Indexes are changed over with every single fundamental trait 
  • Option to consolidate PostgreSQL information into a current MySQL tables 
  • Command line support
  • Stores change settings into profile 
  • Unicode support

PostgreSQL to MySQL converter keeps running on Windows 7/8/10/11 and Linux via WINE emulator. It requires access to live instance of PostgreSQL server, sufficient permissions in source and target database for reading and writing the data.

The products comes with free demo version that converts not more than 50 rows per table and does not convert foreign keys. As every product produced by Intelligent Converters, PostgreSQL to MySQL converter is supplied with 1-year subscription for updates and unlimited tech support.