Oracle and PostgreSQL are well-known advanced database management systems supplied with a wide range of tools for administration and development purposes. Although these two systems offer ultimate capabilities, when comparing DBMS, the most significant difference appears in the license terms. While Oracle has stricter licensing terms and high total cost of ownership, PostgreSQL is open source and goes under a liberal license similar to BSD or MIT.
This is why many companies intend migrating their Oracle databases to PostgreSQL on-premises or cloud platforms. Considering the process of Oracle to Postgres database migration, the following sequence and steps are considered:
- Export Oracle table definition table to be in form of statements written in data definition language (DDL)
- DDL statements should be converted into PostgreSQL format including the substitution of Oracle embedded functions which are not supported in PostgreSQL, correct type mapping etc.
- Using a target PostgreSQL database, import the resulting table definitions.
- While working on comma-separated values (CSV) format, export the Oracle data from the source database into an intermediate storage.
- In order to comply with PostgreSQL format, dates and escaping special symbols in text fields when it is required, providing necessary transformation of binary data, convert the data.
- Import the CSV files right into the target database.
- Export Oracle stored procedures, views, and triggers in the form of SQL statement and source code.
- Transform source codes according to its destination.
- Import the database into PostgreSQL database.
The above steps prove that it is not easy to migrate a database from Oracle to Postgres manually. It may cause human data loss or corruption as the tedious procedure takes many efforts and human factor. Special software may be used in order to automate database migration and avoiding any related risk.
Fortunately, there are few software companies which provide solutions for automating database migration. Oracle to Postgres conversion tool is one of such solution, developed by Intelligent Converters, a database migration field software company since 2001.
Product Features:
- Tables, data, constraints, indexes and foreign keys are converted with all necessary attributes.
- Direct connection and bulk insert technique for high performance of the database migration.
- All modern on-premises and cloud variation of Oracle and PostgreSQL are supported including Heroku, Amazon RDS, Microsoft Azure.
- Oracle to Postgres converter provides command and line support for the purpose of automation.
- Options to merge or synchronize (full scan, non-incremental) Oracle data with existing PostgreSQL database.
- All settings of the migration process are stored in the profile to simplify next run.
Notwithstanding, the core feature provided by Oracle to Postgres converter which will definitely makes database migration more flexible and powerful is filtering data via SELECT-queries. Before converting it into PostgreSQL format, this feature allows you to select column and records for transformation or even for conversion.
- Filter records:
select title, address from vendors where status=1
- Skip NULL Value(s):
select first_name, last_name from people where birthday IS NOT NULL
- Select and rename particular columns:
select f109 as bulk_price, f112 as retail_price from sales
- Merge two tables:
select * from Students S INNER JOIN Advisors A ON S.Advisor_ID=A.Advisor_ID
Visit the official product page to learn more about Oracle to Postgres database migration tool by Intelligent Converters.