Home > Documentation > Oracle to PostgreSQL

How to convert Oracle database to PostgreSQL

[Oracle to PostgreSQL Converter]  [Validate Migration Results]  [Types Mapping]  [Converting Views]

Oracle is a powerful object-relational DBMS suitable for handling huge enterprise scale databases. However, it has high cost of ownership and strict licensing policy. This valuable disadvantage forces some companies and organizations to migrate their databases from Oracle to another DBMS.

Why PostgreSQL?

Database admin or another person responsible for database migration should expect that new system provides similar set of features compared to the original DBMS. When talking about Oracle database, it is obvious that none of other relational database management systems is so enriched with powerful features such as:

Being powerful standards-compliant RDBMS that combines object-oriented and relational database functionality, PostgreSQL gets much closer to Oracle than other systems. Here is the brief list of its advanced features:

Those benefits make PostgreSQL excellent solution for complicated projects demanding high reliability and data integrity i.e. the best alternative to Oracle database.

Oracle to PostgreSQL Migration

The process of converting Oracle database to PostgreSQL consists of the following steps:

Table definitions

Below we consider basic steps from this list in more details. Anywhere below SQL*Plus is used as default Oracle client application. This is the command line to connect to the database via SQL*Plus:

sqlplus username/password@database

First, it is necessary to get list of all tables:

SQL> select table_name from user_tables;

And this is how definition of particular Oracle table can be extracted:

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('TABLE','<TABLE NAME>'[,'SCHEMA']) from DUAL

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

Data

Next step is to export Oracle data into CSV format. It can be done via the following 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;

The resulting CSV file can be imported into PostgreSQL table via "COPY" command:

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

If there is "Permission denied" error after running this statement, try to use "\COPY" command instead.

Indexes

Let's get list of all indexes that belong to table "mytable":

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

Keep in mind that Oracle stores table names as upper case by default unless lower case was specified directly by enclosing table name in quotes in "CREATE TABLE" statement.

And this is how 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;

Oracle to PostgreSQL Conversion Tools

The steps described above prove that migrating Oracle database to PostgreSQL is a sophisticated process. Manual migration takes a lot of efforts and it can cause data loss or corruption due to human factor. It is reasonable to cosider special migration tools that can transfer database from Oracle to PostgreSQL with just a few clicks of mouse button. Intelligent Converters developed such a tool - Oracle to PostgreSQL converter. It can automate migration of the following database objects: