Workflow Manager odd DataStore Errors

[CodyPatterson]

Hey All,

Enterprise 11.2

We have been plagued with a Workflow Manager issue for as long as we've been in 11.2.

We cannot create or delete any Workflows at all, it has completely evaded ESRI support, but I believe through my own research I am getting closer.

In the DataStore, we are finding unusual log messages when attempting to create a Workflow, we are seeing errors like this:

I also seem to be getting this error here:

Any ideas?

Cody

[HenryLindemann]

Hi @CodyPatterson , I had a similar problem with another application, what resolved it for me was to make a backup of the relational database ArcGIS Data Store utility reference—ArcGIS Enterprise | Documentation for ArcGIS Enterprise then uninstall datastore delete all the datastore files under {drive_letter}:\arcgisdatastore and c:\program files\arcgis\datastore and then reinstall datastore. After that you restore your backup ArcGIS Data Store utility reference—ArcGIS Enterprise | Documentation for ArcGIS Enterprise.

I think something breaks in the RDBMS, and since the backup is just a SQL dump this approach rebuilds it.

If you have any other Datastores please back them up as well.

Hope it helps

Regards
Henry
 

[CodyPatterson]

Hey @HenryLindemann 

Thank you for the tips! I will be sure to give this a shot, sadly this will have to wait until the next weekend due to the amount of traffic that goes through our Enterprise environment. If I am able to do this any sooner I will and I will be sure to give an update regarding!

Thank you again,

Cody

[CodyPatterson]

Hey @HenryLindemann 

I seem to be getting this error now:

It doesn't seem to be able to be fixed by disabling the firewall or changing the permissions on the folders. I was also not able to find the postmaster.pid file that is referenced in my pgdata file as listed in this document:

https://support.esri.com/en-us/knowledge-base/error-error-encountered-no-valid-connection-to-configu...

Any thoughts on what to do next?

Cody

[CodyPatterson]

This can be disregarded as of this point, I found that the Postgre config files had somehow inherited an incorrect FQDN for our server!

Cody

[CodyPatterson]

Hey @HenryLindemann 

I was able to get the datastore reinstalled and rebuilt, but I'm so far unsuccessful, seeing these errors here:

Not sure what to do from here as this was the same results as previously sadly 😞

Cody

[HenryLindemann]

Hi @CodyPatterson, looking at your logfile more closely, it looks like you have some database corruption on the table sde.sde_spatial_references. the sde_logfile error is just chatter and can be ignored, it does look like you have different errors now. I would suggest rolling back to a backup that works but that would lead to data loss on your hosted feature services.

You can confirm this by connecting to the datastore database using pgadmin and trying to open this table if you get an error on opening it then it is probably corrupt.

I would spin up your pre upgrade backup on an isolated VM then upgrade that system again and if that succeeds copy over the backup to production if you can afford the data loss on the hosted services.

Regards

Henry

[CodyPatterson]

Hey @HenryLindemann 

Thank you for the response, I assumed it may have been corruption as the database objects aren't correctly represented and I was seeing many issues with the indexes. A backup that would work would be 11.1 in October of 2023, I inspected some log files in that time, and as you mentioned the chatter was still there, but the database was able to select and modify the indexes without issue. 11.2 seems to be the major issue.

An edit as well: The tables do not exist at all in the pgAdmin view, sde_spatial_references and sde_logfile are missing, many of the indexes are missing as well.

I ended up creating this post as well to see if anyone could dive into it, but eventually, I convinced our IT to spin up 4 servers, and I installed Datastore, Server, Portal, and Workflow Manager, and then attempted to do it once again, and the same exact errors came up, even though everything was completely disconnected, and completely fresh. I'm assuming this is a bug, but I may create yet another support ticket to see if they have any ideas before they state it's out of their hands and they must refer me externally:

https://community.esri.com/t5/data-management-questions/datastore-postgresql-errors/m-p/1402719#M450...

Thank you for your help!