[postgis-users] When To Upgrade

[Paul Ramsey]

As a special bonus to mailing list subscribers (someone feel free to  
dump this in the wiki) here is my early morning summary of when to do  
a  dump/restore upgrade:

* DO dump and restore if you have upgraded your PostgreSQL software  
by 0.1 or more (8.0 to 8.1, for example).
* DO NOT dump and restore for PostgreSQL upgrades of 0.0.1 (8.0.1 to  
8.0.2).
* DO dump and restore if you have upgraded your PostGIS software by a  
major version (0.9 to 1.0, for example).
* DO run the "soft upgrade" process whenever you upgrade your PostGIS  
software.  <http://postgis.refractions.net/docs/ch02.html#soft_upgrade>
* DO NOT dump and restore for upgrades that only need the "soft  
upgrade", unless you really enjoy doing it.  In that case, do it a lot.

Double bonus Frequently Asked Questions about upgrade types:

Why do I have to dump and restore?  Because if using free software  
were easy, everyone would be doing it!  No, actually, because the  
dump and restore process moves your data through a version neutral  
state, the dump file.  Sometimes, software upgrades change the format  
of the data the DB holds on the disks, so the new software cannot  
directly read the data from the old software.  By taking the data out  
of the old format, into the dump file, then loading it back into the  
new software, the version upgrade of the data itself is completed.   
PostgreSQL changes their on disk formats for every "major" release  
(0.1 releases, about every 9-12 months).  PostGIS changes on disk  
formats very rarely, only once so far, in the 0.X to 1.X transition.

If a soft upgrade does not dump and restore, then what is it doing?   
PostGIS is a set of functions and objects that are added on to the  
PostgreSQL system.  The adding is accomplished with the lwpostgis.sql  
script, which contains things like "CREATE FUNCTION" calls and so  
on.  When new functions are added, or function signatures changed,  
new CREATE FUNCTION statements are needed.  Compiling and installing  
a new PostGIS library makes the functions theoretically available to  
the database system, but they are not *actually* available until the  
correct CREATE FUNCTION calls are made to tell the database of the  
existence of the functionality.  The soft upgrade makes sure your  
PostGIS installation is aware of all of the functionality in your  
version of the PostGIS library.  A corollary of this fact is that if  
you don't care about new functionality, only bug fixes to existing  
functionality, you can sometimes even ignore the soft upgrade when  
updating your library.  This is not recommended, because sometimes  
the bug fixes will be in pl/pgsql scripts, rather than the PostGIS  
library, and you will not get script fixes unless you run the soft  
upgrade.