Migrating data from Oracle to PostgreSQL database

April 1st, 2020

There are several reasons you may want to migrate your databases from Oracle to PostgreSQL:

1. Oracle can be very expensive, with some important features requiring additional investments on top of that.

2. PostgreSQL provides convenient and robust open-source licensing, including various free add-ons that can help increase the performance and extend the functionality of your databases.

3. PostgreSQL’s language may be easier to work with (for example, the way it handles data types) or you just can find it more preferable to Oracle’s one.

Many other aspects can contribute to your decision, too. However, if you already decided that the move to PostgreSQL is necessary, there are some things you need to keep in mind.

The most important thing to understand is that no tool can help you completely automate the migration process. Due to many differences in how the two environments work, some parts of it have to be performed manually.

Secondly, while smaller databases (100GB or less in size) can be migrated relatively easily, larger databases will require a different approach if you want to make sure they won’t take an inconveniently big amount of time and resources during migration. Specifically, it’s preferred to use date-based partitions in such cases – this will help with data manipulation a lot.

With this information out of the way, here are the steps describing what you need to check before migrating your data:

1. Preliminary checking

Find out what exactly needs to be migrated – locate and exclude all data that is unnecessary to replicate, such as old backups and obsolete temporary tables.

2. Assessing migration

Analyze the objects in your app and databases, find differences between databases, and export the data in a migration report to understand what is the estimated time and resource usage for the migration process. Tools like Ora2pg will help immensely with this step.

3. Mapping data types

Data types in PostgreSQL have some differences when compared to Oracle data types. So some conversion will be needed in most cases. Here’s a comparison table of some data types used in these two environments:

data types

4. Transactions

Transactions in Oracle and PostgreSQL are implemented in slightly different ways. Any executed statement will trigger a transaction in Oracle, and a COMMIT statement will end the transaction. In PostgreSQL, a BEGIN statement is needed before the transaction is activated.

This may lead to additional work when porting the code between the environments.

5. Other technical differences

Some of the most important differences you need to account for during the migration process are:

  • Empty Strings and NULL – In PostgreSQL, the IS NULL operator yields a FALSE when used on an empty string, while in Oracle it produces a TRUE in such cases.
  • Dual Table – Oracle databases require a FROM clause for each SELECT statement, so a DUAL table is used for such statements. In PostgreSQL, on the other hand, FROM clauses (and, by extension, DUAL tables) are not required.
  • DELETE Statement – DELETE statements in PostgreSQL only work with a FROM clause, unlike in Oracle.
  • SUBSTR – This function yields different results in Oracle and PostgreSQL which can potentially lead to issues.
  • SYSDATE – PostgreSQL doesn’t have an equivalent to Oracle’s SSYSDATE function which provides date and time on execution. The following methods should be used instead according to your needs:
Required Time Information Corresponding Function
SQL start time Statement_timestamp()
Transaction start time Transaction_timestamp()
Time of function implementation Clock_timestamp()

6. Language conversion

PL/pgSQL and PL/SQL languages are quite similar, but there are some important differences that should be kept in mind:

  • As already mentioned, data types are implemented differently in these two languages, so some work is required to convert them properly.
  • PostgreSQL has no packages, so schemas are used to group functions as required. This also means that package-level variables cannot be used.
  • Cursor variables are notated differently in these two languages
  • Because the function body needs to be written as a string literal in PostgreSQL, dollar sign quoting should be used or, alternatively, single quotes should be escaped in the body of a function.

As you can see, there’s a lot of things you need to remember prior to and during the migration process. So, to be sure that the database migrated successfully, it should be thoroughly tested after migration.

Now, with all of this information at your disposal, let’s see how a database can be migrated from Oracle to PostgreSQL with the help of dbForge Studio for PostgreSQL.

How to migrate data from Oracle to PostgreSQL database?

As a prerequisite, the machine you’re working on should have PostgreSQL Server (we’re using v.12) and Oracle client installed.

First of all, we’ll need oracle_fdw. It’s a PostgreSQL extension that provides a foreign data wrapper for simple and effective access to Oracle databases, with EXPLAIN and WHERE supported.

oracle_fdw can be download here: https://github.com/laurenz/oracle_fdw/releases

There are different versions of the wrapper for different PostgreSQL server versions and OS bit depths.

