Re: pg_upgrade / postgis issue

On Thu, Jun 27, 2013 at 11:29 AM, Marcos Cano <mcano(at)stsa(dot)info> wrote:
> so after following the steps, and going with the pg_upgrade i get an issue
> with the content in the DB
>
> fatal
>
> 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. A list of the problem
> columns is in the file:
> tables_using_name.txt
>
>
> so going to that file it shows three DB that apparently use the "name" data
> type

Can you show the tables_using_name.txt content and definitions of
these tables, please? \d+ tablename in psql will be okay.

>
> so i dont know what to do next because im not a DB user nor developer, i'm
> the server admin
>
>
> On Thu, Jun 27, 2013 at 10:58 AM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
>>
>> (please, don't forget to put the list in CC when replying)
>>
>> On Thu, Jun 27, 2013 at 9:40 AM, Marcos Cano <mcano(at)stsa(dot)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(dot)ru(at)gmail(dot)com>
>> > wrote:
>> >>
>> >> On Thu, Jun 27, 2013 at 9:30 AM, Marcos Cano <mcano(at)stsa(dot)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(dot)ru(at)gmail(dot)com>
>> >> > wrote:
>> >> >>
>> >> >> On Thu, Jun 27, 2013 at 8:04 AM, Marcos Cano <mcano(at)stsa(dot)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(at)stsa(dot)info>
>> >> >> > wrote:
>> >> >> >>
>> >> >> >> wow thanks sergey... i will definitely try this try this
>> >> >> >>
>> >> >> >>
>> >> >> >> On Wed, Jun 26, 2013 at 1:09 PM, Sergey Konoplev
>> >> >> >> <gray(dot)ru(at)gmail(dot)com>
>> >> >> >> wrote:
>> >> >> >>>
>> >> >> >>> On Wed, Jun 26, 2013 at 7:10 AM, Marcos Cano <mcano(at)stsa(dot)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(at)postgresql(dot)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(dot)ru(at)gmail(dot)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(dot)ru(at)gmail(dot)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(dot)ru(at)gmail(dot)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(dot)ru(at)gmail(dot)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(dot)ru(at)gmail(dot)com