Select to view content in your preferred language

Moving from Oracle to PostgreSQL eGDB - kick off points

1216
6
Jump to solution
08-10-2023 05:39 AM
Labels (2)
JamesMorrison1
Frequent Contributor

Hello Everyone.

Sorry to ask a very general question like how long is a piece of string. In the coming months we will open talks with our IT department who currently host our Oracle databases with the aim to migrate to PostgreSQL. This will be perhaps be a 1 to 2 year goal to complete.

From your own experiences are there any general issues which we should address with the IT department and any issues ArcGIS Pro clients may have? For example - lyrx files created pointing at Oracle will no longer work when pointing at PostgreSQL?

Thank you very much for your feedback.

 

0 Kudos
1 Solution

Accepted Solutions
VinceAngelo
Esri Esteemed Contributor

Esri supports multiple RDBMS vendors.  Each have their quirks and foibles. Oracle is the only vendor who forces names to UPPERCASE, so get used to seeing lowercase in field names.

A layer file pointing at an Oracle table will not work against a PostgreSQL table, but only because it's not pointing at the PostgreSQL table. You can change the source of the layer, so this is a non-issue.

The majority of the burden of migrating between databases is the skills of the DBA with the new database. So it really depends on the administrator (and the management above) on whether you can drive such a requirement.

- V

View solution in original post

6 Replies
mj_gis
by
Occasional Contributor

One thing to be aware of is all PostgreSQL field names are lowercase in arcgis. There is no camelcase or uppercase accepted. The data within the field can be camel case and/or uppercase. The only way to get around this is to build your field names in pgadmin, using quotations if you want the field camelcase. However, I am not sure ESRI will read field names built this way. This is not usually a huge problem but will break scripts and web apps that call the field name. 

 

VinceAngelo
Esri Esteemed Contributor

No, if you use double-quotes to force fields to camelCase, ArcGIS will not see the fields.

Field aliases exist and are case independent in comparison.

- V

VinceAngelo
Esri Esteemed Contributor

Esri supports multiple RDBMS vendors.  Each have their quirks and foibles. Oracle is the only vendor who forces names to UPPERCASE, so get used to seeing lowercase in field names.

A layer file pointing at an Oracle table will not work against a PostgreSQL table, but only because it's not pointing at the PostgreSQL table. You can change the source of the layer, so this is a non-issue.

The majority of the burden of migrating between databases is the skills of the DBA with the new database. So it really depends on the administrator (and the management above) on whether you can drive such a requirement.

- V

JamesMorrison1
Frequent Contributor

Thanks Vince for the feedback. After running Oracle for close on 20 years I'll have to get up to speed with PostgreSQL .

0 Kudos
MarceloMarques
Esri Regular Contributor

@JamesMorrison1 - see my community.esri.com blog for database guidebooks for PostgreSQL.
Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
0 Kudos
VinceAngelo
Esri Esteemed Contributor

I'm on the slide toward 35 years as an Oracle DBA, but I've mostly been using PostgreSQL the past 10. I was always multi-platform (Oracle, Oracle/Sybase, Oracle/Sybase/Informix/DB2, Oracle/Sybase/Informix/DB2/SQL-Server, Oracle/SQL-Server/DB2/PostgreSQL, PostgreSQL/SQL-Sever/Oracle/Informix, PostgreSQL/SQL-Server/Oracle), so I'm comfortable with multi-tenanted  configurations, but I've been quite pleased with the maturity of PG 11/12/13/14/15. Dealing with schema names which aren't user or login names is probably the biggest pain point, but at least you don't need to try to run four different releases of ArcSDE in the same instance (on a single laptop with SQL-Server and Oracle and Informix also installed).

- V