Re: Upgrade 96 -> 11

On 9/2/19 2:45 PM, James Sewell wrote:
>
>
> On Tue, 3 Sep 2019 at 7:41 am, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com