Feel the excitement!

Major upgrades for both PostGIS and Postgresql were released in the last few weeks.  Postgres moved from 9.5 to 9.6 and PostGIS from 2.2 to 2.3.  With both programs moving up major version numbers you have to take an extra step to make sure your upgrade goes smoothly.  In Windows PostGIS is an easy install, it comes bundled with the Postgres installer in the StackBuilder.  This is very handy, except in cases like this were both programs are jumping to new major versions.  You’ll run into a snag if you try to use pg_upgrade to migrate your database from the old to new server.  Pg_upgrade will look for the old PostGIS libraries in your new Postgres directory, not find them and error out.  To get around this you’ll need to upgrade PostGIS first.

To start download the new version of PostGIS for your current Postgres install from here: http://download.osgeo.org/postgis/windows.

Once installed upgrade PostGIS in your spatial databases by running:

ALTER EXTENSION postgis UPDATE TO '2.3.0';

Change 2.3.0 to the version up PostGIS you are upgrading to. Once you upgraded PostGIS in your old spatial databases run the Postgres installer for the new version. The installer will install the new version in parallel with your existing install(s), by default it will run the new version on port 5433 instead of 5432.

After the new version is installed make sure both the old and new server services are stopped, then in each servers data folder open the pg_hba.conf file and change the connection method to ‘trust’.  Next open a command prompt and navigate to a directory you have write permission in and run:

"c:\Program Files\PostgreSQL\9.6\bin\pg_upgrade.exe" -b "c:\Program Files\PostgreSQL\9.5\bin" -B "c:\Program Files\PostgreSQL\9.6\bin" -d "c:\Program Files\PostgreSQL\9.5\data" -D "c:\Program Files\PostgreSQL\9.6\data" -p 5432 -P 5433 -U postgres

Change the version numbers and database superuser account as needed.