Error upgrading Enterprise Geodatabase

[MathieuVillemont]

Hi,

I'm trying to migrate my Enterprise geodatabase from PostgreSQL 10/ArcGIS 10.9.1 to PostgreSQL 15/ArcGIS 11.3

I've already manage to migrate it to PostgreSQL 15 using pg_dump/pg_restore and I'm now trying to upgrade it to 11.3 using ArcGIS Pro.

Although the checkup says every prerequisites are ok, when I run the upgrade geoprocessing it fails with these errors in sde_setup.log:

[04:52:47.451] In SE_arcsde_schema_setupEx() ...
[04:52:47.452] Calling sdepgsrvr.dll::DB_arcsde_setup() ...
[04:52:47.494] ev.SQLstate = 42P01
SDE DBMS Error: -37  ERREUR:  la relation « pg_dist_node » n'existe pas
LINE 1: SELECT nodename FROM pg_dist_node WHERE position('localhost'...
                             ^
[04:52:47.497] Current bytea_output setting in postgresql.conf is "hex". 
Require 2 bytes of storage for each byte of input data. 
[04:52:47.512] ArcSde is already at an acceptable release:
	10.3.0 10.3.1 geodatabase
[04:52:47.519] S_set_drop_gist_index_flag error.
[04:52:47.519] Warning: Check ST_GEOMETRY TYPE and functions: Error -471
[04:52:47.519] ST_GEOMETRY upgrade not completed (-471).
[04:52:47.520] ERROR installing/upgrading ArcSDE, Error = -471

What is this pg_dist_node ?

Fun fact: I do not have any log in GDBUpgrade.log 

[RyanUthoff]

EGDB upgrade issues are very hard to troubleshoot in my experience. I personally do not know how to resolve that specific error message, but I'll comment and say that we've had to migrate to new EGDBs in the past to get around upgrade errors. 

One workflow is create a new EGDB at the 11.3 version and then copy all your data from your old EGDB to it. Then delete the old EGDB and rename the new EGDB to the same name as the old EGDB. Luckily with 11.3 EGDBs, Esri now allows you to rename them without it breaking the DB.

Edit: I apologize, it appears that you might be only to rename EGDBs that are MS SQL Server DBs. Not sure about PostgreSQL.

[MichaelVolz]

Ryan:

You said "One workflow is create a new EGDB at the 11.3 version and then copy all your data from your old EGDB to it. Then delete the old EGDB and rename the new EGDB to the same name as the old EGDB. Luckily with 11.3 EGDBs, Esri now allows you to rename them without it breaking the DB."

Do you know at what version of enterprise this capability worked of renaming an EGDB without breaking the DB?

Is there any documentation related to this new capability?

[RyanUthoff]

Oh no, I apologize. It appears this only applies to MS SQL Server EGDBs, at least according to the what's new documentation and this documentation. I thought it applied to all DBs. I'm not 100% sure if it's possible to rename a PostgreSQL EGDB or not. But this functionality was released in Pro 3.0 and Enterprise 11.x.

[George_Thompson]

You may want to reach out to technical support about this and work with a geodata analyst.

[VinceAngelo]

The PostgreSQL team made a significant change at PG 12 that eliminated a hidden column that Esri was using as the primary key on the objectid allocation algorithm. I'm not sure you can make the leap from PG10 to PG15 just by doing a backup/restore, since all your "i-tables" are now invalid.

If the tables aren't versioned, you might be better off exporting through file geodatabase, or just copying from the PG10 instance to the PG15 one (you can run both on the same box using different ports).

Tech Support likely has better tools to work out the problem here.

- Vince

[MathieuVillemont · Accepted solution]

I eventually managed to upgrade my EGDB.

First of all, my EGDB were in 10.6.1 and not 10.9.1 so I had first to upgrade them in 10.9.1 before doing so in 11.3.

Steps :

• Install PG 13 which is both supported by EGDB 10.9.1 and 11.3
• pg_dump to export EGDB from PG 10  (this remove OID fields which are not supported from PG 13)
• pg_restore to import EGDB into PG 13
• upgrade to EGDB 10.9.1 with AGS Pro 2.9
• upgrade to EGDB 11.3 with AGS Pro 3.3

I did not migrate to PG 15 but this should be straightforward using pg_dump/pg_restore and AGS Pro 3.3