Why Migrate to PostgreSQL?
Migrate from MySQL to PostgreSQL |
|
|
MySQL and PostgreSQL are one of the foremost open source RDBMS that have various tools for administration and development. Furthermore, both of the systems support all major operating systems and have wide community of experts for technical 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. It does not require to migrate from MySQL to PostgreSQL except there are plans to upscale in the future. This migration is only reasonable for the complicated large projects with database requirements that are not covered by MySQL.
Migration Strategies
- In order to migrate from 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.
- 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.
- The final step to migrate from MySQL to PostgreSQL is to extract views, stored procedures and triggers from the source 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 procedure to migrate 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 dedicated software applications such as MySQL to Postgres converter developed by Intelligent Converters
Related Courses and Certification
Also Online IT Certification Courses & Online Technical Certificate Programs