Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
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
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote: 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. You'll need to invoke pg_dump using the -Fc (custom format) switch, which gives you not an SQL but an indexed dump, needed for skipping the parts you won't want to restore. postgis_restore.pl will take care of the restore (and skipping). And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.1 | / 2.1 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
Le 28/06/2012 10:32, Sandro Santilli a écrit : On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote: 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. You'll need to invoke pg_dump using the -Fc (custom format) switch, which gives you not an SQL but an indexed dump, needed for skipping the parts you won't want to restore. postgis_restore.pl will take care of the restore (and skipping). Dear Sandro, Thanks for the additional information. I didn't understand the specifics of the -Fc switch (maybe something to add in the doc?). It seems I don't have to worry too much with the PostgreSQL/PostGIS upgrade. And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... This is actually my main concern... I'm very familiar and happy with Debian, and not at all with Windows... Anyway, the migration is not my call! I will thus proceed with the upgrade first on Debian, and later switch to Windows. I'll let you know how it goes! Thanks, Mathieu --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.1 | / 2.1 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
On Thu, Jun 28, 2012 at 10:38 AM, Mathieu Basille basi...@ase-research.org wrote: Le 28/06/2012 10:32, Sandro Santilli a écrit : On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote: Dear list, I need to proceed with a sensitive (and unfortunate) migration of the computer which currently runs PostGIS from Debian to Windows. I You'll need to invoke pg_dump using the -Fc (custom format) switch, which gives you not an SQL but an indexed dump, needed for skipping the parts you won't want to restore. postgis_restore.pl will take care of the restore (and skipping). Dear Sandro, Thanks for the additional information. I didn't understand the specifics of the -Fc switch (maybe something to add in the doc?). It seems I don't have to worry too much with the PostgreSQL/PostGIS upgrade. Manthieu, -Fc specifies custom format, which is basically Postgres' own dump format. It is smaller and faster to dump/restore than plain text. It also allows you at restore to selectively choose which tables or schemas you want to restore. But it can only restore to a Postgres database. Plain text, which builds the restore as SQL CREATE and INSERT statements, is slower, but can often be restored to non- Postgres databases. See http://www.postgresql.org/docs/8.4/static/app-pgdump.html. And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... This is actually my main concern... I'm very familiar and happy with Debian, and not at all with Windows... Anyway, the migration is not my call! I will thus proceed with the upgrade first on Debian, and later switch to Windows. pg_dump is designed to restore between different Postgres platforms and from lower to higher versions. I have not done a Linux→Windows migration, but I have done a Windows→Linux migration, and there were no problems or incompatibilities in switching OSes. I don't see the point of restoring to a machine you are going to wipe anyway, and since the database structure and content won't have changed (even though the version has), I believe you will end up with exactly the same dump file. If you wanted to be paranoid about it, rather than restoring to the machine you will wipe, maybe you have some other old piece of equipment running Debian (or that you can install Debian on) that you can restore to temporarily. Obviously, you wouldn't want to have thousands of users hitting it, but Postgres/PostGIS runs quite well on desktop-class hardware. This would also preserve access to your data while you are setting up Windows, in case something goes wrong or it takes longer than you expect. --Lee -- Lee Hachadoorian PhD, Earth Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
Le 28/06/2012 11:37, Lee Hachadoorian a écrit : On Thu, Jun 28, 2012 at 10:38 AM, Mathieu Basille basi...@ase-research.org wrote: Le 28/06/2012 10:32, Sandro Santilli a écrit : On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote: Dear list, I need to proceed with a sensitive (and unfortunate) migration of the computer which currently runs PostGIS from Debian to Windows. I You'll need to invoke pg_dump using the -Fc (custom format) switch, which gives you not an SQL but an indexed dump, needed for skipping the parts you won't want to restore. postgis_restore.pl will take care of the restore (and skipping). Dear Sandro, Thanks for the additional information. I didn't understand the specifics of the -Fc switch (maybe something to add in the doc?). It seems I don't have to worry too much with the PostgreSQL/PostGIS upgrade. Manthieu, -Fc specifies custom format, which is basically Postgres' own dump format. It is smaller and faster to dump/restore than plain text. It also allows you at restore to selectively choose which tables or schemas you want to restore. But it can only restore to a Postgres database. Plain text, which builds the restore as SQL CREATE and INSERT statements, is slower, but can often be restored to non- Postgres databases. See http://www.postgresql.org/docs/8.4/static/app-pgdump.html. I see, thx for the explanation! For a PostGIS DB, I understand why this is the preferred format (there's not real advantage of restoring a PostGIS DB in a non-PostgreSQL DB...). I expected the PostGIS doc to mention it, but should have indeed looked at the PostgreSQL doc first! And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... This is actually my main concern... I'm very familiar and happy with Debian, and not at all with Windows... Anyway, the migration is not my call! I will thus proceed with the upgrade first on Debian, and later switch to Windows. pg_dump is designed to restore between different Postgres platforms and from lower to higher versions. I have not done a Linux→Windows migration, but I have done a Windows→Linux migration, and there were no problems or incompatibilities in switching OSes. I don't see the point of restoring to a machine you are going to wipe anyway, and since the database structure and content won't have changed (even though the version has), I believe you will end up with exactly the same dump file. Dear Lee, 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). If you wanted to be paranoid about it, rather than restoring to the machine you will wipe, maybe you have some other old piece of equipment running Debian (or that you can install Debian on) that you can restore to temporarily. Obviously, you wouldn't want to have thousands of users hitting it, but Postgres/PostGIS runs quite well on desktop-class hardware. This would also preserve access to your data while you are setting up Windows, in case something goes wrong or it takes longer than you expect. This is actually a good idea! I'm currently running Debian on my own laptop, which could serve as a basis for this upgrade (dump from the server, upgrade on my computer). I'll need to check memory requirements though (I might be short of space on my hard drive). I'll let you know how all of this work! Thanks again, Mathieu. --Lee -- ~$ 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
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
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
