Thread: upgrading from postgresql 8.4 - postgis 1.3.6 to postgresql 9.2/postgis2.0

upgrading from postgresql 8.4 - postgis 1.3.6 to postgresql 9.2/postgis2.0

From

Athanasios Kostopoulos

Date:

06 September 2013, 12:38:43

Dear list,

first of all thanks for the direct and indirect help all this time. I now have a problem that proves to be quite the brain-teaser (at least for me).

I have an ancient database (8.4.X) integrated with postGIS 1.3.6. Clearly, this while currently working will reach end of life soon and needs to be upgraded.

On a Debian wheezy system, I installed postgres 9.2/postgis2.0 - all from the apt repositories provided by postgres. When I try to do things the recommended way from postGIS (processing the backup with their perl script) during restore I get a few errors and constantly increasing memory usage, until the restore process will die with an "out of memory error". When I try to restore the "pure" dump, I get a number of errors and an unusuable database at the end.

Now, I understand that I am talking a big increase in major versions, including some non backwards compatible changes in postgis but such an upgrade should be feasible. Can someone give me a small push, so I can move forward with this? I would prefer to upgrade to the latest versions of postgresql and postGIS, if such a thing is possible.

Thanks in advance for any replies.

Amtsgericht Charlottenburg HRB 111815 B | USt.Id.Nr: DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.

Re: upgrading from postgresql 8.4 - postgis 1.3.6 to postgresql 9.2/postgis2.0

From

Tom Lane

Date:

06 September 2013, 17:03:03

Athanasios Kostopoulos <athanasios.kostopoulos@classmarkets.com> writes:
> I have an ancient database (8.4.X) integrated with postGIS 1.3.6. Clearly,
> this while currently working will reach end of life soon and needs to be
> upgraded.

> On a Debian wheezy system, I installed postgres 9.2/postgis2.0 - all from
> the apt repositories provided by postgres. When I try to do things the
> recommended way from postGIS (processing the backup with their perl script)
> during restore I get a few errors and constantly increasing memory usage,
> until the restore process will die with an "out of memory error". When I
> try to restore the "pure" dump, I get a number of errors and an unusuable
> database at the end.

You'd probably be best off to ask about this on the postgis mailing lists;
I'm not sure there are any experts about postgis reading this list.

Having said that, I think one of the main hurdles you're dealing with is
that postgis should now be installed as an "extension" not just a bunch
of loose database objects.  What I'd try is doing "create extension
postgis" in the empty 9.2 database and then loading the 8.4 dump file.
You *will* get a bunch of object-already-exists errors with this approach,
and you'll need to read through them all to see if anything is really
wrong or it's just that the already-installed extension kept the old
postgis objects from being reinstalled (which is what you want).
The process the postgis docs recommend is probably smoother when it works,
but it sounds like it may not cope well with a three-major-version jump.

            regards, tom lane