Re: Upgrade 96 -> 11 - Mailing list pgsql-general

On 9/2/19 2:45 PM, James Sewell wrote:
> 
> 
> On Tue, 3 Sep 2019 at 7:41 am, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 9/2/19 2:20 PM, James Sewell wrote:
> 
>      >
>      >     So this is a separate cluster from the one you used
>     pg_upgrade on?
>      >
>      >
>      > In that case yes it was seperate
>      >
>      >
>      >
>      >     In other words when doing pg_upgrade you could not get a working
>      >     cluster, correct?
>      >
>      >
>      > Pg_upgrade does not complete - but as it hasn’t started migrating
>     data I
>      > can start it and it’s fine (except for postgis which is now in a
>     partial
>      > install state)
> 
>     Now I will actually respond to list:)
> 
>     So from your original post:
> 
>     1) "The tablename table gets created causing the above error"
> 
>     Is 'tablename' a user table or part of PostGIS?
> 
> 
> The tablename table is a user table, spatial_ref_sys is a postgis table.

I am going to assume then that it has to do with this:
"LINE 39:     "location_pt" "public"."geography"(Point,4283), "

What is the above pointing to?


> 
>     2) "Both the versions have PostGIS 2.5.1 installed and working"
> 
>     How do you know it is working on the 11 version?
> 
> 
> By version I mean 11 - I can init a new cluster and it’s fine
> 
> The more I look at this it seems like:
> 
> A) pg_upgrade somehow creates postgis without supporting tables

So you see a CREATE EXTENSION postgis?

Is there anything in the logs that pertains to the above?


> B) while the tables would be created from the schema dump files, they 
> happen too late

What happens if you use pg_dump from version 11 against the 9.6 cluster 
with the -s and -p?

Does it shows the schema creation in the order you need?

> 
> 
> 
> 
>      >
>      >
>      >
>      >
>      >
>      >
>      >
>      >     --
> 
>      > James Sewell,
>      > Chief Architect
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> -- 
> James Sewell,
> Chief Architect
> 
> Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
> P (+61) 2 8099 9000  W www.jirotech.com 
> <http://www.jirotech.com>  F (+61) 2 8099 9099
> 
> ------------------------------------------------------------------------
> The contents of this email are confidential and may be subject to legal 
> or professional privilege and copyright. No representation is made that 
> this email is free of viruses or other defects. If you have received 
> this communication in error, you may not copy or distribute any part of 
> it or otherwise disclose its contents to anyone. Please advise the 
> sender of your incorrect receipt of this correspondence.


-- 
Adrian Klaver
adrian.klaver@aklaver.com