Re: pg_upgrade / postgis issue - Mailing list pgsql-admin

On Thu, Jun 27, 2013 at 9:30 AM, Marcos Cano <mcano@stsa.info> wrote:
> just one last question how do i know if after doing a soft_upgrade (from 1.3
> to 1.58) everything went ok?

No errors during upgrade plus test your geo features afterwards.

>
>
> On Thu, Jun 27, 2013 at 10:13 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
>>
>> On Thu, Jun 27, 2013 at 8:04 AM, Marcos Cano <mcano@stsa.info> wrote:
>> > now one concern i've always had with all the methods that needed a
>> > "spatially enabled database" is"
>> >
>> > apparently i need to create a newDB (with the spatially enabled....) but
>> > what if i dont want to create a new one, but to retain the old ones with
>> > the
>> > same name and everything?
>>
>> You can rename olddb to _olddb and postgis_restore to olddb. Or if you
>> do not have enough space just drop olddb after you pg_dump-ed it.
>>
>> >
>> >
>> > On Thu, Jun 27, 2013 at 8:47 AM, Marcos Cano <mcano@stsa.info> wrote:
>> >>
>> >> wow thanks sergey... i will definitely try this try this
>> >>
>> >>
>> >> On Wed, Jun 26, 2013 at 1:09 PM, Sergey Konoplev <gray.ru@gmail.com>
>> >> wrote:
>> >>>
>> >>> 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
>> >>
>> >>
>> >
>>
>>
>>
>> --
>> 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
>
>



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