Oracle to Postgresql

Question

We are planning to conduct a prototype to migrate data from Oracle to postgresql. Is there any standard guidelines/documentation available for the migration?

This would not only include the data, but migrating the user roles, privileges, logins etc. any standard guide outlining the best principles would be helpful

The target version of ArcGIS would 10.8 (current version ArcGIS 10.2.1). We will have ArcFM in the existing and the target environment

JohnLovato · Answer

Suman,

We are undergoing the same exercise.  For the roles and user, if you have fewer than 1000, the EnterpriseDB Migration Toolkit is a good option. https://www.enterprisedb.com/products/oracle-to-postgresql-migration-toolkit-move-from

The migration portal can give you an assessment of the LOE for moving general workloads to Postgres pr Enterprise DB Advanced Server..  https://migration.enterprisedb.com/

Here is a complete guide to general Oracle to Postgres migration that has some things you may not have thought of.

https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative?sbrc=1n3eFq4MdbMJq8Qi1Bdz8SA%3D%3D%24uOwjHro3_qzZNDDx74OInw%3D%3D

George_Thompson · Answer

I can appreciate and understand the complexity with the migration, especially with ArcFM involved.

Doing the option #2 below is a good one, very similar to the copy/paste directly method.

I am contemplating one of the following options:-

1. .....

2. Convert source data to FGDB, then move to target Postgres

Using the Copy/Paste should bring over all of the related data, Geometric Network, etc.

You will need to make sure that you reconcile / post all versions (delete after edits posted), then get a full compress before copying over data. This will prevent losing any edits in a version.

SumanD · Answer

Well there are a few complex stuff to deal:

1. The DB has ArcFM implemented

2. The database objects are in Geometric Network

3. Few feature classes have more than a couple of million records, and these FCs have tables related to it. So copy/paste may hang the system

3. Source version Oracle 12/ArcSDE 10.2.1- target Postgres/ArcSDE 10.8

I am contemplating one of the following options:-

1. Use FME

2. Convert source data to FGDB, then move to target Postgres

4. Use ArcPy (Append/copy/load) tools

3. A combination of all the above options

Please advise