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. Below is the list of PostgreSQL key advantages over MySQL:
- 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.
Such benefits make the real reason for many database users to switch from MySQL to PostgreSQL. The migration of database may be simple to complex depending on to modules involved but a schematic migration entail:
- From source MySQL database, extract table definitions in form of DDL SQL statements.
- Before loading to target database, convert the statements to destination format.
- An intermediate storage such as CSV files should receive the exported data from MySQL database.
- PostgreS database as destination format, transformation of the incoming data is necessary.
- Triggers, stored procedures and views present in MySQL database is extracted from MySQL database in form of SQL source code and CREATE-statements.
- According to the destination format, the statements and source codes are converted. Loading into PostgreSQL database should follow.
- 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, and it is reasonable to automate the process through a dedicated software.
Being founded in 2001, Intelligent Converters has continually strived to introduce high quality database migration and synchronization tools into the market. One of their products is MySQL to PostgreSQL converter which automates the migration of MySQL, MariaDB or Percona databases to PostgreSQL server or cloud platform such as Azure or Heroku. This converter is extremely functional and offers high performance of the migration bench marked as 10,000 records made per second. Compare this to average modern systems due to the lack of need for middle ware 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, the tool can do non-incremental synchronization of data after initial migration is completed.
Another flexible and powerful capability provided byMySQL to PostgreSQL converteris filtration of data via SELECT queries. This feature allows selection of columns and records according to the specified filter, with transformation of data before migrating to PostgreSQL.
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.
The converter can also export MySQL data into SQL script if the target PostgreSQL server disallows remote connection. By mode of operation, this local file contains SQL-statements extracted from the source database and transformed to comply with PostgreSQL dialect of SQL. Those SQL-statements cover creation of tables with respective indexes and constraints and inserting the data. The person responsible for database migration can import that script file to PostgreSQL server via standard tools like phpPgAdmin or psql.
