CLEAN HARD UPGRADE ON WINDOWS

General Hard upgrade instructions are documented in ​PostGIS Hard Upgrade

You need to do a hard upgrade if either:

• You are using a pre-alpha release of a major release, like pre-release PostGIS 2.0.
• You are upgrading from one major version to another, such as 1.* to 2.*.

1. Create a backup of your postgis database as usual. You can do this with pg_dump as the instructions describe or PgAdmin III by right-clicking on the database → Backup. Make sure to use "Custom" backup for format if you are using PgAdmin III.

2. Create a new postgis database installed with the postgis version you want to upgrade to. Note if you are using PostgreSQL 9.1 and PostGIS 2.0 — you can use the new EXTENSION SQL commands in psql or pgAdmin III sql window after you have created a new database

to spatially enable a database.

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

You may also want to install legacy.sql if you have functions that use deprecated PostGIS functions.

3. If you don't have perl on your windows box, download ​ActivePerl and install. You can use the 64-bit or 32-bit version even if you are using 32-bit PostgreSQL. ​http://www.activestate.com/activeperl/downloads.

When prompted to do so, choose to add Perl to your environment path and map perl to .pl scripts.

Note that ActivePerl also has packages for MacOS, Linux, but is generally not necessary for those platforms since they come prepackaged with perl.

4. Create an upgrade.bat file with contents something like this and save in same folder as your database backup:

   set PGPORT=5432
   set PGHOST=localhost
   set PGUSER=postgres
   set PGPASSWORD=yourpasswordhere
   set THEDB=mydb
   set BACKUPFILE=mydb.backup
   set PGINSTALL=C:\Program Files\PostgreSQL\9.2
   set PATH=%PATH%;"%PGINSTALL%\bin"
   perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt
   pause
   

5. Edit the script accordingly to your path settings. Note that utils folder is packaged with the PostGIS tar balls

if not present in your PostGIS install. Download the tar ball for the PostGIS you want to upgrade to and copy the utils folder to the same folder as your backup. ​http://postgis.net/source

6. Right mouse click on the .bat file (you may have to choose — Run as Administrator) or run if run as administrator is not available.

7. If all is working — you should see a screen something like:

ALTER TABLE
ALTER TABLE
SET

Clicking enter after the process is done will close the batch script.

8. When all is done, check the errors.txt file to see if there are any serious errors. Serious errors are tables that did not come back, or some of your own functions that did not come back, your specialty constraints and indexes. PostGIS function failures should not show, but if they do, you can ignore them.