PostgreSQL 11 upgrade to 12.4 needed to upgrade to Arc Server 10.9

[Anonymous User]

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.

[George_Thompson]

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

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-postgresql/restore-geodatabase-postg...

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.

[Anonymous User]

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. 

1. Create PostgreSQL 11 dump file for postgres and sde owned database.
2. Create role dump
3. Create schema only dump for postgres, public and sde owned database.
4. Stop PostgreSQL 11x service
5. Install PostgreSQL 12.4
6. Copy the st_geometry.dll file into \\Postgresql\12\lib folder
7. Create/edit system environment variables. Click New and enter PGDATA as the variable name. Enter 'C:\Program Files\PostgreSQL\12\data ' as the value. Click New and enter LD_LIBRARY_PATH as the variable name. Enter 'C:\Program Files\PostgreSQL\12\lib ' as the value. Click New and add the path to the PostgreSQL bin folder. 
8. Open pgAdmin/dBeaver and connect to the PG 12 instance. Restore or create sde login role with superuser privileges, and if you have a data owner other than sde, create that login role.
9. Create a database name owned by sde
10. Create sde and data owner schemas in the new database, owned by their own roles respectively.
11. Run queries within PGadmin to set search path and create postgis extension.
12. Alter the pg_hba.conf file to allow connections to your network.
13. 7. Run the Create Spatial Type geoprocessing tool from the appropriate ArcCatalog version on a database connection to your database as the Postgres user (this tool must be ran as the head RDBMS user).
14. To restore the data, open a command line on the PostgreSQL server, and cd to the PG home \lib folder to access the pg_restore function. Restore public schema.
15. Restore sde schema.
16. Restore the data owner schema.
17. Connect to the new database and create sde schema:(Create other schemas if present and grant usage on sde to other schemas).
18. Alter database search path.
19. Restore the public schema followed by sde and different data owner schemas.
20. Connect in Arc Catalog, Arc Pro to test function and availability, check web applications and published services.

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.  

[George_Thompson]

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

[Anonymous User]

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.

[George_Thompson]

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.

[Anonymous User · Accepted 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.