From:       To:      

Home > Documentation > Oracle to Postgres

How to migrate from Oracle to Postgres

[Oracle to Postgres Converter]  [Validate Migration Results]  [Triggers]  [Types Mapping]  [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 an open-source DBMS with similar capabilities and features, for example PostgreSQL.

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 Postgres Database Migration

The process of Oracle to Postgres database migration 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 of Oracle to Postgres database migration is to export the source 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;

Empty String vs NULL

Oracle does not distinguish empty strings from NULL values in string context. The concatenation of NULL and string obtain string as a result. In PostgreSQL the result of concatenation above is NULL.

Oracle uses operator IS NULL to check if string is empty, in PostgreSQL the same expression is FALSE for empty string and TRUE for NULL values.

Synonym

Oracle uses CREATE SYNONYM statements to access remote objects. PostgreSQL does not support CREATE SYNONYM, it can set up remote definition via SET search_path. For example, in Oracle:

CREATE SYNONYM loc.tbl FOR rem.tbl;

In PostgreSQL the equivalent is:

SET search_path TO 'loc.tbl';

Sequences

Although syntax of creating sequences in Oracle and PostgreSQL is quite similar, there is minor difference in getting next value. In Oracle:

Seq_name.nextval

The same in PostgreSQL is:

Nextval('Seq_name')

Oracle to Postgres Migration Tools

There are a number of free and commercial tools that can help customers automate Oracle to Postgres database migration:

Have questions? Contact us