Geospatial Data Transfer from Oracle to PostgreSQL

Hello everyone,

In this post, I will explain 3 different methods we can use to transfer data that contains geographical features from Oracle to PostgreSQL. Oracle will be the source, and PostgreSQL will be the target database.

1-Pentaho Data Integration (PDI)

Our first step will use Pentaho Data Integration (PDI), also known as Spoon. Spoon is an open-source tool that simplifies data transfer, transformation, and loading (ETL) processes.

In our example, we will move a table named “MAHALLE” from Oracle to the PostgreSQL database. When we first open the program, we will use the panel on the left side of the welcome screen. From here, we drag and drop “table input” for our source database (Oracle) and “table output” for our target database (PostgreSQL) into the workspace.

In our data transfer process, first, we need to connect to our source database, Oracle.

• Go to the “Connection” section at the top of the “Table Input” properties window.
• Click the “New” button in the “Connection” section to open the window for creating a new database connection.
• Select “Oracle” as the “Connection Type” and then enter the necessary connection parameters.

After we connect , we press the “Get SQL select statement” button. This opens the Database Explorer window. Here, we pick the table we want to move.

In our second step, we need to set up our connection to PostgreSQL, our target database. We do the same steps we did for the source database connection on Table output. We enter the needed parameters for the PostgreSQL connection.

After we test our connection and make it work, we set up the table where data will go. We say which database design and table name we want. Then, we open a simple SQL editor window with the SQL button. We need to run the SQL script there.

In the “Table Output” settings window, we put in the names for the ‘Target schema’ and ‘Target table’ where we want to send the data in PostgreSQL.

When we finish setting up, we click the ‘SQL’ button to make the table structure needed in the database. This takes us to an SQL editor window. We should execute the script found there.

After we make the target table right, we are ready to start moving data. We click the run button to do the transfer.

2-oracle_fdw

Another tool to move data from Oracle to PostgreSQL is the Oracle FDW extension. FDW lets PostgreSQL work with data from different places like Oracle or SQL Server.

Add the Plugin

Oracle FDW is an extension not provided by PostgreSQL by default.You need to set it up on your computer first.You can visit the laurenz/oracle_fdw github page for installation and configuration instructions suitable for your operating system. After you put Oracle FDW on your system, you can make it work in your PostgreSQL with this command:

 create extension oracle_fdw;

Creating a Foreign Server

To make a connection to the Oracle database:

create server oracle_server foreign data wrapper oracle_fdw options (dbserver ‘//host:port/service_name’);

In this command;

• oracle_server is the name we give our server.
• host is the network address of the Oracle database server.
• port is the usual port number for Oracle.
• service_name is the name of the Oracle database service.

User Mapping

We set up user mapping to let PostgreSQL users access the Oracle database.

— Mapping for a PostgreSQL user

 create user mapping for postgres server oracle_server options (user ‘username’, password ‘user_password’); 

In the options, ‘username’ is the Oracle database user’s name, and ‘user_password’ is their password.

Importing a Schema

We can transfer a whole schema or certain tables or views from an Oracle database to PostgreSQL. For example, we can move a table named “MAHALLE” found in the “MEDIUM” schema of Oracle to the “public” schema in PostgreSQL.

— To transfer a specific table from an Oracle schema to PostgreSQL

import foreign schema “MEDIUM” limit to (MAHALLE) from server oracle_server into public;

3-ogr2ogr

Another way to move data between different databases is by using ogr2ogr, part of the GDAL/OGR software library. ogr2ogr is a command-line tool that can change geographic data formats.

To use ogr2ogr, you need the GDAL/OGR software library installed on your system, including the Oracle and PostgreSQL plugins. Then, you can transfer data from Oracle to PostgreSQL with an ogr2ogr command like in the example below:

ogr2ogr -f “PostgreSQL” PG:”host=localhost user=postgres dbname=sample_data password= ‘user_password’ “ -lco DIM=2 -lco SRID=5256 -t_srs “EPSG:5256” -lco GEOMETRY_NAME=geom -lco PRECISION=NO -nln mahalle -nlt MULTIPOLYGON “OCI:MEDIUM/’user_password’@localhost:1521/service_name “ MAHALLE5256 -geomfield GEOM

This command transfers the “MAHALLE5256” table from the Oracle database to PostgreSQL under the name “mahalle” During the transfer, it also correctly changes the geometry data.

When transferring data, tools like Pentaho Spoon, Oracle FDW, and ogr2ogr each have their own pros and cons. You should consider factors such as the size of the data set, the complexity of the project, and the existing technical infrastructure when choosing.

I appreciate you reading. Good luck! 🌞