Why Migrate to PostgreSQL?
MySQL and PostgreSQL are popular database management systems enhanced by numerous administration and development tools. Both DBMS are open-source, ported on all OS and having wide communities of fans. Nevertheless, PostgreSQL has some advantages over MySQL that may be important for certain projects:
- 100%compatibility with ANSI SQL standard
- multiple indexing models are supported
- Common Table Expressions (CTE) are supported
- synchronous and asynchronous replications are supported
- full outer joins are supported
- unlike MySQL, PostgreSQL works with arrays
Perhaps, the only disadvantage of PostgreSQL is that it is more complex than MySQL and consequently required more time to learn. In view of this fact it is not recommended to choose PostgreSQL as database for simple projects.
Migration Strategies
Manual database migration from MySQL to PostgreSQL consists of the following steps.
- All table definitions must be extracted from the source database in form of CREATE-like SQL statements. This is how it can be done like via standard MySQL tools:
phpMyAdmin – select table in the left pane, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘SQL’ and make sure that radio-button ‘Structure’ is checked
in MySQL console environment the following command line statement must be run
mysqldump–d –h (host) –u (user) –p(password) (databasename) > (dumpifle)
Of course, all text enclosed in parenthesis must be replaced by actual values
- These DDL statements must be translated into PostgreSQL format and imported into the destination database. On this stage, all data types must be converted from MySQL to PostgreSQL properly.
- Data from all MySQL tables must be exported into an intermediate storage such as comma separated values (CSV) file. This is how it can be done like via standard MySQL tools:
phpMyAdmin – highlight table in the left pane, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘CSV’ and make sure that radio-button ‘Data’ is selected
in MySQL console client – use SQL statement
SELECT * INTO OUTFILE (‘table.csv’)
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’ FROM (table)
Of course, all text enclosed in parenthesis must be replaced by actual values
- Then data from CSV files must be converted into PostgreSQL format (if it is necessary) and loaded into the destination database. This is how to import CSV files to PostgreSQL server using the standard tools:
pgMyAdmin – browse tables in the left pane of the interface, right-click on the table where CSV data has to be imported and click on ‘Import’ menu item
psql – run SQL statement
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
- All MySQL views, stored procedures and triggers must be exported from the database as SQL statements and source code.The task can be implemented using these SQL-statements:
views
SELECT table_name, view_definition FROM information_schema.views
WHERE table_schema=’database name’;
stored procedures
SHOW PROCEDURE STATUS WHERE Db = ‘database name’;
triggers
USE ‘database name’; SHOW TRIGGERS;
- Finally, the output statements and source codes have to be converted into PostgreSQL format and loaded into the target database. This procedure requires deep knowledges in MySQL and PostgreSQL programming and stays outside of this article.
These steps indicate that migration of MySQL database to Postgres is complicated process when doing it manually.Inaccurate implementation can lead to data loss or corruption. That’s why many database specialists use dedicated software tools to automate the database migration, such as MySQL to Postgres developed by Intelligent Converters.It combines easy to use interface with sufficient capabilities to handle large and complex migration projects:
- all versions of MySQL and PostgreSQL are supported (including Azure, Heroku and other cloud solutions)
- option to automate conversion via command line support
- MySQL data can be merged into existing PostgreSQL tables
- option to export MySQL data into PostgreSQL script (when there is no direct connection to the target server)
- support for Unicode
- option to convert result of SELECT-query as a regular table
- option to modify the target table structure before migration