Re: pg_upgrade / postgis issue

On Wed, Jun 26, 2013 at 7:10 AM, Marcos Cano <mcano@stsa.info> wrote:
> so my problem is that i have pg8.3 + postgis 1.3.x and i want to upgrade both
> of em to pg9.2.4 + postgis 2.0.4.

I had this problem with 8.4->9.2 upgrade recently.

It is not documented, but postgis 1.5.8 is the only version, that is
compatible with all the pg versions from 8.3 to 9.2, so you could use
pg_upgrade with it.

So here is the solution:

1. on pg 8.3 you need to soft upgrade [1] postgis from 1.3.x to 1.5.8 first;
2. then pg_upgrade 8.3 to 9.2;
3. and then you will have to do hard upgrade [2] postgis from 1.5.8 to 2.0.4.

[1] http://postgis.net/docs/manual-2.0/postgis_installation.html#soft_upgrade
[2] http://postgis.net/docs/manual-2.0/postgis_installation.html#hard_upgrade

>
> so i've installed pg9.2.4 with postgis 2.0.4 and the server is running in a
> different port (5433) so both servers are up and running (not a big deal).
>
> when i try to pg_upgrade it gives me an error that pg_upgrade was expecting
> that some libraries provided by postgis extensions were installed so did a
> bit of research and i was missing this two "commands" from the postgis
> installation guide:
>
> CREATE EXTENSION postgis;
> CREATE EXTENSION postgis_topology;
>
> so everything went "well"  after that, my pg_upgrade was able to advance a
> little more, but suddenly it gives me this error
>
> New cluster database "postgres" is not empty
> Failure, exiting
>
> obviously i went to psql and saw that the database "postgres" was not empty
> and was filled with 3 relations :
>
>  Schema  |      Name       | Type  |  Owner
> ----------+-----------------+-------+----------
>  public   | spatial_ref_sys | table | postgres
>  topology | layer           | table | postgres
>  topology | topology        | table | postgres
>
>
> that were added by the 2 psql commands i just mention (that installed the
> extensions i was missing from the past)
>
> so im stuck here and i really need help or if someone could tell where to
> contact the postgis people.
>
>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/pg-upgrade-postgis-issue-tp5761138.html
> Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin



--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com