[postgis-users] postgis/postgres upgrade?

[Paragon Corporation]

Marcos,
 
Short answer. You can't use pg_upgrade to go from 8.3.2 to 9.2.4.  In order
to use pg_upgrade you need to have the same Minor version of PostGIS on both
installs (e.g 1.3 on both or 1.5 on both or 2.0 on both)
So if you are running PostGIS 1.3 on 8.3.2 you can't use pg_upgrade because
PostGIS 1.3 is not supported on 9.2.4.  9.2 requires PostGIS 2.0+
 
Also going from 1.3 to 2.0 requires a PostGIS hard-upgrade anyway because
the disk formats change and pg_upgrade doesn't do anything with PostGIS on
disk data format.
 
We wrote an article about this you might find useful
 
http://www.bostongis.com/blog/index.php?/archives/187-How-to-upgrade-your-da
tabase-to-PostGIS-2.0-let-me-count-the-ways.html
 
Hope that helps,
Leo and Regina
http://www.postgis.us
 

  _____  

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Marcos Cano
Sent: Thursday, June 20, 2013 2:54 PM
To: postgis-users at lists.osgeo.org
Cc: psql-admin at postgresql.org
Subject: [postgis-users] postgis/postgres upgrade?


hello im writting to let you know of an issue that i've beem dealing for
more than two weeks. so i just copy the main problem just to put you in
context



"so i'm trying to migrate/upgrade from 8.3.2 to 9.2.4 and following the
instructions of the documentation i've found myself in a major issue which
has overwhelmed my little knowledge. 
these were the steps i followed: 

1)downloand and install 9.2.4, with the contrib packages. 
2)init the 9.2.4 cluster in /usr/local/pgsql9.2.4/data, i also have the
8.3.2 cluster in /usr/local/pgsql/data 
3) stop both servers 
4)change the 9.2.4 port to 5433 
5) i run this command: 

/usr/local/pgsql9.2.4/bin/./pg_upgrade -b /usr/local/pgsql/bin/ -B
/usr/local/pgsql9.2.4/bin/ -c -d /usr/local/pgsql/data/ -D
/usr/local/pgsql9.2.4/data/ -p 5432 -P 5433 -v 


so after some 30 seconds the upgrade process seemed like "working"  but
after it crashed 
with this message: 
Checking for presence of required libraries                 fatal 

Your installation references loadable libraries that are missing from the 
new installation.  You can add these libraries to the new installation, 
or remove the functions using them from the old installation.  A list of 
problem libraries is in the file: 
    loadable_libraries.txt 

Failure, exiting 
"/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D "/usr/local/pgsql9.2.4/data/" -o ""
-m fast stop >> "pg_upgrade_server.log" 2>&1 


so i went to check the log and found: 


ERROR:  could not access file "$libdir/liblwgeom": No such file or directory

STATEMENT:  LOAD '$libdir/liblwgeom' 
command: "/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D
"/usr/local/pgsql9.2.4/data/" -o "" -m fast stop >> "pg_upgrade_server.log"
2>&1 
LOG:  received fast shutdown request 
LOG:  aborting any active transactions 
waiting for server to shut down....FATAL:  terminating connection due to
administrator command 
LOG:  could not send data to client: Broken pipe 
LOG:  shutting down 
LOG:  database system is shut down 
 done 
server stopped 


please help as i dont have any idea what's going wrong"




so Robert answered me what i was missing:

"Means that you have PostGIS installed and it can't find it in the upgrade
path I believe.  Did you get postGI configured correctly in the upgraded
version? "



so after looking and thinking i came up withe these question which is my
main concern:



i'm a little bit confused right now and i really need some help.. this is my
case: 
i have pg8.3.2 running with postgis 1.3.x, i'm willing to upgrade to pg9.2.4
using the pg_upgrade utility, now the problem is that apparently i need to
upgrade also to postgis 2.0.3 (so that's two upgrades in one) i read the
instruction in postgis webpage and i need to do a hard_upgrade... which is a
dump and restore. 

so i don't know how to proceed... install pg9.2.4 -> upgrade pg8.3.2 to pg
9.2.4-> then postgis??? or 

install pg9.2.4->install postgis 2.0.3 in the pg9.2.4 ->   (pg_upgrade of
the DB on pg8.3 to 9.2.4) OR hard_upgrade using the postgis scripts? 

thanks 


 so Robert came up with this intuitive and helpful answer:
Marcos, 
This answer might be best put over on the postGIS listserv, but I will
suggest a couple of strategies based on some differing assumptions and
certainties. 

One certainty - it seems that you absolutely have to have some form of
PostGIS installed in order to perform the postgreSQL upgrade using
pg_upgrade. 
One variable - whether or not you will have to do the "hard upgrade"
required by the postGIS version when doing the standard PostgreSQL upgrade.
In other words -- is the postgreSQL upgrade essentially doing the same as
the "hard upgrade"? 
   If YES - then install postgis 1.3.x on your 9.2.4 then use pg_upgrade,
then do the pgis hard upgrade 
   If NO - install postgis 2.x on your 9.2.4 then use pg_upgrade 

This is a very relevant case study, one that I myself will be going through
very very shortly here so I am interested to follow your progress and assist
in whatever way I might. 



But really i'm really stuck right now can you please give me some guidance
on what to do next? thank you very much
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130620/c583faeb/attachment.html>