[postgis-users] Upgrade PostgreSQL/PostGIS - Version 9.2.9/2.1 to 10.3/2.4.4

Regina Obe lr at pcorp.us
Fri Mar 8 11:32:40 PST 2019

I just finished an upgrade on windows from 2.1 to 11 / 2.5 (no ArcGIS involved) and one on CentOS 6 from 2.1 to 9.6 (with ArcGIS) 


There are two approaches you can take. In both approaches you should install

PostgreSQL 10.3/2.4 first on a separate port and make sure you install the ArcGIS extensions st_geometry etc, for 10.3/2.4 if you are using those


Approach 1 do a pg_dump of your 9.2 database  – note you'll want to script users separately and ensure they are in place before you do the restore

                Do pg_restore on 10.3/2.4


Approach 2 using pg_upgrade – I tend to prefer because it's much faster than approach 1 and migrates everything. This is especially handy if you have several databases or a fairly complex configuration.

Going from 2.1 to 2.4 is a bit tricky because some functions were removed in 2.2 so need to be removed before you can do an upgrade.


Basic steps I have outlined here - http://www.bostongis.com/blog/index.php?/archives/273-Using-pg_upgrade-to-upgrade-PostgreSQL-9.3-PostGIS-2.1-to-PostgreSQL-11-2.5-on-Yum.html (though this is CENTOS specific, the general upgrade params are much the same)


I should also mention if you feel squeamish about doing upgrade yourself, I'm available for consulting.









From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of ivo silvestre
Sent: Friday, March 08, 2019 12:50 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Upgrade PostgreSQL/PostGIS - Version 9.2.9/2.1 to 10.3/2.4.4




Sorry if the subject is repeated, but I did not find this specific topic. 


I have PostgreSQL 9.2.9 and PostGIS 2.1 running on Windows Server and I have to upgrade to version 10.3 / 2.4.4.

The specific versions is due to PostgreSQL database requirements for ArcGIS 10.6.x (http://desktop.arcgis.com/en/system-requirements/latest/database-requirements-postgresql.htm).


All backups are made. Together, all the databases have around 4GB.


What's the best way to make this migration without affecting any configuration (web services, users privileges, database connections, etc.)?


Kind regards,

Ivo Silvestre

especialista em SIG ● GIS specialist


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190308/1345a186/attachment.html>

More information about the postgis-users mailing list