The PostgreSQL 11 is not supported or "compatible" with 10.8.1 ArcMap, ArcServer or Arc Pro 2.7. For the ST_geometry file download is a simple solution and can be found under Arc Pro 2.8 in My Esri, but making the upgrade for PostgreSQL to be compatible with 10.9 and 2.8 for Arc Pro has proven to be a challenge.
PostgreSQL 12.4 no longer supports the OID columns in PostgreSQL 11 and will throw a lot of warnings once the install of PostgreSQL 12 is installed and you begin restoring the databases.
I have gone through quite a few variations of the steps for this upgrade including doing it all through command prompt, using pgAdmin4 and dBeaver, all having errors or failing to allow connections to the database from the database connections in Arc Map, Catalog, or Pro.
Once I get it figured out completely I'll post the solution but so far making the upgrade happen has not been an easy process. If anyone has the order of operation or the combo to unlock the secret of this upgrade, do tell! Otherwise stay tuned.
Solved! Go to Solution.
Hello world!
The solution I discovered to work for me is the following. I worked with a private consultant Patrick Headley to get through some of the issues and he is credited with the idea of a full server schema backup and the remapping of the PGDATA and LD_LIBRARY_PATH in the advanced system variables for the server.
I created a full server schema backup and created individual database data backups using pgAdmin4 for version 11. Then turned off the PSQL 11 service and switch it to manual start. Installed the 12.4 Postgresql and began the restore of each database in the order of postgres database first and the production database second.
Restored the server schema command line:
C:\Program Files\PostgreSQL\12\bin>psql -U postgres -h localhost -p 5433 -f "D:\\backuprestore\server_schema_only.backup"
Data restore command line:
C:\Program Files\PostgreSQL\12\bin>pg_restore -c -U postgres -p 5433 -v -d postgres "D:\\backuprestore\backup_dataonly_production.backup"
C:\Program Files\PostgreSQL\12\bin>pg_restore -c -U postgres -p 5433 -v -d production "D:\\backuprestore\backup_dataonly_production.backup"
The errors I did get didn't halt completion or inhibit the function of the database after the restore.
An example of the error:
ERROR: duplicate key value violates unique constraint "i100_pk"
DETAIL: Key (id_type, num_ids, base_id)=(2, -1, 3) already exists.
Thanks also to Kushboo K. and Vaibhav S. at Esri support for working through the workflow and assisting in verifying it as well.
Off to start the next step, upgrading to 10.9 on our staging environment.
I am confused on your statement below:
The PostgreSQL 11 is not supported or "compatible" with 10.8.1 ArcMap, ArcServer or Arc Pro 2.7.
PostgreSQL 11.7 (64 bit) with PostGIS 3.0.1 is supported at 10.8.x versions:
https://desktop.arcgis.com/en/system-requirements/latest/database-requirements-postgresql.htm
In the link above there is some guidance about the upgrade to PostgreSQL 12:
"Due to changes in data type support in PostgreSQL 12.x, if your database contains a geodatabase, you must use the pg_dump and pg_restore commands to upgrade to PostgreSQL 12.x. When you use this method to upgrade, it removes unused, unsupported OID fields from geodatabase system tables that would otherwise block you from upgrading to PostgreSQL 12.x.
Once you upgrade to PostgreSQL 12.x, if the database contains a geodatabase, older ArcGIS clients can no longer use the data; you must upgrade your ArcGIS clients."
Docs for backup / restore:
https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/backups-postgresql.htm
Does that information help you along?
If you need more details or assistance, I would recommend reaching out to Esri technical support to see what they may be able to assist with.
Hi George,
The compatibility statement is more so saying supported and after many discussions with the Esri support team about not being able to create new databases, the ST_GEOMETRY file download, the OID no longer supported so you must piece by piece restore everything in command line, create spatial data type when connected as the superuser.... Long story short I have been working with the Esri support and another database consultant since May.
What I am looking for is something that is missing in the order of operations. Which tiny step is missing that allows for the connection to the Arc "what you choose" after the restore? Which method of dumping or backup is going to work with which method of restoring the database?
The 5th Esri support person is working with me on this and everyday I try two or more different configurations and add to my notes. I am saying stay tuned because there's something we don't have documented yet that is a critical step to get passed what should be a cut and dry upgrade.
Workflow to restore a PostgreSQL Geodatabase.
That's the routine and the changes I have been making included various pg_restore command configurations and options. I tried the administration restores. When there finally a completed no errors, then the Esri side wouldn't work.
So stay tuned, I think we'll see what I am doing wrong or what in this system needs to fixed. Today I am trying a schema only back up using pgAdmin4 of the PostgreSQL 11 server then doing a restore of the schema on Postgresql 12 and then each piece of the database independently. It's worth a shot at this point.
Thanks for that detailed workflow. The one items that I am curious about is why you do step 13?
The help for that tool says:
.... This allows you to use the ST_Geometry SQL type to store geometries in a database that does not contain a geodatabase....
https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/create-spatial-type.htm
When you restore the dump file it should include the ST functions as part of the SDE schema. This is my understanding. I have NOT done this workflow before so cannot confirm 100%.
I actually only did it because one of the Esri support people told me to. When I run the tool it successfully completed after several times of failure. There's a lot of things that I would expect to come over in the restore that do not. The lesson so far is to pay for SQL Server and avoid the perils of free open source upgrades.
I can say that there are pros / cons to SQL Server vs. PostgreSQL. I have clients that are using each. I can say that I would agree the upgrade process and migration to a new instance for SQL Server is "easier".
Maybe some other users of PostgreSQL could chime in on this thread.
Hello world!
The solution I discovered to work for me is the following. I worked with a private consultant Patrick Headley to get through some of the issues and he is credited with the idea of a full server schema backup and the remapping of the PGDATA and LD_LIBRARY_PATH in the advanced system variables for the server.
I created a full server schema backup and created individual database data backups using pgAdmin4 for version 11. Then turned off the PSQL 11 service and switch it to manual start. Installed the 12.4 Postgresql and began the restore of each database in the order of postgres database first and the production database second.
Restored the server schema command line:
C:\Program Files\PostgreSQL\12\bin>psql -U postgres -h localhost -p 5433 -f "D:\\backuprestore\server_schema_only.backup"
Data restore command line:
C:\Program Files\PostgreSQL\12\bin>pg_restore -c -U postgres -p 5433 -v -d postgres "D:\\backuprestore\backup_dataonly_production.backup"
C:\Program Files\PostgreSQL\12\bin>pg_restore -c -U postgres -p 5433 -v -d production "D:\\backuprestore\backup_dataonly_production.backup"
The errors I did get didn't halt completion or inhibit the function of the database after the restore.
An example of the error:
ERROR: duplicate key value violates unique constraint "i100_pk"
DETAIL: Key (id_type, num_ids, base_id)=(2, -1, 3) already exists.
Thanks also to Kushboo K. and Vaibhav S. at Esri support for working through the workflow and assisting in verifying it as well.
Off to start the next step, upgrading to 10.9 on our staging environment.