[postgis-users] Postgis upgrade

[Barend K=?ISO-8859-1?B?9g==?=bben]

Hi, Mark just told me a few mails ago how to do that from 8.1 to 8.2, and I
put that in a readme file for my studnets, so I guess that could help you
too. See below...


__ 
Barend Köbben
International Institute for Geo-information
Sciences and Earth Observation (ITC)
PO Box 6, 7500AA Enschede (The Netherlands)
ph: +31 (0)53 4874253; fax: +31 (0)53 4874335


Assuming that you have installed PostGIS on a new host <host_of_new_install>
and the old PostGIS on <host_of_old_install>, then:

1. 
a) Create a new PostGIS-enabled database of the same name under
   PostgreSQL 8.2.
b) Delete/drop the 'geometry_columns' table
c) Keep the new PostGIS installed table spatial_ref_sys (it might have
   updates coming from a PROJ4 update); Only if you  customized your
   spatial_ref_sys table in your old DB, delete the one in this new DB.

2.
a) Use pg_dump with the -Fc argument to create a custom format dump of your
   entire old database:
pg_dump -h <host_of_old_install> -U <username> -Fc -f myDB.backup -i -C
<myDB>


b) Use pg_restore with the -l option to create a text file containing a
   list of the contents of your dump file:
pg_restore -h <host_of_new_install> -U <username> -f toc.list -l myDB.backup

c) From this text file, remove everything that isn't a reference to any
   of your tables, indices or the geometry_columns table.
   If you did NOT have a customized spatial_ref_sys table (and therefore
   deleted it in 1c), make sure to also delete all references to that.

d) Restore your dump file into the new PostgreSQL 8.2 database using
   the -L option to specify a list of items to restore:
pg_restore -h <host_of_new_install> -U <username> -L toc.list -e -C -Fc -d
<myDB> myDB.backup