[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
From:       Mathieu Basille <basille () ase-research ! org>
Date:       2012-08-23 15:32:49
Message-ID: 50364D21.1000100 () ase-research ! org
[Download RAW message or body]

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 =E9crit :
> On Thu, Jun 28, 2012 at 1:41 PM, Mathieu Basille
> <basille@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 a=
gain
>> at the end of the process (to have a clean DB). But I might be wrong her=
e.
>>
>> 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 /m=
ain
>> 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 ra=
ther
>> lose some time in the upgrade process than running critical operations w=
ith
>> 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'=C9cologie Comportementale et de Conservation de la Faune
+ Centre d'=C9tude de la For=EAt
D=E9partement de Biologie
Universit=E9 Laval, Qu=E9bec

~$ 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
[prev in list] [next in list] [prev in thread] [next in thread]