[postgis-users] Input on upgrade to GIS database

[Darren Houston]

Hello Onyx,

If like me and you don't have custom functions, schema, etc. and only have a 
couple of tables with data, I found the easiest way to do an upgrade is to 
dump the tables, upgrade your database, and load the tables. I also dump and 
load the geometry_columns table and then run fix_geometry_columns(). I always 
use a new spatial_ref_sys table from the upgrade. VACUUM ANALYZE and you 
should be good to go. I keep the old database kicking around just in case I 
need to start it up again to grab something I forgot. I hear 
postgis_restore.pl works, but I haven't used it.

Darren H.

On Thursday 17 November 2005 09:37, Sears, Jeremy wrote:
> Onyx,
>
> I have recently preformed a simmilar task. In the name of experimentation I
> had tried a coupla methods to move data from an older PostgreSQL(8.0.2) +
> PostGIS install to a new one (postgresql 8.1 and postgis 1.0.4). The older
> database dumped data into a sql loader file. This was then loaded into the
> new database via 'psql -f datafile.sql'. This method seems to have worked
> although I did notice a couple of errors fly by as the data loaded. . .
> Note that the new database is still in the testing phase and errors could
> exist that I havent noticed yet.. however so far so good.
>
> Jeremy
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Onyx
> Sent: November 17, 2005 11:01 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Input on upgrade to GIS database
>
>
>
> PostGIS Friends,
> I need some help/input on the proper approach to upgrading on GIS-driven
> database backend, and I am hoping that some of you have been in similar
> situations.  We run a PostgreSQL database with Proj, Geos, and (of course)
> PostGIS libraries.  I want to upgrade the following:
>
> - PostgreSQL 7.4.6      --> 8.1.0
> - Geos 2.1.0            --> 2.1.4
> - PostGIS 0.9.0         --> 1.0.4
>
> What is the proper approach to get this done successfully, keeping our
> existing data intact?  I know that it is not as simple as 1) dumping the
> schema & data from the database to a file, 2) moving/deleting PostgreSQL
> 7.4.6 (and old libraries), 3) installing PostgreSQL 8.1.0 (and the new
> libraries), and 4) feeding the schema & data back in, although I wish it
> were that easy.  I have read the section of the PostGIS manual on
> upgrading, but I am worried that there are some other aspects I need to
> know since I have other variables I am upgrading as well (PostgreSQL &
> Geos).  Has anyone been in my boat before?
>
> Any help would be greatly appreciated.  Thanks!
>
>
> - Onyx
> ---
> Onyx Mueller
> Software Engineer
> i-cubed : information integration & imaging LLC
> 201 Linden Street : Third Floor
> Fort Collins, CO  80524
> 970-482-4400 voice
> 970-482-4499 fax
>  <http://www.i3.com> http://www.i3.com