RE: Input on upgrade to GIS database

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

RE: Input on upgrade to GIS database

Sears, Jeremy
Input on upgrade to GIS database
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: [hidden email] [mailto:[hidden email]]On Behalf Of Onyx
Sent: November 17, 2005 11:01 AM
To: [hidden email]
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
 

Reply | Threaded
Open this post in threaded view
|

Re: Input on upgrade to GIS database

Darren Houston-3
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: [hidden email]
> [mailto:[hidden email]]On Behalf Of Onyx
> Sent: November 17, 2005 11:01 AM
> To: [hidden email]
> 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

Reply | Threaded
Open this post in threaded view
|

RE: Input on upgrade to GIS database

Onyx
Daniel Faivre, Jeremy Sears, Darren Houston, thank you for your input.  OK,
so, with your help, I have been able to bring up _most_ of our data
successfully in the new database.  But, I am having problems with the data
contained within the tables of type PostGIS geometry.

Originally, the table contained something like:

SRID=4326;MULTIPOLYGON(((-105.499999977881 40.5 0,-105.499999977881
40.9999999185093 0,-105.000000006985 40.9999999185093 0,-105.000000006985
40.5 0,-105.249999999884 40.5 0,-105.499999977881 40.5 0)))


Now, after the upgrade and xfer of data, the table contains:

01060000A0E61000000100000001030000800100000006000000FA3FE8FFFF5F5AC000000000
004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F444000000000000000000680
070000405AC0040051FFFF7F444000000000000000000680070000405AC00000000000404440
00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000000FA3FE8FFFF5F
5AC000000000004044400000000000000000


Any ideas why and how to fix?


- 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
 
 

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Darren Houston
> Sent: Thursday, November 17, 2005 12:20 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Input on upgrade to GIS database
>
> 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: [hidden email]
> > [mailto:[hidden email]]On Behalf Of
> > Onyx
> > Sent: November 17, 2005 11:01 AM
> > To: [hidden email]
> > 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
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



Reply | Threaded
Open this post in threaded view
|

RE: Input on upgrade to GIS database

Gregory S. Williamson
In reply to this post by Sears, Jeremy
Onyx --

This really ought to be FAQ -- the geometry is ok, it's just that postGIS has changed the way it displayes when you ask for the unadulterated contents of a geometry column.

Try using asEWKT(the_geom) and you'll see it displayed with the SRID; use WKT to see it without the SRID.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: [hidden email] on behalf of Onyx
Sent: Thu 11/17/2005 2:06 PM
To: 'PostGIS Users Discussion'
Cc:
Subject: RE: [postgis-users] Input on upgrade to GIS database
Daniel Faivre, Jeremy Sears, Darren Houston, thank you for your input.  OK,
so, with your help, I have been able to bring up _most_ of our data
successfully in the new database.  But, I am having problems with the data
contained within the tables of type PostGIS geometry.

Originally, the table contained something like:

SRID=4326;MULTIPOLYGON(((-105.499999977881 40.5 0,-105.499999977881
40.9999999185093 0,-105.000000006985 40.9999999185093 0,-105.000000006985
40.5 0,-105.249999999884 40.5 0,-105.499999977881 40.5 0)))


Now, after the upgrade and xfer of data, the table contains:

01060000A0E61000000100000001030000800100000006000000FA3FE8FFFF5F5AC000000000
004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F444000000000000000000680
070000405AC0040051FFFF7F444000000000000000000680070000405AC00000000000404440
00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000000FA3FE8FFFF5F
5AC000000000004044400000000000000000


Any ideas why and how to fix?


- 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
 
 

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Darren Houston
> Sent: Thursday, November 17, 2005 12:20 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Input on upgrade to GIS database
>
> 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: [hidden email]
> > [mailto:[hidden email]]On Behalf Of
> > Onyx
> > Sent: November 17, 2005 11:01 AM
> > To: [hidden email]
> > 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
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users

!DSPAM:437cf8f862501835115090!





Reply | Threaded
Open this post in threaded view
|

Re: Input on upgrade to GIS database

Dylan Keon-3
In reply to this post by Onyx
On 11/17/2005 02:06 PM, Onyx wrote:

> Daniel Faivre, Jeremy Sears, Darren Houston, thank you for your input.  OK,
> so, with your help, I have been able to bring up _most_ of our data
> successfully in the new database.  But, I am having problems with the data
> contained within the tables of type PostGIS geometry.
>
> Originally, the table contained something like:
>
> SRID=4326;MULTIPOLYGON(((-105.499999977881 40.5 0,-105.499999977881
> 40.9999999185093 0,-105.000000006985 40.9999999185093 0,-105.000000006985
> 40.5 0,-105.249999999884 40.5 0,-105.499999977881 40.5 0)))
>
>
> Now, after the upgrade and xfer of data, the table contains:
>
> 01060000A0E61000000100000001030000800100000006000000FA3FE8FFFF5F5AC000000000
> 004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F444000000000000000000680
> 070000405AC0040051FFFF7F444000000000000000000680070000405AC00000000000404440
> 00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000000FA3FE8FFFF5F
> 5AC000000000004044400000000000000000
>
>
> Any ideas why and how to fix?


