In the world of relational databases Oracle and PostgreSQL are known as extremely popular powerful systems supplied with wide range of development and management tools. These two DBMS are quite similar in scope of capabilities and features, however Oracle has more strict licensing terms and high total cost of ownership than PostgreSQL. This is why many companies and organizations migrate their databases from Oracle to PostgreSQL.
Database migration from Oracle to PostgreSQL is a complicated procedure that includes the following basic steps:
- Export Oracle tables, indexes and constraints in form of data definition language (DDL) statements
- Convert those DDL statements into PostgreSQL format with special attention to Oracle types and embedded functions missing in the target DBMS
- Import converted statements into PostgreSQL
- Oracle data is exported into comma separated values files (one file per table) as intermediate storage
- The data in CSV files is converter to comply with PostgreSQL format. It includes conversion of binary data, dates and escaping special symbols in text fields.
- After this transformation all data files are imported into PostgreSQL database
- Oracle views, stored procedures and triggers are extracted from the database as SQL CREATE-statements and source code
- These statements and source code must be converted into PostgreSQL syntax
- Finally, result of this conversion is imported into the target database
These steps show that database migration from Oracle to PostgreSQL is a tedious procedure requiring lot of efforts when doing it manually. Also, the human factor may cause data loss or corruption. That’s why many database specialists use special software to automate Oracle to PostgreSQL migration and avoid the related risks.
There are multiple solutions to automate Oracle to PostgreSQL database migration available on the market. One of such solutions is Oracle to PostgreSQL tool developed by Intelligent Converters, a software company specializing in database migration and synchronization since 2001. The product provides wide range of features:
- tables, data, constraints, indexes, foreign keys and views are supported
- bulk insert technique allows to reach high performance of database migration
- all versions of Oracle and PostgreSQL are supported including “software as a service” (SAAS) variations of these DBMS
- command line support allows to automate Oracle to PostgreSQL migration
- options to merge and to synchronize existing PostgreSQL database with Oracle data
- conversion settings can be stored into profile to simplify next run
- option to filter data via SELECT-queries. This powerful feature allows to select columns and records for conversion, merge multiple tables into a single one or even to make some transformations of the data before converting it into PostgreSQL format.