Why Migrate to PostgreSQL?
MySQL and PostgreSQL are one of the foremost open source RDBMS that have various tools for administration and development. Furthermore, the duo has been ported to all major operating systems and each has a large followership and community of experts for support. In spite of the similarities, PostgreSQL has some advantages that makes it a preferable choice in certain projects. Such features includes:
- Absolute compliance with ANSI SQL standard.
- Multiple indexing model feature.
- Support for synchronous and asynchronous replication.
- Common Table Expressions supported.
- Support for full outer joints.
- In contrast to MySQL, PostgreSQL works with array.
However, PostgreSQL operation procedures is more complex than MySQL and can be quite knotty for beginners to handle. Due to this reason, a simple database project that is functioning excellently under the capabilities of MySQL should be let alone. There is need to migrate such to PostgreSQL except there are plans to upscale in the future.
Migration Strategies
- In order to migrate MySQL to PostgreSQL manually, follow the step-by-step guide described below.
All the table definitions should be extracted from the source database as DDL SQL statements.
In phpMyAdmin Select the table and locate the Export tab; chose on custom option and set the file format to SQL. Make sure you checked the radio button labelled structure
In MySQL command terminal, use the statement below:
mysqldump -d -h (host) -u (user) –p(password) (databasename) > (dumpifle)
- Replace all contents in brackets with the actual values.
- The DDL statements should be translated according to PostgreSQL format. Thereafter, it should be loaded into the target database. The most important part of this process is to effectively convert column types from MySQL to Postgres. Details about this type of conversion is available here Types Mapping.
- The Data of every MySQL table should be exported into an intermediate format like CSV. The process below should be followed to achieve this.
in phpMyAdmin Select the table and click on the “Export” tab, chose “Custom” option and set the output format to CSV. Check the radio button labelled “Data”
MySQL command terminal – use the statement
SELECT * INTO OUTFILE (‘table.csv’)
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’ FROM (table)
- Replace all contents in brackets with the actual values.
- At this stage, it is required in some scenarios that the data in the CSV files be transformed to the PostgreSQL format before loading into the destination database.
- On the final note, views, stored procedures and triggers should be extracted from the MySQL database in form of SQL statements and source code. For phpMyAdmin and MySQL command terminal users, the SQL statements listed below can be applied to achieve this.
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 statements and source codes derived from the previous stage is then converted to PostgreSQL format and loaded into the destination database. It is noteworthy that this process requires intricate knowledge of database environment and the peculiar SQL commands for MySQL and PostgreSQL.
The procedures for migration of database from MySQL to PostgreSQL described above is quite complex. A single error in the manual process could lead to data loss or corruption. It is recommended to use professional software utilities to automate the process of database conversion. Large and complex database migration can be easily handled using intelligent software applications such as MySQL to Postgres conversion tool developed by Intelligent Converters
