Real Time Replication from Oracle (Spatial ) to PostgreSQL (PostGIS )

In this article we will discuss how to implement real time replication of spatial data between heterogenous database using Golden Gate.

Before we start, what make this article unique ?

Since spatial data are stored in different format in both database and replication of spatial Datatype not supported in GoldenGate, CDC ( Change Data Capture ) is not possible through GoldenGate.

Then, how replication is possible ?

With that enthusiasm, let’s proceed ..

For our experimentation, we used following binary versions.

Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications and geographic information system (GIS) applications. Once spatial data is stored in an Oracle database, it can be easily manipulated, retrieved, and related to all other data stored in the database.

Oracle spatial supports the object-relational model for representing geometries. This model stores an entire geometry in the Oracle native spatial data type SDO_GEOMETRY

Oracle Spatial uses a two-tier query model to resolve spatial queries and spatial joins.

The integration of spatial indexing capabilities into the Oracle Database engine is a key feature of the Spatial

For additional information, refer here

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL. It also adds functions, operators, and index enhancements that apply to these spatial types.

With PostGIS, spatial data are stored in a Geometry column. This column stores data in a spatial coordinate system that’s defined by an SRID (Spatial Reference Identifier).

For additional information, refer here

Recently, we got challenging use case from customer to show Real Time Replication from PostGIS to Oracle Spatial (vice Versa) using Oracle GoldenGate.

Technical Challenge: Since spatial data are stored in different format in both database and replication of spatial Datatype not supported in GoldenGate, CDC ( Change Data Capture ) is not possible through GoldenGate.

Non Supported PostgreSQL Data Types

For additional information, refer here

Proposed Solution:

Even though Oracle Golden Gate NOT supports PostGIS spatial data , since use case requirement is to use Oracle GoldenGate for CDC, we need to think out of box for workaround.

As workaround, we converted Spatial Data to WKT ( Well-Known Text) , that captured via extract process , transferred to Target database as trial file, again WKT translated to Spatial Data in Target Database

Data Conversion Pipe Line

Spatial Translation Data Pipe Line

How it works:

Below functions helped to translate spatial Data to WKT and re-translate from WKT to Spatial Geometry data

@ PostGIS

To convert Geometry to WKT

• ST_AsText(geometry g1, integer maxdecimaldigits = 15);

• ST_AsEWKT(geometry g1, integer maxdecimaldigits=15); # includes SRID

To convert WKT to Geometry

• ST_GeomFromText(text WKT, integer srid);

For additional information, refer

• ST_AsEWKT.html

• ST_AsText.html

• ST_GeomFromText.html

@ Oracle

To convert SDO_GEOMETRY to WKT [ Use SDO_GEOMETRY methods ]

• SDO_GEOMETRY.get_wkt()

To Convert WKT to SDO_GEOMETRY [ Use SDO_GEOMETRY constructors ]

• SDO_GEOMETRY(wkt CLOB, srid NUMBER DEFAULT NULL);

• SDO_GEOMETRY(wkt VARCHAR2, srid NUMBER DEFAULT NULL);

• SDO_GEOMETRY(wkb BLOB, srid NUMBER DEFAULT NULL);

For additional information, refer here

Implementation:

For this use case, we will replicate real time CDC from Oracle to PostGIS

@ Source — Oracle Spatial

Add additional two columns in existing table :

To store WKT data and SRID add two additional columns in existing table or in new table. If we are storing above in new table, then we need to have referential integrity key of Parent table PK

In this use case, we have added additional column in same source (oracle) table.

• alter table GEOM_INFO add (sdo_srid number(10,0));

• alter table GEOM_INFO add (GEOM_4326_WKT CLOB);
  

Existing spatial data looks like this

select id, geom from geom_info;

output of sql query

Convert existing SDO_GEOMETERY to WKT

create a loop in cursor, and update the table with WKT data

execute immediate ‘update GEOM_INFO g set g.geom_4326_wkt = g.geom.get_wkt() where g.id = ‘ || v_unqid.id ;

execute immediate ‘update GEOM_INFO g set g.sdo_srid = g.geom.sdo_srid where g.id = ‘ || v_unqid.id ;

For New ( records ) Data:

Create “ Insert on” trigger to translate spatial to WKT and store SRID in separate column.

After ( translating SDO_GEOMETRY to WKT ) update, data looks like:

select id,geom,geom_4326_wkt,sdo_SRID from geom_info;

@ Target — PostgreSQL ( PostGIS)

1/ Configure odbc.ini after installing appropriate drivers

2/ Create table-A (Parent) with appropriate Datatype and child Table-b to store spatial data with PK col to map the parent table and Geometry Column.

