we just got a new PostgreSQL Database (12.6) with Postgis 3.0.3. When trying to publish services on our ArcGIS Server that access this database, we receive the following errors:
|SEVERE||26.03.2021, 13:39:58||Error executing tool. PublishServiceDefinition Job ID: j2f63ddb84e3744ca9e9bc9fb9c54e25f : ERROR 001487: Failed to update the published service with the server-side data location. Please see the server's log for more details. ERROR 001369: Failed to create the service. Failed to execute (Publish Service Definition).||System/PublishingTools.GPServer|
|SEVERE||26.03.2021, 13:39:58||Failed to create the service.: Updating the server connection string for layer ghk_hang failed. Attempted connection string was ENCRYPTED_PASSWORD=<long string>;SERVER=<server>;INSTANCE="DSID=faa6b0d0-03a6-4861-a364-78ff36b33d0e";DBCLIENT=postgresql;DB_CONNECTION_PROPERTIES=<server>;DATABASE=<database>;USER=<user>;AUTHENTICATION_MODE=DBMS. Table name is <database>.<user>.%<table>Please verify the data exists on the server.||System/PublishingTools.GPServer|
|SEVERE||26.03.2021, 13:39:58||DoAfterSDExtraction failed.||System/PublishingTools.GPServer|
|SEVERE||26.03.2021, 13:39:58||SwizzleService failed.||System/PublishingTools.GPServer|
The data exists, and can be accessed from ArcMap and pgAdmin. The computer ArcGis Server runs on can connect to the database server. The database has been registered in ArcGIS Server and can be verified via the data stores menu and through ArcMap. We use the latest versions of ArcGIS server (10.8.1) and ArcMap (10.8.1) with all patches installed. Publishing services with our old Postgres database (9.6) works. Switching to ArcMap 10.6.1 or ArcGIS Pro 2.7.1 yields the same results. Switching to a different server with the same ArcGIS Server version showed no difference.
We tried several hints that were mentioned in other threads, like recreating the data folder / dsconnections.lst, stopping and starting the Publisher Tools and removing and recreating all data store connections.
The only thing that worked is the workaround described here: https://community.esri.com/thread/177553#comment-614422 that revolves around creating a service through the ArcGIS Server Rest API. This way we managed to see that the database can indeed be accessed and the problem is in the publishing process. But we cannot depend on this workaround for our daily workflow...
Okay, this seems to be solved now. The problem was not with the Postgres-Database per se, but with the registration of the database in the Manager. When registering the database, although it was shown in Manager, there was no entry created inside dsconnections.lst, so after adding it manually, it suddenly worked...
Any ideas to why the registering of new data stores failed (and keeps failing) in such a way?