Upgrade PostgreSQL version

Question

Hi Team,

We have recently upgraded ArcGIS Enterprise from version 11.3 to 11.4. Previously, our deployment was using PostgreSQL version 12, which was compatible with 11.3. However, with the new upgrade to 11.4, PostgreSQL 12 is no longer supported.

To ensure compatibility and continued stability, we now need to upgrade the PostgreSQL version to 15 or later version.

I would appreciate your assistance in upgrading the PostgreSQL.

Thanks & Regards,

Kristofer

Kristofer · Answer

Hi @VenkataKondepati @George_Thompson @MarceloMarques ,

Thanks for the quick response and Support !!

Thanks & Regards,

Kristofer

Marcelo Marques · Answer

@VenkataKondepati , @Kristofer , @George_Thompson

If you have PG 12 geodatabases, then you can attempt to upgrade the PG instance from PG 12 to PG 15.

But, if you have PG 11 geodatabases, then read the information below carefully.
-------------------
How To: Upgrade to PostgreSQL 12.x if the Database Contains a Geodatabase

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

-------------------

I have included the information about this in my community.esri.com white paper.

How to Move the PostgreSQL Enterprise Geodatabase with pg_dump and pg_restore

-------------------

After the migration, we also need to upgrade the PostGIS extension.

How to Upgrade the PostgreSQL and PostGIS version for the Ent. Geodatabase on Windows

How to Upgrade the PostgreSQL and PostGIS version for the Ent. Geodatabase on Linux

For more best practices, visit my community.esri.com blog.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

System Requirements

https://support.esri.com/en-us/knowledge-base/faq-what-postgresql-database-versions-are-supported-by--000030040

https://enterprise.arcgis.com/en/system-requirements/latest/windows/database-requirements-postgresql.htm

-------------------

Question: So I am using the pgdump utility from a PG 12.x (or later) to dump the database from the PG 11.x  geodatabase?

Answer: Yes, you must, see below the reason. In your case you will use pg_dump 15 version.

IMPORTANT:
Problem: pg-10 dump fail to restore on pg-12
"pg_restore: warning: restoring tables WITH OIDS is not supported anymore"
Solution: create the dump again using pg-12 and try to restore again
--pg-10-5438 --> pg-14-5437
/usr/pgsql-14/bin/pg_dump --version
/usr/pgsql-14/bin/pg_dump -h mondblnxpg -U postgres -p 5438 -F c -b -W -f /mnt/nfs/redarchive4/db_bkp_active_yes/mondblnxpg1/pg_5414/agmgdbpg/agmgdbpg_06_27_2025_070000.bkp agmgdbpg
pwd: *****
-------
pg_dump: warning: WITH OIDS isnot supported anymore (table "i10")
....
-------

Recommended Upgrade Steps to avoid breaking ArcGIS Server Services

* create a backup of the PG 11 / 12 geodatabase using pg_dump 15
* you can use pg_dump 15 if you install postgresql 15 in some other machine
* then create a new PG 15 instance to test the restore with pg_restore 15
* create new geodatabase connection files and make sure you can connect with ArcGIS Pro
* upgrade the PG 15 geodatabase arcsde repository with the new arcgis pro version that you plan to use.
* upgrade the postgis extension in the PG 15 database
* now create a new backup of the PG 15 database using pg_dump 15
* drop the database in the PG 11 / 12 instance
* upgrade the PG 11 / 12 instance to 15
* or drop the PG 11 / 12 instance and install PG 15, keep the same port number
* restore the backup of the database using pg_restore 15
* eccollo!!! all arcsgis server services will continue to work !!!
You kept the same database name and the same PG instance name and same PG port number.

I hope this clarifies.

George Thompson · Answer

Here is a good blog from @MarceloMarques : https://community.esri.com/t5/mapping-and-charting-enterprise-databases/how-to-upgrade-the-postgresql-and-postgis-version/ba-p/1307075