The reasons for Migration to PostgreSQL
Either MySQL or PostgreSQL are well-known open-source RDBMS that are backed up by wide variety of administrative and developmental tools. Both of these processes are designed for every popular operating system; there are also a league experts in place. Nevertheless, PostgreSQL offers several benefits that could be crucial for some 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
- Contrary to MySQL, Postgres works with arrays
Nevertheless, PostgreSQL is a lot more advanced than MySQL and could also be confusing for beginners. So, for anyone that works on simple database project that’s processed by MySQL features and you don’t intend to scale it, you don’t have to migrate it from MySQL to Postgres.
Migration Techniques
The procedures outlined below are necessary for the manual migration of database from MySQL to Postgres.
- Each table descriptions are taken from the source database as DDL SQL statements. This is how it can be done with different client tools:
- in phpMyAdmin web interface highlight the table, then select ‘Custom’ option on ‘Export’ tab, set format to ‘SQL’ and make sure radio-button ‘Structure’ is selected
- for MySQL console client – use thethat command line call
mysqldump -h {host} -u {user} –p {database} > (dumpifle)
All forms in the round brackets need to be changed with the exact values
- Convert those DDL statements in accordance to PostgreSQL format and upload into the destination database. The main obstacle of this level is to correctly convert column types from MySQL to Postgres. Take a look at the article Types Mapping for step-by-step information about all forms of conversion.
- Data of every single MySQL table is exported into an intermediate format such as CSV file. This can be achieve with the following process:
- In web interface phpMyAdmin highlight the table, then select ‘Custom’ option on ‘Export’ tab , set format to ‘CSV’ and check that option ‘Data’ is selected
- In MySQL console client use the statement
SELECT * INTO OUTFILE (‘table.csv’)
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’ FROM (table)
All forms in the round brackets need to be changed with the exact values
- The data contained in the CSV files has to be adjusted with respect to PostgreSQL format (if it is necessary) and then loaded into the destination database.
- At last, views, stored procedures and triggers are taken from MySQL database in form of SQL statements and source code. For both phpMyAdmin and MySQL console client, it can be done 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 ensuing statements and source codes are transformed into PostgreSQL format and later on loaded into the target database. Remember that this process needs deep knowledges in MySQL and PostgreSQL dialects of SQL and database development.
The process discussed above proves that the process of data migration from MySQL to Postgres is a complex task. Processing it manually can also contribute to data loss or data corruption as a result of human factor. It is also ideal following the best practice to use the right software tools to automate the database conversion process. Intelligent Converters MySQL to Postgres database migration tool is one of such resources that possess all the right features required to manage both large and advanced migration projects.
