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
<basi...@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@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.
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
