Migrating Data From Oracle to PostgreSQL

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

1. Oracle can be very expensive, with some essential features requiring additional investments.

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 can find it preferable to Oracle’s one.

Many other aspects can contribute to your decision, too. However, if you have 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) can be migrated relatively easily, more extensive databases will require a different approach if you want to ensure they won’t take an inconveniently considerable amount of time and resources during migration. Specifically, using date-based partitions in such cases is preferred – this will significantly help with data manipulation.

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 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:

Oracle	PostgreSQL
VARCHAR2(n)	VARCHAR(n)
CHAR(n)	CHAR(n)
NUMBER(n,m)	NUMERIC(n,m)
NUMBER(4)	SMALLINT
NUMBER(9)	INT
NUMBER(18)	BIGINT
NUMBER(n)	NUMERIC(n)
DATE	TIMESTAMP(0)
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMPTZ
CLOB	TEXT
BLOB RAW(n)	BYTEA(1 GB limit) Large object

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 should be executed 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 some important differences should be kept in mind:

• As 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 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 are a lot of things you need to remember before and during the migration process. So, to be sure that the database migrated successfully, it should be thoroughly tested after migration.

Now, with all this information at your disposal, let’s see how to migrate a database from Oracle to PostgreSQL using dbForge Studio for PostgreSQL.

How to migrate data from Oracle to a PostgreSQL database?

As a prerequisite, make sure that the machine you’re working on should have PostgreSQL server (we’re using v.15) and Oracle client installed.

To get started, we need to configure PostgreSQL to access the Oracle table. To do this, perform the following steps:

• Step 1: Download and install oracle_fdw.
• Step 2: Create the foreign server.
• Step 3: Create a user mapping to the foreign server.
• Step 4: Create the foreign table.

Step 1: Download and install oracle_fdw

oracle_fdw is a PostgreSQL extension that provides a foreign data wrapper for simple and effective access to Oracle databases, with EXPLAIN and WHERE supported.

Ensure that you have PostgreSQL server version 15 installed on your machine. Then, download the oracle_fdw-2.5.0-pg15-win64.zip archive corresponding to your PostgreSQL version and extract to obtain the necessary files.

After that, copy the following files and paste them into the corresponding folders as follows:

• oracle_fdw.dll from the lib folder into the lib folder where the PostgreSQL server is installed. For example, the default folder on a Windows machine is C:\Program Files\PostgreSQL\15\lib\
• oracle_fdw.control along with other SQL scripts from the share\extension\ folder to \PostgreSQL\15\share\extension\

Then, we need to create a superuser connection. To do this, launch dbForge Studio for PostgreSQL, and on the Database menu, select New Connection. In the Database Connection Properties dialog that opens, enter the PostgreSQL host, port, username, password, and database. To create a connection to the server, click Connect:

The next step is to create a wrapper. To do this, click New SQL and execute the CREATE EXTENSION statement:

CREATE EXTENSION oracle_fdw;

Step 2: Create the foreign server

To establish a connection to the Oracle database and configure access in PostgreSQL, we need to create a foreign server and provide the necessary connection details. The syntax of the CREATE SERVER statement is as follows:

CREATE SERVER server_name              
      TYPE 'server_type'                      
      VERSION 'server_version'                         
      FOREIGN DATA WRAPPER oracle_fdw
      OPTIONS (dbserver '//host:port/service_name');

To configure the connection options, replace the values with the connection details such as host, port, and service name, of the Oracle server on which the table with the data to migrate is located.

Note that the server type and version are optional, so you can skip them. In our example, the host is dbforcl, the port is 1521, and the service name is orcllast.

Also, you need to grant privileges to the PostgreSQL user – postgres – so it can access the foreign server.

Step 3: Create a user mapping to the foreign server

We also need to define a user mapping for the PostgreSQL user to the foreign server. This mapping involves specifying the Oracle remote user along with its corresponding password. To do this, in the SQL editor, execute the CREATE USER MAPPING statement:

CREATE USER MAPPING FOR postgresql_username                       
           SERVER foreign_server_name                  
           OPTIONS (
              "user" 'oracle_username',                     
               password '********');                                     

1. postgresql_username is the name of the user connected to the PostgreSQL server. This user will be responsible for migrating the data from Oracle to PostgreSQL.
2. foreign_server_nameis the name of the Oracle foreign server. In the migration process, it is the server from which the data will be extracted and migrated to PostgreSQL.
3. oracle_username is the name of the user who created the Oracle table on the Oracle side. It is necessary to have the appropriate privileges to access and extract data from the Oracle table.
4. password is the password for the specified oracle_username. It is required to authenticate and establish a connection to the Oracle database.

Step 4: Create the foreign table

Now, we need to create the foreign table corresponding to the Oracle table on the foreign server. First, create the Oracle table that has the following structure and associated 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
);

If we execute this statement in dbForge Studio for Oracle, we get the following result:

Now, let’s go to dbForge Studio for PostgreSQL and create a foreign table. Note that the table structure and the data types of the columns of the Oracle and foreign tables must match.

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');

‘ORACLE_TABLE’ is the name of the table created on the Oracle side.

To retrieve data from the foreign table, execute the following SELECT statement:

SELECT * FROM foreign_table;

In addition, create a PostgreSQL table parallelling the foreign table to migrate and synchronize data:

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)  
 );

When everything has been set up, we can proceed to migrate data from Oracle to a PostgreSQL table using Data Compare for PostgreSQL.

Step 5: Compare and synchronize data with dbForge Data Compare for PostgreSQL

Launch dbForge Data Compare for PostgreSQL and click New Data Comparison. In the New Data Comparison wizard that opens, specify the PostgreSQL source and target server and databases.

Then, go to the Mapping page in the wizard and click Custom Query. The Custom Queries Mapping dialog opens. Under Source objects, select Query and enter the SELECT statement of the foreign table; under Target objects, click Table or View and select the PG_TABLE table. Then, click Map and then Close.

On the Mapping page, specify a comparison key. To do this, select Custom from the Comparison Key column dropdown list. Then, select the Key option for the ID column and click OK.

To run the data comparison, click Compare.

In the .dcomp document that opens, click Synchronize to synchronize the databases.

In the Data Synchronization Wizard, select the output option to manage the synchronization script and click Synchronize.

To simplify the process, you can automate data synchronization using the command-line interface available in dbForge Data Compare.

Automate data comparison and synchronization through the command-line interface

When you configure comparison and synchronization options in the Data Synchronization Wizard, you can generate a file with command-line arguments. To do this, click Save Command Line in the upper-left corner of the Data Synchronization Wizard.

The Command line execution file settings window opens, where you can verify and configure the settings to manage the *.bat file text. It should be noted that the file does not include password information for security purposes. So, you can edit the file and enter the password manually. Click Save to save the file.

Conclusion

As you can see, migrating data between databases can be tricky and involve 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!