Frequently asked question

Can a PostgreSQL database be restored to a newer version using a pg_dump file?

Last Published: April 8, 2024

Answer

Usually, in-place upgrades of PostgreSQL can be performed using pg_upgrade, however, due to changes in data type support beginning with PostgreSQL 12.x, if the database contains a geodatabase, you must use the pg_dump and pg_restore commands to upgrade to PostgreSQL 12.x or later. When using this method to upgrade, it removes unused, unsupported OID fields from geodatabase system tables that would otherwise prevent upgrading PostgreSQL.

Esri recommends using pg_dump to create an archive file, drop the database, re-create the database with the same name, and restore the archive file to the re-created database using pg_restore.

Note:
When restoring the PostgreSQL database, you can restore the pg_dump file to a newer version of PostgreSQL. Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version.

Article ID: 000032177

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Related Information

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options