Upgrade to PostgreSQL 12.x if the database contains a geodatabase

Summary

A significant change to PostgreSQL 12 and later is the removal of the special behavior of internal OID columns. Tables can no longer be created using the WITH OIDS clause, as described in the PostgreSQL release 12 notes . Prior to PostgreSQL 12, certain geodatabase system tables in PostgreSQL were created using the WITH OIDS clause. Refer to the solution section for information on how to upgrade your geodatabase when using a PostgreSQL 12.x instance.

Please note the following points related to this change while working with ArcGIS:

• Using ArcGIS clients with geodatabases in PostgreSQL 12 and later:
  ArcGIS versions earlier than 10.8.1 and ArcGIS Pro 2.6 are not supported with geodatabase data stored in PostgreSQL 12 and later. These ArcGIS client versions attempt to create certain geodatabase tables using the WITH OIDS clause. An example error of an earlier client failing to create a table in an ArcGIS 10.8.1 and/or ArcGIS Pro 2.6 geodatabase in PostgreSQL 12.x is as follows:

Failed to create feature class.
DBMS table not found [ERROR: relation map.i48 does not exist]

Certain editing operations also fail when using an earlier client to edit data in a geodatabase in PostgreSQL 12 or later.

• Using pg_upgrade for upgrades to PostgreSQL 12:
  Using pg_upgrade for upgrades to PostgreSQL 12 and later is not supported if your database contains a geodatabase. The pg_upgrade utility detects the presence of tables created using the WITH OIDS clause and suggests an (ALTER TABLE) action to be taken on those tables. Because manual SQL changes to geodatabase system tables are not supported, use the method described in the solution section below when upgrading your geodatabase and upgrading your PostgreSQL database to version 12 or later.

Procedure

Using pg_dump/pg_restore for upgrades to PostgreSQL 12
There are several options for creating backups of PostgreSQL databases. However, for PostgreSQL databases used to store geodatabases, Esri recommends you use pg_dump to create an archive file, drop the database, re-create the database with the same name, and restore to the re-created database using pg_restore. This backup and restore methodology can be used before or after upgrading the geodatabase to ArcGIS version 10.8.1 or ArcGIS Pro 2.6. You can upgrade directly from a 10.6.x, 10.7.x, or 10.8 geodatabase if your database is at a supported release for ArcGIS Pro 2.6 or ArcGIS 10.8.1.