Convert Oracle Database to PostgreSQL

Oracle is a very sophisticated and powerful object-relational DBMS which is capable of handling enormous enterprise scale databases. However, there is a downside – it cost a lot to have ownership due to tough licensing terms. This has forced many companies to migrate their content databases from to other DBMS.

Why you should pick PostgreSQL over Oracle?

A database administrator should always keep in mind that a new system should provide similar features before moving to other DBMS. When it comes to Oracle database, it is clear that no other relational DBMS have powerful features like this:

1. An extensive backup
2. A multi-level compression
3. A totally flexible storage customisation

PostgreSQL  is much more similar to Oracle as it has powerful standards-compliant RDBMS that combines object-oriented and relational database functionality. Some of the advanced features are: –

1. an asynchronous replication
2. a multi-version concurrency control system
3. a nested transactions
4. a point-in-time recovery
5. extremely sophisticated locking mechanism

These power features make PostgreSQL an excellent pick for most complicated projects which demand high reliability and data integrity. It is the best alternative to Oracle.

Migration from Oracle to PostgreSQL

Converting Oracle database to that of PostgreSQL involves these steps:

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 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 in PostgreSQL
5. Export the Oracle views, triggers, stored procedures and the functions into SQL statements and plain text source code
6. Transform all the statements and code as per PostgreSQL syntax and then load to the target server

Table Definitions

Here’s a more detailed look into the basic steps. Anywhere below the SQL*Plus there is used as default Oracle client application. Use this command line in order to connect to the database via SQL*Plus:-

sqlplus username/password@database

Firstly, it is essential to get list of every tables: 

SQL> select table_name from user_tables;

And here’s how to extract the definition of particular Oracle table: –

SQL> set long 1000

SQL> set pagesize 0

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

The resulting script should be corrected before loading to the PostgreSQL as follows:

• Remove Oracle specific statements which are at the end of table DDL (starting straight from “USING INDEX PCTFREE…”)
• Convert every data types into PostgreSQL equivalents. You can refer this according to https://www.convert-in.com/oracle-to-postgres-types-mapping.htm

Data export

Now, export the Oracle data into a CSV format. This can be executed by following the sequence of commands:-

SQL> set heading off

SQL> spool filename.csv

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

SQL> set colsep ‘,’

SQL> select * from my_table;

SQL> spool off;

You will be able to import the resulting CSV file into the PostgreSQL table via the “COPY” command:

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

If you encounter “Permission denied” error after you ran this statement, you can try out this common “\COPY”.

The Indexes

Get the list of every index which belongs to the table “mytable”:

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

Please note that Oracle stores table names are in upper case by default, unless a lower case was required directly by enclosing a table name in quotes in “CREATE TABLE” statement.

And, here’s how the definition of particular indexes can be extracted:

SQL> set long 1000

SQL> set pagesize 0

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

Conversion Tools for Oracle to PostgreSQL Migration

As seen from the various steps which are mentioned above, migrating Oracle database to your new PostgreSQL is a very sophisticated process. Manual migration also takes a lot of efforts and it can cause data loss or corruption due to human factor. You may need to use special migration tools that can smoothly transfer your database from Oracle to PostgreSQL with a click of a few mouse buttons. One such tools can be found here, it can convert the following database objects:

1. Table definitions
2. Data
3. Indexes and constraints
4. Foreign keys
5. Views