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

[Mathieu Basille]

Dear list,

I need to proceed with a sensitive (and unfortunate) migration of the 
computer which currently runs PostGIS from Debian to Windows. I figured out 
I could as well upgrade PostGIS and PostgreSQL in the process.

Current versions are:
- Debian Squeeze
- PostgreSQL 8.4
- PostGIS 2.0 SVN r6066 from 13 October 2010 (long before the extension 
mechanism was implemented)

Target versions thus are:
- Windows 7
- PostgreSQL 9.1
- PostGIS 2.0.1

Did anyone already go through such a migration? From what I understand, I 
need to run a hard upgrade using pg_dump (or pg_dumpall) from PostgreSQL 
9.1 first. I will then get a .sql file which contains the whole DB. I can 
then save it, install Windows 7 + PostgreSQL 9.1 + PostGIS 2.0.1, and use 
it again to restore the DB in the new PostgreSQL/PostGIS.

The tricky part is that the hard drive will be totally erased in the 
process, so that I won't have the two installations in parallel (which 
would have allowed me to do some harmless tests). Is this safer then to 
first install PostgreSQL 9.1 and PostGIS 2.0.1 on the Debian system? I 
should be able to run the hard upgrade then, and thus safely check that 
everything is fine before wiping out the hard drive. The sequence would 
thus be:

First on Debian:
- Install PostgreSQL 9.1
- Back-up the DB using pg_dump/pg_dumpall from PostgreSQL 9.1 (and store it 
on an external hard drive)
- Install PostGIS 2.0.1
- Create a new spatially enabled database
- Do the hard upgrade using postgis_restore.pl (I'm not too sure about 
legacy.sql... Should I load it too?)
- Check if the database loads and runs correctly
- If it is successful, then back-up the DB again using pg_dump/pg_dumpall.

And after the fresh install of Windows is ready:
- Install PostgreSQL 9.1
- Install PostGIS 2.0.1
- Create a new spatially enabled database
- Simply restore the DB using psql

I found detailed explanations here for PostgreSQL [1] and PostGIS [2]. Is 
there anything I'm missing?

Thank you for any comment on the procedure!
Sincerely,
Mathieu Basille.


[1] http://www.postgresql.org/docs/9.1/static/upgrading.html

[2] 
http://postgis.refractions.net/documentation/manual-2.0/postgis_installation.html#upgrading


-- 

~$ 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.