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

[Mathieu Basille]

Le 28/06/2012 11:37, Lee Hachadoorian a écrit :
> On Thu, Jun 28, 2012 at 10:38 AM, Mathieu Basille
> <basille at 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.