It is hard to say which DBMS is better – Oracle or PostgreSQL, since both systems are high performance, very popular and have a wide range of tools and APIs. However, Oracle has stricter licensing terms and high total cost of ownership while PostgreSQL comes with less expensive licenses and even better – it is open source. This is one of the primary reasons why many database specialists consider migration from Oracle to PostgreSQL.
The process of database migration from Oracle to PostgreSQL consists of these basic stages:
- Oracle table definitions, indexes and constraints must be exported in form of SQL CREATE-statements
- These SQL statements must be translated according to PostgreSQL syntax including appropriate type mapping, replacement of Oracle patterns that are not supported by PostgreSQL, etc
- Then the resulting SQL script must be imported into PostgreSQL database
- Oracle data must be exported into comma separated values (CSV) format as an intermediate storage
- The data must be processed with transformation of binary data, dates and escaping special symbols in text fields according to PostgreSQL format when it is required
- The resulting CSV files must be loaded into PostgreSQL tables
- Stored procedures, triggers and views must be exported in form of CREATE-statements and source code
- These statements and source code must be translated according to PostgreSQL format and loaded to the target database
The brief guide given above proves that migration from Oracle to PostgreSQL is not an easy procedure when doing it manually, especially for complex corporate scale data warehouses. Manual database migration may demand essential resources and also there is high risk of data loss or corruption. In order to simplify migration from Oracle to PostgreSQL and avoid the related risks many database experts advise to use special software.
There are multiple software providers offer high-quality database migration tools. One of such solutions has been developed by Intelligent Converters, a software company working in database migration field since 2001. Here is the list of product’s main features:
- All database objects such as table definitions, data, constraints, indexes, foreign keys, and views are converted
- All versions of Oracle and PostgreSQL are supported including “software as a service” (SAAS) variations of these database management systems
- High performance of the conversion due to efficient algorithms of reading and writing the data
- Command line support for purpose of automating and scheduling Oracle to PostgreSQL migration
- Options to merge and to synchronize existing PostgreSQL database with Oracle data
- All conversion settings can be stored in the profile to simplify next run of the migration for the same databases
For migration projects that require processing partial data, Oracle to PostgreSQL converter provides filtering data via SELECT-queries. This feature allows to specify columns and records for conversion using particular SELECT statements. It also can be used for transformation of the data before loading it into PostgreSQL database.
To learn more about the product please visit the official site https://www.convert-in.com