Oracle to PostgreSQL Migration

Being powerful object-relational database management system, Oracle can manage very sophisticated enterprise-scale data warehouses. It is also supplied with huge collection of administration tools and developer frameworks. However, such disadvantages of the DBMS are high total cost of ownership and strict licensing policy forces many organizations to migrate Oracle databases to different systems and platforms. 

Why choose PostgreSQL?

When planning migration, the person who manages the procedure expects that new system has capabilities similar to the original DBMS. It is hard to find an alternative to Oracle database due to its unique powerful features. Among all advanced database management systems PostgreSQL with its mixes object-oriented and relational database functionality gets much closer to Oracle than others. Here are the main of PostgreSQL powerful features: 

• full compliance with ANSI SQL standard 
• multi-version concurrency control
• asynchronous replication
• nested transactions
• point-in-time recovery
• sophisticated locking mechanism

Those advanced benefits make PostgreSQL ideal choice for managing powerful sophisticated databases i.e. the most effective alternative for Oracle. 

Steps of Oracle to PostgreSQL Migration

The procedure of migrating Oracle database to PostgreSQL consists of the following steps: 

• extract Oracle table definitions as “CREATE TABLE” SQL commands, convert these statements into PostgreSQL format and import to the target database
• export Oracle data into intermediate CSV files, convert it into the target format (when required) and load to PostgreSQL database
• extract Oracle views, triggers, stored procedures as SQL statements and source code; transform it according to PostgreSQL syntax and load to the target database

Let us explore the steps listed above in details.

Table Definitions

This is how to get list of all tables in Oracle: 

SQL> select table_name from user_tables;

The definition of particular Oracle table is extracted through these statements: 

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL

Then output statements must be manually converted to complete Oracle to PostgreSQL migration of table definitions as follows: 

• Oracle specific statements at the end of each table definition must be removed
• Oracle data types must be translated into PostgreSQL equivalents

Data

This is how to export Oracle data into CSV format: 

SQL> set heading off

SQL> spool filename.csv

SQL> select column1 || ‘,’ || column2 || … from table1;

SQL> set colsep ‘,’

SQL> select * from table1;

SQL> spool off;

PostgreSQL can load the output CSV file via “COPY” command: 

COPY <table_name> FROM csv_file> DELIMITER ‘,’ CSV;

If you experience “Permission denied” error, try using “\COPY” command. 

Indexes

Oracle explores all indexes that belong to particular table through this query: 

SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;

Keep in mind that Oracle table are case sensitive. The definition of particular index can be extracted via these statements: 

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;

Views

Oracle exposes the list of all views through this query:

select VIEW_NAME, TEXT from SYS.USER_VIEWS;

Then definition (SQL query) of every view must be converted in PostgreSQL format by removing specific Oracle keywords that are not supported by the target DBMS and replacing all built-in functions by the appropriate equivalents.

Oracle to PostgreSQL Migration Tools

The steps described above illustrate that database migration from Oracle to PostgreSQL is quite sophisticated procedure. Running migration manually requires a lot of efforts and also implies high risk of losing data integrity or database logic due to human errors. This is the reason why using special tools to automate migration process makes sense. 

Intelligent Converters, a software company focusing on database conversion and synchronization, provides one of such tools automating Oracle to PostgreSQL migration. The product migrates main database entries: schemas, data, indexes and foreign keys, views. Also, it works with all recent versions of Oracle and PostgreSQL, supports major DBaaS platforms like Azure, Amazon RDS and Google Cloud. 

For those cases when the target PostgreSQL server does not accept remote connections, Oracle to PostgreSQL migration tool offers export into script file containing statements to create all required database objects and to load the data. Another useful feature of the program is filtering and pre-processing data through SELECT-queries. Resulting row sets of those queries can be migrated to PostgreSQL as a regular table. Finally, the tool provides flexible way to thoroughly customize table being migrated – option to modify name, type and other attributes of every column as well as exclude particular columns from migration.

Visit the official site of Intelligent Converters for more information about Oracle to PostgreSQL migration.