Isn't this just due to the newer versions of PostGIS storing lightweight
geometries?  Try querying out your geometries in well-known text using
AsText():

SELECT AsText(the_geom) FROM foo WHERE id = 123;

--Dylan

Reply | Threaded
Open this post in threaded view
|

Re: Input on upgrade to GIS database

Darren Houston-3
In reply to this post by Onyx
Hi Onyx,

No fix necessary. PostGIS now stores spatial data as binary. Try selecting
that data out as text (select asewkt(the_geom)) and compare it to your old
data. Should match 100%.

Darren H.

On Thursday 17 November 2005 15:06, Onyx wrote:

> Daniel Faivre, Jeremy Sears, Darren Houston, thank you for your input.  OK,
> so, with your help, I have been able to bring up _most_ of our data
> successfully in the new database.  But, I am having problems with the data
> contained within the tables of type PostGIS geometry.
>
> Originally, the table contained something like:
>
> SRID=4326;MULTIPOLYGON(((-105.499999977881 40.5 0,-105.499999977881
> 40.9999999185093 0,-105.000000006985 40.9999999185093 0,-105.000000006985
> 40.5 0,-105.249999999884 40.5 0,-105.499999977881 40.5 0)))
>
>
> Now, after the upgrade and xfer of data, the table contains:
>
> 01060000A0E61000000100000001030000800100000006000000FA3FE8FFFF5F5AC00000000
>0
> 004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F44400000000000000000068
>0
> 070000405AC0040051FFFF7F444000000000000000000680070000405AC0000000000040444
>0
> 00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000000FA3FE8FFFF5
>F 5AC000000000004044400000000000000000
>
>
> Any ideas why and how to fix?
>
>
> - 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
>
> > -----Original Message-----
> > From: [hidden email]
> > [mailto:[hidden email]] On
> > Behalf Of Darren Houston
> > Sent: Thursday, November 17, 2005 12:20 PM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] Input on upgrade to GIS database
> >
> > 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: [hidden email]
> > > [mailto:[hidden email]]On Behalf Of
> > > Onyx
> > > Sent: November 17, 2005 11:01 AM
> > > To: [hidden email]
> > > 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
> >
> > _______________________________________________
> > postgis-users mailing list
> > [hidden email]
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

Reply | Threaded
Open this post in threaded view
|

Re: Input on upgrade to GIS database

strk-2
In reply to this post by Sears, Jeremy
On Thu, Nov 17, 2005 at 11:37:58AM -0500, 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.

Ehmm... I'm afraid those errors were important.
What does SELECT postgis_full_version() tell you ?
--strk;

>  
> Jeremy
>  
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]]On Behalf Of Onyx
> Sent: November 17, 2005 11:01 AM
> To: [hidden email]
> 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 
>  
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users


--

 /"\    ASCII Ribbon Campaign
 \ /    Respect for open standards
  X     No HTML/RTF in email
 / \    No M$ Word docs in email


Reply | Threaded
Open this post in threaded view
|

RE: Input on upgrade to GIS database

Sears, Jeremy
In reply to this post by Sears, Jeremy
Strk,
SELECT postgis_full_version()  gives me :
PostGIS 1.0.4, GEOS 2.1.4 Proj rel. 4.4.9 29 Oct 2004 Use Stats
DBProc="0.3.0" Relproc= 0.3.0.

I suspect the errors were due to the fact that the db that the dump came
from had written geometry functions in along with the data, when I loaded
the data into a new db that already had these functions, PostgreSQL put up
some fuss.

This is my working theory. As far as I can see all of my data are in the new
db. I can access it with out problem.

I'm very open to any suggestions you or anyone else may have regarding
methods by wich I can verify that my data are indeed in their proper order.
Im quite new to PostgreSQL and db's in general.

Best Regards
Jeremy


-----Original Message-----
From: [hidden email]
[mailto:[hidden email]]On Behalf Of
[hidden email]
Sent: November 17, 2005 5:56 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Input on upgrade to GIS database


On Thu, Nov 17, 2005 at 11:37:58AM -0500, 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.

Ehmm... I'm afraid those errors were important.
What does SELECT postgis_full_version() tell you ?
--strk;

>  
> Jeremy
>  
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]]On Behalf Of Onyx
> Sent: November 17, 2005 11:01 AM
> To: [hidden email]
> 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 
>  
>

> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users


