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.
Solved! Go to Solution.
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
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.
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
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
Thanks Vince for the feedback. After running Oracle for close on 20 years I'll have to get up to speed with PostgreSQL .
@JamesMorrison1 - see my community.esri.com blog for database guidebooks for PostgreSQL.
Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices
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