Quick Links

Re: pg_upgrade / postgis issue

From:	Marcos Cano <mcano(at)stsa(dot)info>
To:	Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
Cc:	"pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject:	Re: pg_upgrade / postgis issue
Date:	2013-07-01 16:15:20
Message-ID:	CAP2Rsa+jtgJPH8gHXQc7gVgBF4Wv=G4jzUpUGByXR-EBz5eHpg@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-admin

*content of the file tables_using_names.txt:*
Database: dev
public.geography_columns.f_table_schema
public.geography_columns.f_table_name
public.geography_columns.f_geography_column

*
*
*

On Thu, Jun 27, 2013 at 2:25 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

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

In response to

Re: pg_upgrade / postgis issue at 2013-06-27 20:25:04 from Sergey Konoplev

Responses

Re: pg_upgrade / postgis issue at 2013-07-01 16:20:45 from Marcos Cano

Browse pgsql-admin by date

	From	Date	Subject
Next Message	Alvaro Herrera	2013-07-01 16:20:21	Re: How was I able to drop a role even though objects depend on it?
Previous Message	Wojciechowski, Robert (GE Transportation)	2013-07-01 14:20:03	How was I able to drop a role even though objects depend on it?