MySQL to PostgreSQL: database migration made easy

Open source RDBMs with strong programming and administration tools are available but special types are MySQL and PostgreSQL. As usual, every database administrator wants an upgrade over what he currently uses and that is why PostgreSQL is an option. Amongst its many benefits over MySQL are:

• Full compliance with ANSI SQL standards.
• Many models of indexing are supported.
• Asynchronous commit is permitted.
• It supports two types of replication – synchronous and asynchronous.

With such benefits, it is easy to conclude on the reasons anyone would make a switch.

The migration of database may be simple to complex depending on to modules involved but a schematic migration entails:

1. From source MySQL database, extract table definitions in form of DDL SQL statements.
2. Before loading to target database, convert the statements to destination format.
3. An intermediate storage such as CSV files should receive the exported data from MySQL database.
4. PostgreS database as destination format, transformation of the incoming data is necessary.
5. Triggers, stored procedures and views present in MySQL database is extracted from MySQL database. These data should be in form of source code and SQL statement.
6. According to the destination format, the statements and source codes are converted. Loading into PostgreSQL database should follow.
7. Crosscheck database objects for complete conversion.

Manual and physical migration of database from MySQL to PostgreSQL is time consuming and tedious. In fact, data corruption or data loss may be inevitable due to human factor. Therefore, it is always good to automate the process by using a conversion software.

For that reason, a particular software company had risen to the challenge. Established in 2001, Intelligent Converters is a software company that has continually strived to introduce programming tools into the market. Their specialization includes data synchronization, migration, and conversion. One of their creations is the MySQL to PostgreSQL converter which automates the conversion process. This converter is extremely functional and durable with as high as 10,000 records made per second. Compare this to average modern systems! This efficiency can be linked to the lack of need for middleware components to read and write data.

Cloud solutions are versions of MySQL and PostgreSQL that are supported. Also, database conversion is automated and scheduled with a customized command line version. In addition, synchronization of data after migration with target destination is possible – PostgreSQL database.

Furthermore, this tool allows filtration of data via SELECT queries. This is handy when there is need to migrate selected records to destination database. This options allows selection of particular columns and records, with transformation of data before conversion into PostgreSQL format. Examples are;

• Filtration of records
• Election and renaming individual columns

These steps are programming lines. Search online for detailed definition.

It can also be necessary to modify the type of column in the target PostgreSQL database. This is done through ‘custom column mapping’. This physical feature allows the modification of default value, type, name, and NULL-attribute for columns and tables while other columns cab be exempted from conversion.

Data can also be exported into SQL script if the target PostgreSQL server disallows remote connection. By mode of operation, a local file containing SQL-statements receives the source database.  Then tables with respective indexes and constraints are created after which they are filled with data. The generated script file is imported to PostgreSQL server. The data administrator uses standard tools like phpPgAdmin or psql at this stage.