Among all open source RDBMS MySQL and PostgreSQL are recognized as the most popular systems enriched with lot of management frameworks and developer APIs. When compare these two RDBMS,they provide similar capabilities although PostgreSQL overcomes MySQL in following advantages:
- full compliance with ANSI SQL standard
- support for several models of indexing
- asynchronous commit
- support for both synchronous and asynchronous replication
This is the primary reason of why many organizations migrate their MySQL databases to PostgreSQL.
The process of database migration from MySQL to PostgreSQL is arranges according to extract-transform-load (ETL) concept and it consists of the following steps:
- MySQL table definitions are extracted in form of SQL CREATE-statements along with the corresponding indexes and constraints
- those statements are converted to the PostgreSQL data definition language (DDL) and then loaded into the target database
- MySQL data is exported into an intermediate comma separate values (CSV) files
- these CSV files are transformed according to PostgreSQL format and then loaded into the target database
- MySQL views, stored procedures/functions and triggers are extracted in form of SQL CREATE-statements and source code
- These statements and source code are converted to PostgreSQL format and loaded into the target database
- Finally, the most important step is to verify that all database objects were converted properly
The guide above illustrates that MySQL to PostgreSQL migration is quite complicated procedure demanding a lot of efforts when doing it manually. Also, it may cause data loss or corruption due to human errors. In view of these facts it is reasonable to use special software to automate the migration procedure.
MySQL to PostgreSQL converter is one of such tools provided by Intelligent Converters, a company specializing in database conversion, migration and synchronization since 2001. The tool has high performance due to direct reading and writing algorithms.
All versions of MySQL and PostgreSQL are supported including SAAS variations such as Amazon RDS and Heroku. MySQL to PostgreSQL converter supports command line for scheduling and automating the database migration.The product not only can migrate MySQL data into new database but also merge or synchronize it with existing PostgreSQL database.
Some migration projects require partial data transfer to the destination database.For this purpose,MySQL to PostgreSQL converter provides filtering data via SELECT queries. Below examples of using this feature are given for different tasks.
- Select particular rows: SELECT * FROM Some Table WHERE Key Column>100
- Rename columns: SELECT column123 AS Last Name, column_2 as Company FROM Some Table
- Do not migrate NULL values: SELECT * FROM Some Table WHERE Birthday IS NOT NULL
If it is required to modify data type in the destination table, MySQL to PostgreSQL converter can do custom type mapping. If PostgreSQL server forbids remote connections, the tool can export the data into SQL script. Then database administrator or other person responsible for migration can import the script file into the destination database using the standard tools like psql or phpPgAdmin.
