[postgis-users] When To Upgrade
pramsey at refractions.net
Sun Mar 5 08:08:40 PST 2006
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
* 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
* 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
More information about the postgis-users