--

 /"\    ASCII Ribbon Campaign
 \ /    Respect for open standards
  X     No HTML/RTF in email
 / \    No M$ Word docs in email

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users

Reply | Threaded
Open this post in threaded view
|

Re: Input on upgrade to GIS database

Markus Schaber
Hi, Jeremy,

Sears, Jeremy schrieb:

> I suspect the errors were due to the fact that the db that the dump came
> from had written geometry functions in along with the data, when I loaded
> the data into a new db that already had these functions, PostgreSQL put up
> some fuss.

So I'd think that your old dump has replaced some of the new definitions
with their old ones. You can try to replay the new lwpostgis.sql
(possibly with removed transaction encapsulation) and hope the best, but
I'd guarantee for nothing.

Markus

Reply | Threaded
Open this post in threaded view
|

RE: Input on upgrade to GIS database

Onyx
In reply to this post by Darren Houston-3
Gregory, Dylan, Darren, or anyone else,
So, is the postgis_restore.pl script converting the geometry data to binary?
Or is this done somewhere else?  Regardless, is there a way to avoid the
conversion, for legacy code reasons?  I would like to keep the geometries as
ASCII.

- 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
 
 

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Darren Houston
> Sent: Thursday, November 17, 2005 3:33 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Input on upgrade to GIS database
>
> Hi Onyx,
>
> No fix necessary. PostGIS now stores spatial data as binary.
> Try selecting that data out as text (select asewkt(the_geom))
> and compare it to your old data. Should match 100%.
>
> Darren H.
>
> On Thursday 17 November 2005 15:06, Onyx wrote:
> > Daniel Faivre, Jeremy Sears, Darren Houston, thank you for
> your input.  
> > OK, so, with your help, I have been able to bring up _most_ of our
> > data successfully in the new database.  But, I am having
> problems with
> > the data contained within the tables of type PostGIS geometry.
> >
> > Originally, the table contained something like:
> >
> > SRID=4326;MULTIPOLYGON(((-105.499999977881 40.5 0,-105.499999977881
> > 40.9999999185093 0,-105.000000006985 40.9999999185093
> > 0,-105.000000006985
> > 40.5 0,-105.249999999884 40.5 0,-105.499999977881 40.5 0)))
> >
> >
> > Now, after the upgrade and xfer of data, the table contains:
> >
> >
> >01060000A0E61000000100000001030000800100000006000000FA3FE8FFF
> F5F5AC0000
> >0000
> >0
> >
> >004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F444000000
> 0000000000
> >0068
> >0
> >
> >070000405AC0040051FFFF7F444000000000000000000680070000405AC00
> 0000000004
> >0444
> >0
> >
> >00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000
> 000FA3FE8F
> >FFF5
> >F 5AC000000000004044400000000000000000
> >
> >
> > Any ideas why and how to fix?
> >
> >
> > - 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
> >
> > > -----Original Message-----
> > > From: [hidden email]
> > > [mailto:[hidden email]] On
> Behalf Of
> > > Darren Houston
> > > Sent: Thursday, November 17, 2005 12:20 PM
> > > To: PostGIS Users Discussion
> > > Subject: Re: [postgis-users] Input on upgrade to GIS database
> > >
> > > 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: [hidden email]
> > > >
> [mailto:[hidden email]]On Behalf Of
> > > > Onyx
> > > > Sent: November 17, 2005 11:01 AM
> > > > To: [hidden email]
> > > > 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
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > [hidden email]
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > [hidden email]
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



Reply | Threaded
Open this post in threaded view
|

Re: Input on upgrade to GIS database

Markus Schaber
Hi, Onyx,

Onyx schrieb:
> Gregory, Dylan, Darren, or anyone else,
> So, is the postgis_restore.pl script converting the geometry data to binary?
> Or is this done somewhere else?  Regardless, is there a way to avoid the
> conversion, for legacy code reasons?  I would like to keep the geometries as
> ASCII.

It is the internal storage format as well as the so-called canonical
representation that has changed between postgis 0.x and 1.x. This
representation was not meant to be processed by client software
directly, apart from database interface drivers.

If you need the text representation, you can use the OpenGIS compliant
asText() and getSRID() functions which are not only immune to postgis
internal changes, but also compatible with other OpenGIS compliant
databases, as well as the PostGIS extended asEWKT().

Another way is to use the adaptor interfaces for your programming
language (like the pgjdbc extension delivered with postgis or the others
e. G. for python and php announced here on the list) - they abstract
over the database internal representations.

If you really need to run an old, unchangeable application against a new
PostGIS database, you can rename all your GIS tables and replace them by
views that provide the translated data, via the asEWKT() function. If
your application needs write access, you have to create the appropriate
ON ... DO INSTEAD rules for the view, and it may be necessary to
explicitly expose the legacy OID column in the view.

Markus