Migrating from oracle to PostgreSQL 9.2

[ESRIESRI1]

Hello All,

We have huge database with several feature class on oracle (ArcSDE 10) which need to migrate to postgreSQL9.2. I am new to postgreSQL , I could not find direct way other than exporting to shapefile and re-importing.  ESRI help provides method using pg_dump command, can any one give step by step procedure to execute the same or point to me correct tutorial to execute the same.

Please help me on this.

[VinceAngelo]

There are likely more than a dozen ways to migrate this data, but the top options
include:

• Drag-n-drop with ArcGIS


• XML schema


• File geodatabase


• sdeexport


• ASCII


• Personal geodatabase

Each and every one is better than using shapefiles.  Do not ever use shapefiles
to transfer SQL data -- there is simply too much data lost in translation.

- V

[ESRIESRI1]

Thanks Vince. Can you give URL/link to find step by step procedure do the same. I do not want to use ESRI Tools for the data migration.

Hope am not bothering you much. Thanks again.

[VinceAngelo]

It's not possible to give step-by-step instructions for an inadequately defined task.
Removing the best tools available from consideration only makes your task much
more difficult.  If you have no intention of using Esri tools, then looking to an Esri
Forum for solutions seems counterproductive.

- V

[ESRIESRI1]

Vince-  I think I have not communicated properly. We are ESRI shop, but for the current project we are using POSTGRESQL as DB. I am using ArcGIS Desktop (basic) version to publish maps. Hence I am not sure whether I can use ArcGIS Desktop to migrate the data from One DB to another. I would like to do it using DB scripts or other easy methods.  I am developer , not a DB admin hence looking for easy solution to migrate entire data from oracle (sitting on Linux) to Windows 2012 (PostgreSQL). Please help me.

[VinceAngelo]

The complexies of exporting geodata from Oracle to PG using only Oracle or PG tools boggles.

Some geodatabase data can *only* be transferred using ArcGIS tools.  Even if you only have
a few simple featureclasses, it is much easier to use Desktop than learn how to use 'sdeexport'
and 'sdeimport' to transfer individual tables. 

The lack of Standard (Editor) or Advanced (ArcInfo) is a nearly insurmountable obstacle to
geodatabase creation and loading.  How are you planning to enable the enterprise geodatabase
in the PG 9.2 instance?

- V

[lauravganges]

If you have multiple schemas on an Oracle instance, would you choose sdeexport /sdeimport to move from Oracle to Postgresql?

[VinceAngelo]

There are multiple possible interpretations of "schema" in this context -- do you mean
having multiple USERs or making use of a user-schema geodatabase?

I *always* have multiple users in my Oracle instances, so this would have no bearing
on my data transfer methodology.  I almost never use user-schema geodatabases,
but doing so wouldn't influence how to transfer data either.

The decision tree is simple.  For each table:

1. Is the table an unversioned simple feature class (ideally, unregistered with ArcGIS)?

• If yes, you may use any data transfer methodology


• If no, you must use a geodatabase-aware tool.

Most of the data I shift between instances is both simple and very large.  I have a huge
suite of ASCII conversion tools available, so I'm likely to use 'asc2sde' (probably taking
the opportunity to optimize the data with column reorganization).  Most geodatabase
administrators don't work with the same data I work with, and most of them find using
ArcGIS tools perfectly adequate for the task.

There are very few situations where I would recommend use of sdeexport/sdeimport
for data transfer to anyone who didn't already have years of experience using them.

- V

[lauravganges]

Vince, thank you so much, this is extremely helpful.
Since all database schemas (users) contain both geospatial and tabular data, it looks like we can /should use ESRI to migrate the data from one database platform to another.
Thank you again...

[VinceAngelo]

If you have large or very-large tables, then the allure of simple bulk-loading can become
overpowering.  My cut-off point is somewhere between one and five million rows in a table.
Not coincidently, it is tables of this size (or larger) which are most impacted by spatial
fragmentation, and can therefore be aided by an ORDER BY export during migration.

- V