Oracle to Postgres Migration

Oracle is a powerful and sophisticated object-relational database management system which can be used for any kind of project involving databases. The only but essential disadvantage of Oracle is its price of licensing. This is a key reason why many organizations come into Oracle to Postgres migration.

PostgreSQL is much closer to Oracle than any other DBMS as it has powerful standards-compliant RDBMS that combines object-oriented and relational database functionality. PostgreSQL supports asynchronous replication and provides a multi-version concurrency control system. Adding nested transactions, point-in-time recovery and a sophisticated locking mechanism – this combination gives a portrait of advanced DBMS that really can be considered as a replacement for Oracle.

Basically, Oracle to Postgres migration can be represented as the following:

1. Export Oracle database table definitions to "CREATE TABLE" statements
2. Ensure that the SQL statements are compatible with PostgreSQL format. After that load it to the target server
3. Export the Oracle data into an intermediate storage like CSV files
4. Convert it to the target format (if needed) and then import to PostgreSQL
5. Export the Oracle views, triggers, stored procedures and functions into SQL statements and plain text source code
6. Transform all the statements and code as per PostgreSQL syntax and then load them to the target server

Table Definitions

Now let us give a more detailed look at all these steps. A list of all available Oracletables can be obtained by running this query:

SQL> select table_name from user_tables;

The definition of the particular Oracle table can be extracted through the following query:

SQL> set long 1000

SQL> set pagesize 0

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

Do not forget to correct CREATE TABLE statements before loading to the PostgreSQL database:

• Remove Oracle-specific statements at the end of table DDL
• Convert every data type into PostgreSQL equivalents

Data

The next step is to export Oracle data into a CSV format via the following commands:

SQL> set heading off

SQL> spool filename.csv

SQL> select column1 || ',' || column2 || ... from my table;

SQL> set colsep ','

SQL> select * from my_table;

SQL> spool off;

Once you have the CSV file, it can be imported into the PostgreSQL table as follows:

COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;

Indexes

All indexes belonging to the table "mytable" are listed through the following statement:

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

Oracle stores table names in upper case by default, lower case names must be enclosed in quotes in all related statements. The definition of the particular index can be extracted via the following commands:

SQL> set long 1000

SQL> set pagesize 0

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

Tools for Oracle to PostgresMigration

The brief guide specified above demonstrates that Oracle to Postgres migration is a very complicated process. It takes a lot of effort and may cause data loss or corruption due to human factors when running manually. The workaround is to use special tools to automate migration and make the process smooth and safe. Intelligent Converters company developed a tool called Oracle to PostgreSQL converter. It supports all modern versions of both DBMS and can migrate table definitions, data, indexes and constraints, foreign keys and views.