[postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS

[Mathieu Basille]

Sorry for the late update, I was just too busy to report. As far as I can 
tell, the migration succeeded, and it went actually without any pain! Here 
is how I did with more details:

1) Backup with the version of PostGIS installed on the Debian workstation 
(SVN r6066 from 13 October 2010) using the command:

pg_dump -h localhost -p 5432 -U [user] -Fc -b -v -N public -f 
"/home/postgres/db.bkp" [db]

I explicitely removed the schema public for 2 reasons, first we used it as 
a temporary testing schema (i.e. none of the tables here were to be saved), 
and second I think it simplifies the migration since the old functions 
won't be saved either.

2) I then tested the backup on my own computer, to make sure I could use 
it. First list the tables:

pg_restore -l db.bkp

Then restore the DB:

pg_restore -h localhost -p 5432 -U [user] -v -1 -d [db] db.bkp

Success!

3) Format the workstation, install Windows with the last stable version of 
PostgreSQL + PostGIS, and finally restore the DB with the same command as 
above. Once again, everything was very smooth, except that I had to 
manually add raster constraints in raster_columns (I think that it appeared 
after the revision I was still using) using a command like:

SELECT AddRasterConstraints('m'::name, 'raster'::name, 'rast'::name);

(I did it for every raster using a very brutal approach based on the list 
of rasters given by 'SELECT * FROM raster_columns;' and edited in a text 
editor... There was probably a simpler SQL approach, but it did the trick)

Finally, I ran a ANALYSE after the restore. And since then, everything 
seems perfect!

Thanks a lot to all people who contributed to this success (this goes to 
the PostGIS dev as well as to Sandro and Lee who answered in this thread!).
Mathieu.



Le 28/06/2012 14:26, Lee Hachadoorian a écrit :
> On Thu, Jun 28, 2012 at 1:41 PM, Mathieu Basille
> <basille at ase-research.org>  wrote:
>>
>> I wonder whether the postgis_restore script would not alter the DB,
>> especially since I still use an old PostGIS version where raster_columns was
>> not yet a view... This is why it made sense to me to dump the whole DB again
>> at the end of the process (to have a clean DB). But I might be wrong here.
>>
>> The main reason about starting the upgrade with Debian first is that I
>> perfectly know how to restore the system (PostgreSQL/PostGIS + DB) in its
>> current state with Debian. Which means that I can break it all, I will be
>> able to come up with a functional system anyway (from a backup of the /main
>> directory). I understand your point of view about switching OSes though.
>> Still, I'm so much more familiar with Debian vs. Windows that I would rather
>> lose some time in the upgrade process than running critical operations with
>> Windows (i.e. upgrading PostGIS).
>
>
> Ah, so the idea is that you need to accommodate changes in how PostGIS
> stores things and feel more comfortable using Debian for that part of
> the process. Makes sense. Need to do that myself to move from 1.5 to
> 2.0. Still think it makes sense to do the restore to a separate Debian
> machine (which it sounds like you agree with) that can serve as an
> fallback if the Windows install is problematic.
>
> --Lee
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

~$ whoami
Mathieu Basille, Post-Doc

~$ locate
Laboratoire d'Écologie Comportementale et de Conservation de la Faune
+ Centre d'Étude de la Forêt
Département de Biologie
Université Laval, Québec

~$ info
http://ase-research.org/basille

~$ fortune
``If you can't win by reason, go for volume.''
Calvin, by Bill Watterson.