CREATE TABLE CRSK_SCHEMA.GEOM_INFO ( ID INTEGER PRIMARY KEY, SDO_SRID INTEGER,GEOM_4326_WKT TEXT, …);

CREATE TABLE CRSK_SCHEMA.GEOM_INFO_GEO (ID INTEGER PRIMARY KEY, GEOM_4326_POLY geometry);

These two tables are mapped with referential integrity. Key column ID is referential column for both tables.

3.1/ Create Trigger Function

CREATE OR REPLACE FUNCTION crsk_schema.fn_update_khasra_geom() ….. 
  insert into crsk_schema.geom_info_geo values ….   ST_GeomFromText(geom_4326_wkt,sdo_srid) ..

3.2/ Create After insert trigger on Parent Table

CREATE TRIGGER tr_update_geomafter INSERTON crsk_schema.GEOM_INFOFOR EACH ROWEXECUTE PROCEDURE crsk_schema.fn_update_geom();

4/ Create OGG Parameter files for

• Extract initial load

• Extract CDC

• Replicat Initial Run

• Replicat CDC

@ Source (Oracle) — Extract Initial Load Param

[oracle@dm-computevm-1 dirprm]$ cat esprun4.prm

extract esprun4

USERID c##gg_user@ORCL, PASSWORD gg_user

RMTHOST 140.xxx.xxx.21, MGRPORT 7809

RMTFILE ./dirdat/ir , megabytes 50

SOURCECATALOG ORCLPDBTABLE CRSK.GEOM_INFO

COLSEXCEPT(GEOM) KEYCOLS(id);          << Exclude Geometry Column

@ Source (Oracle) — Extract CDC Param

[oracle@dm-computevm-1 dirprm]$ cat epos4.prm

EXTRACT epos4USERID c##gg_user@ORCL, 

PASSWORD gg_userRMTHOST 140.xxx.xxx.21, 

MGRPORT 7809

RMTTRAIL ./dirdat/eb

SOURCECATALOG ORCLPDB

TABLE CRSK.GEOM_INFO COLSEXCEPT(GEOM) KEYCOLS(id);

@ Target ( Postgis) Replicat Special Run Param

[postgres@cvm-pgl dirprm]$ cat rsprun4.prm

replicat rsprun4SETENV ( PGCLIENTENCODING = “UTF8” )

SETENV (ODBCINI=”/etc/odbc.ini”)

SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8")

TARGETDB PG_tgt, USERID gguser_pg, PASSWORD gg_user_pg

DISCARDFILE ./dirrpt/diskg.dsc, purge

END RUNTIME

@ Target (Postgis) Replicat CDC Param

[postgres@cvm-pgl dirprm]$ cat rpos4.prm

REPLICAT rpos4SETENV ( PGCLIENTENCODING = “UTF8” )

SETENV (ODBCINI=”/etc/odbc.ini”)

SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8")

TARGETDB PG_tgt, USERID gguser_pg, PASSWORD gg_user_pg

DISCARDFILE ./dirrpt/diskg.dsc, purge

HANDLECOLLISIONS

MAP ORCLPDB.CRSK.GEOM_INFO, TARGET crsk_schema.geom_info;

@ Source (Oracle) — Start CDC Extract

dblogin userid c##gg_user@orcl

register extract epos4 database container(ORCLPDB)

add extract epos4, tranlog, begin now

add exttrail ./dirdat/eb, extract epos4, megabytes 5

start epos4

start initial run

add extract esprun4, sourceistablestart esprun4

@ Target ( Postgis) — start initial replicate

add replicat rsprun4,specialrun , extfile ./dirdat/ir000000 , nodbcheckpoint

start rsprun4

start CDC replicate

add replicat rpos4,exttrail ./dirdat/eb , checkpoint

table crsk_schema.chkpoint

start rpos4

After initial load , do test with inserting few records at source (oracle) for real time CDC replication

QGIS — Verify the Spatial Visualization

Verify ( shape and area) of the Polygon spatial data from source ( Oracle ) and Target ( PostGIS) after replication via QGIS tool.

Spatial Data ( Polygons ) from Oracle

Spatial Data (Polygons ) from PostreSQL Database

Conclusion:

Above shapes & area of polygon are exactly same in Source and Target.

This implies spatial data perfectly replicated through Oracle Golden Gate from Oracle Database to Open source PostGIS Database by translating Geometry data to WKT .

Oracle Spatial Database and Oracle GoldenGate heterogeneous support makes viable to integrate with open source database for easy integration and replication.

Thanks for my Spatial mentor Albert Godfrind , who supported my ideology to experiment.

For complete demo, view on my YouTube channel.

Happy sharing and Happy learning.