How to Migrate Databases to PostgreSQL

The long-term trend of switching to open-source software has forced commercial companies, governmental organizations and educational institutions to shun expensive proprietary database management systems to more cost-effective equivalents. At the same time procedure of database migration is a complicated task requiring lot of time and resources. In view of this fact, some organizations invest in training their staff to the benefits of migrating databases to advanced open-source DBMS like MySQL.

This whitepaper explores some insights to migrate databases from popular system like Oracle, SQL Server and MySQL to PostgreSQL as smooth as possible.

Being derived from Ingres in 1996, PostgreSQL becomes one of the most popular open-source DBMS with capabilities exceeding many commercial database management systems. The main benefits of PostgreSQL towards some another DBMS are:

• full compliance with ANSI SQL standard
• support for several models of indexing
• asynchronous commit
• both synchronous and asynchronous replication

Those advanced capabilities of PostgreSQL attracted world largest IT companies like Apple, Reddit, Spotify, Runkeeper and many others to use this DBMS for their projects.

The most straightforward way of migrating a database to PostgreSQL consists of the steps below:

1. Definitions of tables, indexes and constraints are exported from the source database in form of data definition lingual (DDL) statements
2. The exported statements are translated into PostgreSQL format and imported to the destination database
3. The source data is exported from database into comma separate values (CSV) files as an intermediate storage
4. Then the data from CSV files is transformed according to PostgreSQL format with respect to text and binary representation of particular types and imported into the destination database
5. Views, triggers, stored procedures and functions are extracted from the original database in form of PL/SQL source code
6. The extracted PL/SQL statements are converted into PostgreSQL format and imported into the target database

The approach specified above is known as extract-transfer-load method and it obviously requires essential time and efforts if doing manually. Many database professionals suggest using special software to automate migration to PostgreSQL that is especially reasonable for large and complicated databases.

One of such solutions is PostgreSQL Migration Toolkit developed by Intelligent Converters, a collection of database migration tools from all popular DBMS such as Oracle, SQL Server, MySQL, IBM DB2, MS Access, SQLite, their forks and DBaaS variations into PostgreSQL. It provides intuitive easy-to-use GUI to automate database migration handling carefully translations of data types, attributes of indexes and constraints, syntax of views. Every tool of the kit supports command line that allows to script and schedule the migration process. All versions of PostgreSQL are supported including SAAS variations such as Azure for PostgreSQL, Heroku and Amazon RDS. The product is supplied with unlimited tech support and 1-year subscription for updates.