Обсуждение: 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

On Thu, Jun 27, 2013 at 11:29 AM, Marcos Cano <mcano@stsa.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.ru@gmail.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@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
>
>



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

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

2nd question: don't actually if what im running is what you are expecting, is a really long long list of relations like this:

but i did
psql -d dev

dev=# \d

                      List of relations
 Schema |             Name              |   Type   |  Owner  
--------+-------------------------------+----------+----------
 public | ad_id_seq                     | sequence | jp
 public | agosto_2012                   | table    | jp
 public | agosto_2012_resumen           | table    | jp
 public | al_10_geomid_seq              | sequence | jp
 public | al_10_point                   | table    | jp
 public | al_10_trip_point_seq          | sequence | jp
 public | al_11_geomid_seq              | sequence | jp
 public | al_11_point                   | table    | jp
 public | al_11_trip_point_seq          | sequence | jp
 public | al_12_geomid_seq              | sequence | jp
 public | al_12_point                   | table    | jp
 public | al_12_trip_point_seq          | sequence | jp
 public | al_13_geomid_seq              | sequence | jp
 public | al_13_point                   | table    | jp
 public | al_13_trip_point_seq          | sequence | jp
 public | al_14_geomid_seq              | sequence | jp
 public | al_14_point                   | table    | jp
 public | al_14_trip_point_seq          | sequence | jp
 public | al_15_geomid_seq              | sequence | jp
 public | al_15_point                   | table    | jp
 public | al_15_trip_point_seq          | sequence | jp
 public | al_16_geomid_seq              | sequence | jp
 public | al_16_point                   | table    | jp
 public | al_16_trip_point_seq          | sequence | jp
 public | al_17_geomid_seq              | sequence | jp
 public | al_17_point                   | table    | jp
 public | al_17_trip_point_seq          | sequence | jp
 public | al_1_geomid_seq               | sequence | jp
 public | al_1_point                    | table    | jp
 public | al_1_trip_point_seq           | sequence | jp
 public | al_21_geomid_seq              | sequence | jp
 public | al_21_point                   | table    | jp
 public | al_21_trip_point_seq          | sequence | jp
 public | al_26_geomid_seq              | sequence | jp
 public | al_26_point                   | table    | jp
 public | al_26_trip_point_seq          | sequence | jp
 public | al_27_geomid_seq              | sequence | jp
 public | al_27_point                   | table    | jp
 public | al_27_trip_point_seq          | sequence | jp
 public | al_28_geomid_seq              | sequence | jp
 public | al_28_point                   | table    | jp
 public | al_28_trip_point_seq          | sequence | jp
 public | al_29_geomid_seq              | sequence | jp
 public | al_29_point                   | table    | jp
 public | al_29_trip_point_seq          | sequence | jp
 public | al_2_geomid_seq               | sequence | jp
 public | al_2_point                    | table    | jp
 public | al_2_trip_point_seq           | sequence | jp
 public | al_30_geomid_seq              | sequence | jp
 public | al_30_point                   | table    | jp
 public | al_30_trip_point_seq          | sequence | jp
 public | al_31_geomid_seq              | sequence | jp
 public | al_31_point                   | table    | jp
 public | al_31_trip_point_seq          | sequence | jp
 public | al_3_geomid_seq               | sequence | jp
 public | al_3_point                    | table    | jp
 public | al_3_trip_point_seq           | sequence | jp
 public | al_4_geomid_seq               | sequence | jp
 public | al_4_point                    | table    | jp
 public | al_4_trip_point_seq           | sequence | jp
 public | al_5_geomid_seq               | sequence | jp
 public | al_5_point                    | table    | jp
 public | al_5_trip_point_seq           | sequence | jp
 public | al_6_geomid_seq               | sequence | jp
 public | al_6_point                    | table    | jp
 public | al_6_trip_point_seq           | sequence | jp
 public | al_7_geomid_seq               | sequence | jp
 public | al_7_point                    | table    | jp
 public | al_7_trip_point_seq           | sequence | jp
 public | al_8_geomid_seq               | sequence | jp
 public | al_8_point                    | table    | jp
 public | al_8_trip_point_seq           | sequence | jp
 public | al_9_geomid_seq               | sequence | jp
 public | al_9_point                    | table    | jp
 public | al_9_trip_point_seq           | sequence | jp
 public | al_account                    | table    | jp
 public | al_account_prefs              | table    | jp
 public | al_accountid_seq              | sequence | jp
 public | al_activacionclaro            | table    | jp
 public | al_activacionclaro_seq        | sequence | jp
 public | al_adcreading                 | table    | jp
 public | al_adcreading_seq             | sequence | jp
 public | al_alert_archive              | table    | jp
 public | al_alert_comment              | table    | jp
 public | al_alertcomment_seq           | sequence | jp
 public | al_area                       | table    | jp
 public | al_area_seq                   | sequence | jp
 public | al_bankaccount                | table    | jp
 public | al_bankaccountid_seq          | sequence | jp
 public | al_billing_engine             | table    | jp
 public | al_billingengineid_seq        | sequence | jp
 public | al_cannedmsg                  | table    | jp
 public | al_cannedmsg_seq              | sequence | jp
 public | al_cellular_plan              | table    | jp
 public | al_cellular_plan_id_seq       | sequence | jp
 public | al_cellular_provider          | table    | jp
 public | al_cellular_provider_id_seq   | sequence | jp
 public | al_chat                       | table    | jp
 public | al_chat_seq                   | sequence | jp
 public | al_client                     | table    | jp
 public | al_client_checkpointid_pool   | table    | jp
 public | al_clientid_seq               | sequence | jp
 public | al_comloss                    | table    | jp
 public | al_comlossid_seq              | sequence | jp
 public | al_crew                       | table    | jp
 public | al_crew_seq                   | sequence | jp
 public | al_currency                   | table    | jp
 public | al_custom_map                 | table    | jp
 public | al_custom_map_seq             | sequence | jp
 public | al_customeventname            | table    | jp
 public | al_customeventname_seq        | sequence | jp
 public | al_d_sig                      | table    | jp
 public | al_daemons                    | table    | jp
 public | al_dates                      | table    | jp
 public | al_driver                     | table    | jp
 public | al_driverid_seq               | sequence | jp
 public | al_event                      | table    | jp
 public | al_event_seq                  | sequence | jp
 public | al_expense                    | table    | jp
 public | al_expenseid_seq              | sequence | jp
 public | al_facturaid_seq              | sequence | jp
 public | al_fuelreading                | table    | jp
 public | al_fuelreading_seq            | sequence | jp
 public | al_gadm                       | table    | jp
 public | al_gadm_seq                   | sequence | jp
 public | al_gprscontact_q              | table    | jp
 public | al_gprscontactq_seq           | sequence | jp
 public | al_gprseventq_seq             | sequence | jp
 public | al_group                      | table    | jp
 public | al_group_permission           | table    | jp
 public | al_groupid_seq                | sequence | jp
 public | al_help                       | table    | jp
 public | al_helpid_seq                 | sequence | jp
 public | al_installerid_seq            | sequence | jp
 public | al_inventory_item             | table    | jp
 public | al_inventory_item_seq         | sequence | jp
 public | al_inventory_movement_in      | table    | jp
 public | al_inventory_movement_in_out  | table    | jp
 public | al_inventory_movement_out     | table    | jp
 public | al_inventory_order            | table    | jp
 public | al_inventory_order_seq        | sequence | jp
 public | al_invoice                    | table    | jp
 public | al_invoice_item               | table    | jp
 public | al_invoice_payment            | table    | jp
 public | al_invoiceid_seq              | sequence | jp
 public | al_invoiceitem_seq            | sequence | jp
 public | al_invoiceitemid_seq          | sequence | jp
 public | al_lag_test                   | table    | jp
 public | al_lag_testid_seq             | sequence | jp
 public | al_landmark_seq               | sequence | jp
 public | al_log                        | table    | jp
 public | al_logid_seq                  | sequence | jp
 public | al_loginmessage               | table    | jp
 public | al_loginmessage_seq           | sequence | jp
 public | al_maintinid_seq              | sequence | jp
 public | al_maintoutid_seq             | sequence | jp
 public | al_me_delivery                | table    | jp
 public | al_me_delivery_seq            | sequence | jp
 public | al_namedplace                 | table    | jp
 public | al_namedplace_seq             | sequence | jp
 public | al_notification_q             | table    | jp
 public | al_notification_q_seq         | sequence | jp
 public | al_payment                    | table    | jp
 public | al_paymentid_seq              | sequence | jp
 public | al_percreading                | table    | jp
 public | al_percreading_seq            | sequence | jp
 public | al_permission                 | table    | jp
 public | al_permissionid_seq           | sequence | jp
 public | al_phoneline                  | table    | jp
 public | al_phoneline_bill             | table    | jp
 public | al_phoneline_bill_id_seq      | sequence | jp
 public | al_phoneline_id_seq           | sequence | jp
 public | al_photo                      | table    | jp
 public | al_photo_seq                  | sequence | jp
 public | al_place                      | table    | jp
 public | al_place_seq                  | sequence | jp
 public | al_presscene                  | table    | jp
 public | al_presscene_seq              | sequence | jp
 public | al_print_q                    | table    | jp
 public | al_printq_seq                 | sequence | jp
 public | al_provider_id                | table    | jp
 public | al_provider_seq               | sequence | jp
 public | al_reason_translation         | table    | jp
 public | al_reason_translator          | table    | jp
 public | al_reasontranslation_seq      | sequence | jp
 public | al_reasontranslator_seq       | sequence | jp
 public | al_recover                    | table    | jp
 public | al_refpointvisit              | table    | jp
 public | al_refpointvisitid_seq        | sequence | jp
 public | al_refuel                     | table    | jp
 public | al_refuel_seq                 | sequence | jp
 public | al_refund                     | table    | jp
 public | al_refundid_seq               | sequence | jp
 public | al_repgroup                   | table    | jp
 public | al_repgroup_seq               | sequence | jp
 public | al_report_schedule            | table    | jp
 public | al_reportscheduleid_seq       | sequence | jp
 public | al_route                      | table    | jp
 public | al_route_bak                  | table    | jp
 public | al_route_point                | table    | jp
 public | al_route_point_bak            | table    | jp
 public | al_routeid_seq                | sequence | jp
 public | al_routepointid_seq           | sequence | jp
 public | al_rule_engine_q_seq          | sequence | jp
 public | al_script                     | table    | jp
 public | al_scriptid_seq               | sequence | jp
 public | al_service_contract           | table    | jp
 public | al_serviceid_seq              | sequence | jp
 public | al_setting                    | table    | jp
 public | al_shared_place               | table    | jp
 public | al_simcard                    | table    | jp
 public | al_simcard_id_seq             | sequence | jp
 public | al_sms_in_q                   | table    | jp
 public | al_sms_in_q_seq               | sequence | jp
 public | al_sms_out_q                  | table    | jp
 public | al_sms_out_q_seq              | sequence | jp
 public | al_special_charge             | table    | jp
 public | al_special_chargeid_seq       | sequence | jp
 public | al_stat_allunits              | table    | jp
 public | al_stat_day                   | table    | jp
 public | al_stat_unitday               | table    | jp
 public | al_statallunits_seq           | sequence | jp
 public | al_statday_seq                | sequence | jp
 public | al_statunitday_seq            | sequence | jp
 public | al_stop                       | table    | jp
 public | al_stopid_seq                 | sequence | jp
 public | al_street                     | table    | jp
 public | al_street_2                   | table    | jp
 public | al_street_2_gid_seq           | sequence | jp
 public | al_street_pnc                 | table    | jp
 public | al_street_seq                 | sequence | jp
 public | al_tacasa                     | table    | jp
 public | al_tacasa_seq                 | sequence | jp
 public | al_tacasani                   | table    | jp
 public | al_tag                        | table    | jp
 public | al_tag_seq                    | sequence | jp
 public | al_temperature                | table    | jp
 public | al_temperature_seq            | sequence | jp
 public | al_tmp                        | table    | jp
 public | al_trace                      | table    | jp
 public | al_traceid_seq                | sequence | jp
 public | al_tramite                    | table    | jp
 public | al_tramite_id_seq             | sequence | jp
 public | al_trip                       | table    | jp
 public | al_trip_interp                | table    | jp
 public | al_tripgeom                   | table    | jp
 public | al_tripid_seq                 | sequence | jp
 public | al_trouble_ticket             | table    | jp
 public | al_trouble_ticket_comment     | table    | jp
 public | al_trouble_ticket_comment_seq | sequence | jp
 public | al_trouble_ticket_seq         | sequence | jp
 public | al_udp_q                      | table    | jp
 public | al_udpid_seq                  | sequence | jp
 public | al_unit                       | table    | jp
 public | al_unit_config                | table    | jp
 public | al_unit_config_seq            | sequence | jp
 public | al_unit_contactor_q           | table    | jp
 public | al_unit_contactor_q_seq       | sequence | jp
 public | al_unit_contacttimer          | table    | jp
 public | al_unit_contacttimer_seq      | sequence | jp
 public | al_unit_gprsevent             | table    | jp
 public | al_unit_install               | table    | jp
 public | al_unit_install_seq           | sequence | jp
 public | al_unit_sensor                | table    | jp
 public | al_unit_status_msg            | table    | jp
 public | al_unit_status_msg_seq        | sequence | jp
 public | al_unit_tmp                   | table    | jp
 public | al_unit_tmp_seq               | sequence | jp
 public | al_unitcommand_q              | table    | jp
 public | al_unitcommand_seq            | sequence | jp
 public | al_unitcommandq_seq           | sequence | jp
 public | al_unitid_seq                 | sequence | jp
 public | al_unitsensor_seq             | sequence | jp
 public | al_user                       | table    | jp
 public | al_user_group                 | table    | jp
 public | al_user_login                 | table    | jp
 public | al_user_loginid_seq           | sequence | jp
 public | al_user_veh_rule              | table    | jp
 public | al_user_veh_rule_seq          | sequence | jp
 public | al_user_veh_time_rule         | table    | jp
 public | al_userid_seq                 | sequence | jp
 public | al_userrule_recip             | table    | jp
 public | al_userrulerecip_seq          | sequence | jp
 public | al_vehicle                    | table    | jp
 public | al_vehicle_last_point         | table    | jp
 public | al_vehicle_route              | table    | jp
 public | al_vehicle_route_bak          | table    | jp
 public | al_vehicle_route_trip         | table    | jp
 public | al_vehicle_tag                | table    | jp
 public | al_vehicleid_seq              | sequence | jp
 public | al_vehiclerouteid_seq         | sequence | jp
 public | al_vehicleroutetripid_seq     | sequence | jp
 public | al_vehicletag_seq             | sequence | jp
 public | al_workorder                  | table    | jp
 public | al_workorder_id_seq           | sequence | jp
 public | chat_id_seq                   | sequence | jp
 public | dic_2012                      | table    | jp
 public | dic_2012_resumen              | table    | jp
 public | ene_2013                      | table    | jp
 public | ene_2013_resumen              | table    | jp
 public | export_place                  | table    | jp
 public | feb_2013                      | table    | jp
 public | feb_2013_resumen              | table    | jp
 public | flight_id_seq                 | sequence | jp
 public | gadm_reduced_2                | table    | jp
 public | gadm_reduced_2_gid_seq        | sequence | jp
 public | geography_columns             | view     | postgres
 public | geometry_columns              | table    | jp
 public | mar_2013                      | table    | jp
 public | mar_2013_resumen              | table    | jp
 public | movie_id_seq                  | sequence | jp
 public | nov_2012                      | table    | jp
 public | nov_2012_resumen              | table    | jp
 public | oct_2012                      | table    | jp
 public | oct_2012_resumen              | table    | jp
 public | sept_2012                     | table    | jp
 public | sept_2012_resumen             | table    | jp
 public | showtime_id_seq               | sequence | jp
 public | spatial_ref_sys               | table    | jp
 public | stats_id_seq                  | sequence | jp
 public | test                          | table    | jp
 public | theater_id_seq                | sequence | jp
 public | tmp_place                     | table    | jp
 public | traffic_blockgroups           | table    | jp
 public | traffic_blockgroups_levels    | view     | jp
 public | traffic_blocks_levels         | view     | jp
 public | traffic_levels_b              | table    | jp
 public | traffic_levels_bg             | table    | jp
 public | traffic_streets               | view     | jp
 public | weather_id_seq                | sequence | jp

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

On Thu, Jun 27, 2013 at 11:29 AM, Marcos Cano <mcano@stsa.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.ru@gmail.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@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
>
>



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

On Mon, Jul 1, 2013 at 9:20 AM, Marcos Cano <mcano@stsa.info> wrote:
> 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

Hm... have you performed soft upgrade to PostGIS 1.5.8?

If so then try to pg_dump the geography_columns table, then drop it,
do pg_upgrade, and restore the dump of geography_columns. It is just
my guess but it could help.

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