Let’s look at the process for oracle_fdw v.2.2.0 win64:

  • Download the oracle_fdw-2.2.0-pg12-win64.zip archive and extract it.
  • Move oracle_fdw.dll from the lib folder into the C:\Program Files\PostgreSQL\12\lib\ folder (or the corresponding folder based on where PostgreSQL server is installed on your machine)
  • Move oracle_fdw.control and two SQL scripts from the share\extension\ folder to C:\Program Files\PostgreSQL\12\share\extension\
  • Launch dbForge Studio for PostgreSQL and create a superuser connection:
  • To create a wrapper, execute the following in a new SQL document:
CREATE EXTENSION oracle_fdw;
  • Next, we need to create a foreign server:
  CREATE SERVER foreign_oracle              -–server name
      TYPE 'Oracle12'                       --server type (optional)
      VERSION '12'                          --server version (optional)
      FOREIGN DATA WRAPPER oracle_fdw
      OPTIONS (dbserver '//dboracle:1521/orcl12');

Here, the host is dboracle, the port is 1521, and the service name is orcl12.

  • We also need to map the PostgreSQL user to the Oracle user:
  CREATE USER MAPPING FOR sa                       --sa user
           SERVER foreign_oracle                   --foreign server name
           OPTIONS (
              "user" 'scott',                      --Oracle user
               password '********');               --password       

  • Let’s assume that Oracle already has a table of a specific structure with data:
  CREATE TABLE ORACLE_TABLE (
    ID                      NUMBER(11, 0) DEFAULT 1,
    C_BIN_FLOAT             BINARY_FLOAT,
    C_BLOB                  BLOB,
    C_CHAR                  CHAR(20 CHAR),
    C_CLOB                  CLOB,
    C_DATE                  DATE,
    C_INTEGER               NUMBER(*, 0),
    C_INT_D_TO_SEC          INTERVAL DAY(2) TO SECOND(6),
    C_NVARCHAR2             NVARCHAR2(50),
    CONSTRAINT PK_ORACLE_TABLE_ID PRIMARY KEY (ID) USING INDEX

You can execute this query in dbForge Studio for Oracle, and this is what the result will look like:

  • To retrieve data from an Oracle table, a Foreign Table is created with corresponding data types (you can refer to the data types table at the end of the article). Some parameters may have to be additionally set up for PostgreSQL data types.
  CREATE FOREIGN TABLE foreign_table (
    id NUMERIC,
    c_bin_float DOUBLE PRECISION,
    c_blob BYTEA,
    c_char CHAR(20),
    c_clob TEXT,
    c_date DATE,
    c_integer NUMERIC,
    c_int_d_to_sec INTERVAL,
    c_nvarchar2 VARCHAR
  ) 
  SERVER foreign_oracle
  OPTIONS ("table" 'ORACLE_TABLE');
  • Data can be retrieved with the help of a standard SELECT from the foreign table:
SELECT * FROM foreign_table;

To migrate and synchronize data, we can additionally create a PostgreSQL table parallelling the foreign table:

CREATE TABLE PG_TABLE (
ID numeric,
C_BIN_FLOAT double precision,
C_BLOB bytea,
C_CHAR char(20),
C_CLOB text,
C_DATE date,
C_INTEGER numeric,
C_INT_D_TO_SEC interval,
C_NVARCHAR2 varchar,
CONSTRAINT PK_PG_TABLE_ID PRIMARY KEY (ID)
);

Then, you can compare and synchronize data with the help of dbForge Data Compare for PostgreSQL.

To do this:

  • open Data Compare and click New Data Comparison
  • specify the source and target connections
  • click Custom Query on the Mapping tab
  • choose Query for the source and paste the necessary query
SELECT * FROM sa.public.foreign_table;
  • click OK, then click Compare
  • click Synchronize in the dcomp document and synchronize the databases

These steps can also be automated through dbForge Data Compare’s command-line interface.

Conclusion

As you can see, migrating data between databases can be a tricky process involving many variables. But if you have a plan, a list of things to specifically look for, and use tools that are tailored to help you with migration, this process becomes much easier and smoother. In this article, we described one of the ways in which you can migrate data from an Oracle to a PostgreSQL database. However, there are other ways of Oracle migration to PostgreSQL as well. 

Choose the one that’s more to your liking and see how easy the migration process can be when approached with the right tools and knowledge!

Leave a Comment