Re: pg_upgrade / postgis issue

(please, don't forget to put the list in CC when replying)

On Thu, Jun 27, 2013 at 9:40 AM, Marcos Cano <mcano@stsa.info> wrote:
> i got some errors in some databases like:
> psql:postgis_upgrade_13_to_15.sql:5259: ERROR:  current transaction is
> aborted, commands ignored until end of transaction block

You should look at the beginning of the error list. The error above
just shows that the current command will be ignored because some error
has happened earlier in the transaction.

> i guess these are not spatially enabled databases and i dont know if they
> use postgis at all, but in other it went smoothly
>
> so i guess i'll try the application is working fine
>
>
>
>
> On Thu, Jun 27, 2013 at 10:37 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
>>
>> 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
>
>



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