Transform MySQL to PostgreSQL
Why should you Move to PostgreSQL?
Both MySQL and PostgreSQL are well-known open-source RDBMS rich with great deal of administration and development tools. Both systems ported on every popular OS and also have large groups of professionals. However, MySQL is more easy to learn and use. Moreover, It offers tight integration with web and provides enough capabilities for most projects. Yet, PostgreSQL offers several benefits that may required for large and complicated data warehouses with advanced database logic inside:
- full compliance with ANSI SQL standard
- support for multiple indexing models
- synchronous and asynchronous replication are supported
- support for Common Table Expressions (CTE)
- full outer joins are supported
- unlike MySQL, Postgres works with arrays
On the other hand, PostgreSQL is a lot more complicated than MySQL which can be hard for newbies. So, in case you work with very simple database project that’s protecte by MySQL features and don’t intend to scale it, there’s no much sense to move it from MySQL to PostgreSQL.
Migration Techniques
In this section, a few most important steps of moving database from MySQL to PostgreSQL manually explained.
- First, all table definitions are obtained from the source database as DDL SQL statements. This task can tackled in two different ways:
- For instance, in phpMyAdmin you need to select table, then navigate to ‘Export’ tab, select Please select the ‘Custom’ option, set the format to ‘SQL’, and ensure that the ‘Structure’ radio-button is chosen.
- Similarly, the standard console export tool mysqldump can be used according to the documentation
mysqldump –d –h (host) –u (user) –p(password) (databasename) > (dumpifle)
Of course, all patterns in round brackets should substituted with actual values
- Second step is to translate those DDL statements in accordance with PostgreSQL format and load inside the destination database. In other words, the primary challenge of this specific step is to effectively convert column types from MySQL to PostgreSQL.
- Then the data of every MySQL table is exported into an intermediate format such as CSV file. Depending on the client software being used, you can do this by one of the following options:
- In phpMyAdmin, locate the table and navigate to the ‘Export’ tab. Choose the ‘Custom’ option, specify the format as ‘CSV’, and make sure to select the ‘Data’ radio-button.
- In the MySQL console client, employ the following statement:
SELECT * INTO OUTFILE (‘table.csv’)
FIELDS Finalized BY ‘,’ OPTIONALLY Confined BY ‘”‘
LINES TERMINATED BY ‘\n’ FROM (table)
Of course, all patterns in round brackets must be replaced by actual values
- The data in CSV files has to be transformed according to PostgreSQL format (if it is necessary) and then loaded into the destination database using the standard client tools such as phMyAdmin or psql:
- For instance, in pgMyAdmin you have to browse tables in the left pane of the interface, then right-click on the table where CSV data has to be imported and click on ‘Import’ menu item
- On the other hand, in command line client tool psql you should run this SQL statement
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
- After that, views, stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code. In both phpMyAdmin and MySQL console client it can be achieved by using these SQL-statements:
SELECT table_name, view_definition FROM information_schema.views
WHERE table_schema=’database_name’
SHOW PROCEDURE STATUS WHERE Db = ‘database_name’
SHOW TRIGGERS
- Finally, the resulting statements and source codes are converted into PostgreSQL format and loaded into the target database. Note that this step requires deep knowledges in MySQL and PostgreSQL dialects of SQL and database development.
MySQL to PostgreSQL Converter
In conclusion, the migration steps listed above indicate that the database migration from MySQL to PostgreSQL is a very challenging task. Therefore, carrying it out manually can lead to loss of data or corruption as a result of the human factor. Consequently, its recommended to use suitable software programs to automate the database migration process. For instance, MySQL to PostgreSQL database migration tool by Intelligent Converters is among such tools that has all required capabilities to manage large and complicated migration projects.
Above all, the program reaches amazing performance due to highly efficient multi-threading migration algorithm – more than 500 MB of data processed per minute on a modern hardware platform. In addition, the converter supports all modern versions of on-premises and cloud MySQL and PostgreSQL including such forks as MariaDB and Percona. Besides, the database migration can automated and scheduled via MySQL to PostgreSQL converter since it provides command line support. Also, existing PostgreSQL database can optionally merged or synchronized with MySQL data.
Furthermore, the tool provides some advanced features to make the migration even more flexible. For example, the converter can filter the data to migrate via SELECT queries. This option allows the selection of particular record and columns as well as transforming the data before its converted into PostgreSQL format. Here are few examples of using this feature:
- Filter records: SELECT * FROM TestTable WHERE IdColumn < 300
- Select and rename individual columns: SELECT col1 AS FirsttName, col2 as Mobile FROM TestTable
- Skip NULL values: SELECT * FROM TestTable WHERE Details IS NOT NULL
Conclusion:
Another useful feature that helps to change column name, type and other properties in the resulting database called ‘custom column mapping’ . For instance, it can set or clear NULL-attribute, default values as well as exclude some columns from migration.
Finally, when PostgreSQL database server prevents remote connection, MySQL database can exported into SQL script containing statements to create tables, indexes, constraints, views and fill tables with the data. Then database administrator can import that script file into PostgreSQL database via standard client tools such as phpPgAdmin or psql.
