What is the reason to move database to PostgreSQL? The MySQL and PostgreSQL are well-known open-source RDBMS that comes with wide variety of management and development tools. Both solutions are available on virtually every well-known OS and they have large groups of specialists. Even so, PostgreSQL offers several benefits which can be vital for specific projects:
- 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
However, PostgreSQL is far more complicated than MySQL and can be challenging for newbie. Which means that, for those who work on easy database project that’s protected by MySQL features and don’t intend to scale it, there’s no much sense to move it from MySQL to Postgres.
Migration Techniques
The techniques explained here are some process required for the migration of database from MySQL to Postgres manually.
- All table definitions are extracted from the source database as DDL SQL statements:
- in phpMyAdmin navigate to ‘Export’ tab after highlighting the table, select ‘Custom’ option, set format to ‘SQL’ and verify radio-button ‘Structure’ is selected
- in MySQL console client the following statement can be executed
mysqldump -d -h (host) -u username -p databasename > dumpifle
- Translate those DDL statements in accordance with PostgreSQL format and send to the destination database. The only issue with this step is that you will need to thoroughly convert column types from MySQL to Postgres.
- Data of every MySQL table is exported into an intermediate format such as CSV file. You can accomplish it with the following process:
- phpMyAdmin – highlight the table, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘CSV’ and ensure that radio-button ‘Data’ is picked
- in MySQL console client the following statement can be executed
SELECT * INTO OUTFILE ‘table_name.csv’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’ FROM table_name
- The data in CSV files needs to be transformed in accordance with PostgreSQL format (when it is necessary) then loaded to the destination database.
- Finally views, stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code. Within phpMyAdmin and MySQL console client you can accomplish it by using these SQL-statements:
views =>
SELECT table_name, view_definition FROM information_schema.views
WHERE table_schema=’(your database name)’
stored procedures =>
SHOW PROCEDURE STATUS WHERE Db = ‘your database name’
triggers =>
SHOW TRIGGERS
- The resulting statements and source codes which are transformed into PostgreSQL format and loaded to the target database. Keep in mind that this task demands deep knowledges in MySQL and PostgreSQL dialects of SQL and database development.
The procedures stated above proves that the database migration from MySQL to Postgres is a very complex task. However, executing it manually can result in data loss or corruption as a result of the human factor. However, the best process is make use of appropriate software programs to automate the database conversion process. MySQL to Postgres migration utility by Intelligent Converters is one among such tools having all required features to manage large and complicated migration projects.