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

[Lee Hachadoorian]

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.

>> 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/