Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

Le 20/08/2020 à 17:57, Ko, Christina a écrit :
>
> *From:* Gilles Darold <gilles(at)darold(dot)net>
> *Sent:* Thursday, August 20, 2020 10:54 AM
> *To:* Ko, Christina (US) <christina(dot)ko(at)lmco(dot)com>;
> pgsql-general(at)lists(dot)postgresql(dot)org
> *Cc:* Ho, Chuong <chuongho(at)amazon(dot)com>
> *Subject:* EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql
>
> Le 20/08/2020 à 16:30, Ko, Christina a écrit :
>
> Hi All,
>
> I am running into issue using DMS to load oracle spatial data
> (oracle 11.2.0.3) to postgresql.
>
> Oracle table:
>
> CREATETABLESpatial_Tbl
>
> IDNUMBER(38,9),
>
> P_ID NUMBER(38,9),
>
> GEOMETRY MDSYS.SDO_GEOMETRY
>
> AWS DMS calls the SDO2GEOJSON custom function trying to load the
> data, but it failed and the Geometry columns in Postgresql was empty
>
> I also tried using SDO_UTIL.TO_GEOJSON utility and didn’t work either.
>
> I am looking for suggestions of how to load the geometry data from
> oracle 11.2.0.3 to oracle.
>
> Thanks in advanced for your help!
>
> Christina
>
>
>
> Hi,
>
> You can either use oracle_fdw or Ora2Pg. The first is a FDW extension
> that will allow you to use a foreign table to upload the data into
> your destination table and the second tool export data from the Oracle
> database to plain text file or to your PostgreSQL table directly.
>
> Best regards,
>
> --
> Gilles Darold
> http://www.darold.net/
> I have just installed ora2pg and will see if it works.  I believe I
> have to specify my setting in the config,  do you have any suggestion
> of what I have to set in the config file to load spatial data from
> oracle to postgresql.  Thank you.
> Christina

Well if you start with Ora2Pg your bible is
http://www.ora2pg.com/documentation.html and especially this chapter
that will make you save time
http://www.ora2pg.com/documentation.html#Generate-a-migration-template

After reading that as a shortcut once your ora2pg.conf is configured to
communicate with Oracle and if you just want to migrate this single table:

ora2pg -c config/ora2pg.conf -t COPY -b data/ -o data.sql -a
'SPATIAL_TB1'

I also recommend you to read
http://www.darold.net/confs/ora2pg_the_hard_way.pdf, it is a bit old but
plenty of useful information on Ora2PG use.

Best regards,

--
Gilles Darold
http://www.darold.net/