MySQL and PostgreSQL are known as outstanding DBMS supplied with a lot of frameworks and APIs. At the same time, there are significant advantages giving Postgres the win against MySQL. PostgreSQL offer support for both synchronous and asynchronous replication, provides valuable asynchronous commit feature. Moreover, it can work with different models of indexing and it is completely compiled with ANSI SQL standard.
Being aware about those advantages provided by PostgreSQL, many organizations have decided to migrate from MySQL to PostgreSQL. In most common cases, database migration is a hard and tedious procedure organized into the following steps:
- The table definitions with their indexes and restrictions should be extracted from the initial database to create SQL CREATE-statements
- These CREATE-statements are then converted into destination files and loaded into the final database
- Data from MySQL is exported in form of a CSV file
- In the form of SQL statements or source code views, triggers and stored procedures are extracted from the original database
- These statements and the source code will be converted to a destination file and then loaded into PostgreSQL database and you are done!
As mentioned, database migration can be a very difficult process carrying it out manually. To add, if not handled properly all of your data could get corrupted. Considering this in mind, it would be better to opt for specialized software for database migration.
Luckily, there are several tools to migrate from MySQL to PostgreSQL available in the market. One of those tools has been developed by Intelligent Converters — software vendor working in this sector of database migration since 2001. Some benefits of the converter are listed below:
- This tool is rapid (it can migrate from MySQL to PostgreSQL at the rate about 20,000 records per second on an average modern computer). This is achieved by its direct reading and writing of data without using any intermediary libraries
- It can work with any version of MySQL or PostgreSQL including cloud solutions
- It comes with a command line version
- It allows the user to not only migrate old MySQL data onto a new database but it also can be used to join or synchronize converted data with old PostgreSQL database
Another feature which makes this converter stand out is the filtering option implemented via SELECT-queries. It can help to select certain records meeting the specified criteria to migrate from MySQL to PostgreSQL. Select particular columns, or records and even transform it beforehand. Then convert it to PostgreSQL format. This feature can be used in different ways:
- To filter records
- To select and rename individual columns
- To join multiple tables
Usually, you are required to alter the column type when transferring data into a new database. To help with this process the converter has a feature named “custom column mapping”. Through this feature, you can edit different attributes of a column. It will also let you decide which columns you want to include in the edit.
Another advantage of this converter is that in case the target server has no option for remote connection, you can migrate from MySQL to PostgreSQL via SQL script. For this purpose, the local database is converted into a document with SQL-statements with command for creating tables as per requirement. Then this file can be imported into the PostgreSQL server and used with tools like phpPgAdmin or psql.
Finally, as every tool offered by Intelligent Converters, this converter provides high performance of the migration due to using the most efficient low-level approaches to reading and writing data: it is MySQL C API (aka libmysql) to read the source data and C application programmer’s interface to PostgreSQL (aka libpq) to write into the target database.
Must Read: Introduction to Automation Testing
Besides, the tool implements smart multi-threading algorithm to migrate from MySQL to PostgreSQL medium and large size tables. The main feature of this unique approach is to configure all parameters automatically depending on the hardware platform. The amount of available memory determines volume of single transaction and number of physical CPU cores sets up the concurrent threads.
Learn more about MySQL to PostgreSQL converter and other interesting aspects of database migration on the official site of Intelligent Converters.