topic Re: Migrating from oracle to PostgreSQL 9.2 in Data Management Questions

Migrating from oracle to PostgreSQL 9.2

ESRIESRI1 — Tue, 05 Nov 2013 21:14:50 GMT

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.

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Tue, 05 Nov 2013 23:16:42 GMT

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

Re: Migrating from oracle to PostgreSQL 9.2

ESRIESRI1 — Wed, 06 Nov 2013 13:25:57 GMT

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.

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Wed, 06 Nov 2013 14:03:51 GMT

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

Re: Migrating from oracle to PostgreSQL 9.2

ESRIESRI1 — Wed, 06 Nov 2013 14:36:28 GMT

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.

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Wed, 06 Nov 2013 15:16:48 GMT

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

Re: Migrating from oracle to PostgreSQL 9.2

lauravganges — Mon, 11 Nov 2013 19:25:19 GMT

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

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Mon, 11 Nov 2013 20:32:19 GMT

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:

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

Re: Migrating from oracle to PostgreSQL 9.2

lauravganges — Wed, 13 Nov 2013 14:43:11 GMT

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...

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Wed, 13 Nov 2013 15:45:11 GMT

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

Re: Migrating from oracle to PostgreSQL 9.2

lauravganges — Wed, 13 Nov 2013 16:00:30 GMT

Regardless of the table size/user(schema) size, the migration can occur through ARCCatalog? I am thinking that moving from one database/OS to another database/new OS will best be accomplished by your first suggestion (the drag/drop in ARCCatalog). The allure of bulk load (esp to a DBA) is easily overpowered by the fear of losing data during a migration ;o)

Thank you for your clear explanations.

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Wed, 13 Nov 2013 16:59:55 GMT

Yes, you can use Desktop to process any table, BUT, if the row count is greater than
5 million, I'd recommend using multiple passes out of the table, appending the
results on import.

It's actually rather difficult to lose data in this sort of migration (partially because you
need to come up with a validation procedure to confirm that data hasn't been lost).

- V

Re: Migrating from oracle to PostgreSQL 9.2

lauravganges — Thu, 14 Nov 2013 13:41:25 GMT

One last question? When you refer to "bulk-loading", which ESRI tool/method are you indicating should be used? There are several tables with more than 5 million rows.
Also, in the database world, simple export and import of data defragments and optimizes data. Is this true for the bulk-loading method, as well?
Thank you SO much...

Re: Migrating from oracle to PostgreSQL 9.2

VinceAngelo — Thu, 14 Nov 2013 14:25:06 GMT

"Simple bulk-loading" is a euphemism for "anything but Desktop."

A simple export/import has no impact on spatial fragmentation. Spatial fragmentation
occurs when data is loaded in other than spatial order (usually over time). Imagine
a lightning_strike table that contains all cloud-ground events over a continent for
several years; this table is very likely to be spatially fragmented. Spatial queries will
hit the spatial index, which will quickly determine the rows, and then the database will
effectively need to run a full table scan (possibly multiple times over) fetching the rows
which are randomly distributed across the table. Spatial defragmentation is the process
of using spatial queries or a representative proxy (eg. county FIPS code) to extract data
in other than the orginal order, with the goal of improving spatial query performance.
Of course, while reordering data by a spatial component will improve spatial queries,
it may negatively impact ordering for other queries (e.g. strike_time), so sometimes
compound ordering is necessary to preserve query performance across multiple
attributes.

- V