[postgis-users] postgres and postgis upgrade

Paragon Corporation lr at pcorp.us
Thu Jul 4 13:43:47 PDT 2013

In theory it shouldn't matter, but as far as dumping goes we usually use the
pg_dump that came with the version of PostgreSQL we are dumping.
So in this case pg_dump for 8.3 to guarantee it dumps everything allowed to
be dumped in that version.
and then restore with the pg_restore that comes with newer version we are
restoring to.  In this case pg_restore packaged with 9.2.4
Note when using postgis_restore.pl  make sure the right pg_restore is first
Not sure what platform you are on but FWIW this is how we do it under
Under Linux/Unix it would be much the same except Perl is always available
so no need for perl install and instead of using
set   you use export , ${...} instead of %variable% I think. (You probably
also want to move the version of PG first in path if you have multiple
installation in your path)
Hope that helps,
Leo and Regina


From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Marcos Cano
Sent: Thursday, July 04, 2013 10:08 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] postgres and postgis upgrade

what version of pg_dump should i use?... i tried the 8..3.2 and i think it
works, but trying the suggested one, wich is the latest (9.2.4) seems just
to not work properly because it does not dump my entire database (i assume
is because of the mismatch of postgis versions)

On Wed, Jul 3, 2013 at 12:00 PM, Paragon Corporation <lr at pcorp.us> wrote:

Yes (custom dump of 8.3.2 + pgis, create new postgis 2.0.4 in 9.2.4 and
restore backup) is the recommended way.  9.2.4 + 1.5.8 are borderline
compatible so I would avoid that mix and if your ultimate goal is to go to
2.0, 1.5.8 requires a hard upgrade anyway so not worth the hassle.


From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Marcos Cano
Sent: Wednesday, July 03, 2013 10:43 AM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] postgres and postgis upgrade

So I'm trying to upgrade Postgres and postgis.. My current versions are
8.3.2 and 1.3 respectively. And trying to upgrade to postgis 2.0.4 and
Postgres 9.2.4 

I've been trying a lot of options like:hard upgrade of postgis to 1.5.8 in
the Postgres 8.3 ( as I'm sure that version of postgis is compatible with
Postgres 8.3 and 9.2.4)
Then installing postgres 9.2.4 + postgis 1.5.8 and do a pg_upgrade and
finally do a hard upgrade of postgis to 2.0.4 in the postgres 9.2.4
installation. It  seems to work until an error happened during the

Your installation contains the "name" data type in user tables.  This data
type changed its internal alignment between your old and new  clusters so
this cluster cannot currently be upgraded.  You can remove the problem
tables and restart the upgrade.

So I tried another option but I don't know if this will work. Here's my

Do a custom dump of the DB in Postgres 8.3.2 + pgis 1.3 .

Install 9.2.4 with postgis 2.0.4
And do a restore with perl script included in the postgis binary folder
(perl utils/postgis_restore.pl)

do you think it will work?


postgis-users mailing list
postgis-users at lists.osgeo.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130704/85ef94f4/attachment.html>

More information about the postgis-users mailing list