[postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
basille at ase-research.org
Thu Aug 23 08:32:49 PDT 2012
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
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
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
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!).
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.
> postgis-users mailing list
> postgis-users at postgis.refractions.net
Mathieu Basille, Post-Doc
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
``If you can't win by reason, go for volume.''
Calvin, by Bill Watterson.
More information about the postgis-users