From: Gilles Darold <gil...@darold.net> Sent: Thursday, August 20, 2020 1:59 PM To: Ko, Christina (US) <christina...@lmco.com>; pgsql-general@lists.postgresql.org Cc: Ho, Chuong <chuon...@amazon.com> Subject: Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql
Le 20/08/2020 à 17:57, Ko, Christina a écrit : From: Gilles Darold <gil...@darold.net><mailto:gil...@darold.net> Sent: Thursday, August 20, 2020 10:54 AM To: Ko, Christina (US) <christina...@lmco.com><mailto:christina...@lmco.com>; pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org> Cc: Ho, Chuong <chuon...@amazon.com><mailto:chuon...@amazon.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: CREATE TABLE Spatial_Tbl ID NUMBER(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/ That really helps. Thank you. Christina
