pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade / postgis issue
12+ messages / 3 participants
[nested] [flat]
* pg_upgrade / postgis issue
@ 2013-06-26 14:10 Marcos Cano <mcano@stsa.info>
0 siblings, 2 replies; 12+ messages in thread
From: Marcos Cano @ 2013-06-26 14:10 UTC (permalink / raw)
To: pgsql-admin
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.
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-06-26 14:16 Bruce Momjian <bruce@momjian.us>
parent: Marcos Cano <mcano@stsa.info>
1 sibling, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 2013-06-26 14:16 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; +Cc: pgsql-admin
On Wed, Jun 26, 2013 at 07:10:11AM -0700, Marcos Cano 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.
>
> 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.
I don't think postgis can go from 1 to 2 with pg_upgrade because I
thought they changed their internal format, but I am not sure. There
are various ways of getting help for postgis:
http://postgis.net/support/
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-06-26 19:09 Sergey Konoplev <gray.ru@gmail.com>
parent: Marcos Cano <mcano@stsa.info>
1 sibling, 1 reply; 12+ messages in thread
From: Sergey Konoplev @ 2013-06-26 19:09 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-06-27 16:37 Sergey Konoplev <gray.ru@gmail.com>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Sergey Konoplev @ 2013-06-27 16:37 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-06-27 16:58 Sergey Konoplev <gray.ru@gmail.com>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Sergey Konoplev @ 2013-06-27 16:58 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; pgsql-admin
(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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-06-27 18:29 Marcos Cano <mcano@stsa.info>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Marcos Cano @ 2013-06-27 18:29 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
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
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-06-27 20:25 Sergey Konoplev <gray.ru@gmail.com>
parent: Marcos Cano <mcano@stsa.info>
0 siblings, 1 reply; 12+ messages in thread
From: Sergey Konoplev @ 2013-06-27 20:25 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-07-01 16:15 Marcos Cano <mcano@stsa.info>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Marcos Cano @ 2013-07-01 16:15 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: 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
*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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-07-01 16:20 Marcos Cano <mcano@stsa.info>
parent: Marcos Cano <mcano@stsa.info>
0 siblings, 1 reply; 12+ messages in thread
From: Marcos Cano @ 2013-07-01 16:20 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: 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
*2nd: i run
psql -d dev
*
*and then
dev=# \d
*
*and a list of 330 rows appear do you need that 330 rows?*
On Mon, Jul 1, 2013 at 10:15 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
>
>
> *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
>>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-07-04 01:12 Sergey Konoplev <gray.ru@gmail.com>
parent: Marcos Cano <mcano@stsa.info>
0 siblings, 1 reply; 12+ messages in thread
From: Sergey Konoplev @ 2013-07-04 01:12 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-07-04 14:12 Marcos Cano <mcano@stsa.info>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Marcos Cano @ 2013-07-04 14:12 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-admin
yes i did the soft upgrade (seems to work) but when doing the pg_upgrade
part it seems to no work... ok thanks for your advice.. i will try.
so i came up with an idea and it seems to work just wanna make sure what
you think?
- do a custom pg_dump of postgres 8.3.2+postgis 1.3
- do restore (with the perl script of postgis) within the postgres
9.2.4+postgis 2.0.4
On Wed, Jul 3, 2013 at 7:12 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: pg_upgrade / postgis issue
@ 2013-07-07 19:38 Sergey Konoplev <gray.ru@gmail.com>
parent: Marcos Cano <mcano@stsa.info>
0 siblings, 0 replies; 12+ messages in thread
From: Sergey Konoplev @ 2013-07-07 19:38 UTC (permalink / raw)
To: Marcos Cano <mcano@stsa.info>; +Cc: pgsql-admin
On Thu, Jul 4, 2013 at 7:12 AM, Marcos Cano <mcano@stsa.info> wrote:
> yes i did the soft upgrade (seems to work) but when doing the pg_upgrade part it seems to no work... ok thanks for your advice.. i will try.
>
> so i came up with an idea and it seems to work just wanna make sure what you think?
>
> - do a custom pg_dump of postgres 8.3.2+postgis 1.3
> - do restore (with the perl script of postgis) within the postgres 9.2.4+postgis 2.0.4
I have never tried it between two major postgres versions, but IMHO it
might work it you are talking about the hard postgis upgrade.
However I suggest you to try pg_upgrade with dump/delete/restore of
geography_columns first, because you will not face the full
dump/restore process in this case.
>
>
> On Wed, Jul 3, 2013 at 7:12 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
>>
>> 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
>
>
--
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2013-07-07 19:38 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-06-26 14:10 pg_upgrade / postgis issue Marcos Cano <mcano@stsa.info>
2013-06-26 14:16 ` Bruce Momjian <bruce@momjian.us>
2013-06-26 19:09 ` Sergey Konoplev <gray.ru@gmail.com>
2013-06-27 16:37 ` Sergey Konoplev <gray.ru@gmail.com>
2013-06-27 16:58 ` Sergey Konoplev <gray.ru@gmail.com>
2013-06-27 18:29 ` Marcos Cano <mcano@stsa.info>
2013-06-27 20:25 ` Sergey Konoplev <gray.ru@gmail.com>
2013-07-01 16:15 ` Marcos Cano <mcano@stsa.info>
2013-07-01 16:20 ` Marcos Cano <mcano@stsa.info>
2013-07-04 01:12 ` Sergey Konoplev <gray.ru@gmail.com>
2013-07-04 14:12 ` Marcos Cano <mcano@stsa.info>
2013-07-07 19:38 ` Sergey Konoplev <gray.ru@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox