Thread: Upgrade 96 -> 11

Upgrade 96 -> 11

From
James Sewell
Date:
Hi,

I'm in the process of upgrading from 96 -> 11 (on RHEL 7.3) . Both the versions have PostGIS 2.5.1 installed and working.

pg_upgrade fails with:

pg_restore: [archiver (db)] Error from TOC entry 440; 1259 537086 TABLE tablename databasename
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.spatial_ref_sys" does not exist
LINE 39:     "location_pt" "public"."geography"(Point,4283),  

On looking further at the sequence of events I can see that:
  1. The PostGIS extension is created (but somehow the related tables such as spatial_ref_sys do not get created)
  2. The tablename table gets created causing the above error
  3. At some point later in the upgrade  spatial_ref_sys is to be created

Is there any way round this type of issue (I guess forcing spatial_ref_sys to be created either with the extension as normal or just before any tables which rely on it).

Cheers,
James Sewell,



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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
On 9/1/19 9:03 PM, James Sewell wrote:
> Hi,
> 
> I'm in the process of upgrading from 96 -> 11 (on RHEL 7.3) . Both the 
> versions have PostGIS 2.5.1 installed and working.
> 
> pg_upgrade fails with:
> 
> pg_restore: [archiver (db)] Error from TOC entry 440; 1259 537086 TABLE 
> tablename databasename
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
> "public.spatial_ref_sys" does not exist
> LINE 39:     "location_pt" "public"."geography"(Point,4283),
> 

You used the 11 version of pg_upgrade, correct?


> On looking further at the sequence of events I can see that:
> 
>  1. The PostGIS extension is created (but somehow the related tables
>     such as spatial_ref_sys do not get created)
>  2. The tablename table gets created causing the above error
>  3. At some point later in the upgrade spatial_ref_sys is to be created


Questions:

1) How was PostGIS installed on the 9.6.? and 11.? versions?
Where extensions used or was the manual method used?

2) Did you end up with a working install in 11.?



> 
> 
> Is there any way round this type of issue (I guess forcing 
> spatial_ref_sys to be created either with the extension as normal or 
> just before any tables which rely on it).
> 
> Cheers,
> James Sewell,
> 
> 
> ------------------------------------------------------------------------
> 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



Re: Upgrade 96 -> 11

From
James Sewell
Date:


On Tue, 3 Sep 2019 at 7:15 am, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/2/19 2:04 PM, James Sewell wrote:
Please reply to list also.
Ccing list.
>
>
> On Mon, 2 Sep 2019 at 11:56 pm, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 9/1/19 9:03 PM, James Sewell wrote:
>      > Hi,
>      >
>      > I'm in the process of upgrading from 96 -> 11 (on RHEL 7.3) .
>     Both the
>      > versions have PostGIS 2.5.1 installed and working.
>      >
>      > pg_upgrade fails with:
>      >
>      > pg_restore: [archiver (db)] Error from TOC entry 440; 1259 537086
>     TABLE
>      > tablename databasename
>      > pg_restore: [archiver (db)] could not execute query: ERROR:
>       relation
>      > "public.spatial_ref_sys" does not exist
>      > LINE 39:     "location_pt" "public"."geography"(Point,4283),
>      >
>
>     You used the 11 version of pg_upgrade, correct?
>
>
> Correct
>
>
>
>
>      > On looking further at the sequence of events I can see that:
>      >
>      >  1. The PostGIS extension is created (but somehow the related tables
>      >     such as spatial_ref_sys do not get created)
>      >  2. The tablename table gets created causing the above error
>      >  3. At some point later in the upgrade spatial_ref_sys is to be
>     created
>
>
>     Questions:
>
>     1) How was PostGIS installed on the 9.6.? and 11.? versions?
>     Where extensions used or was the manual method used?
>
>
> Both from extensions, although 9.6 was and older version which was
> upgraded (with ALTER)
>
>
>
>     2) Did you end up with a working install in 11.?
>
>
> Yes. If I create a cluster and start it and create the extension all is
> fine (postgis tabkes created).

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)







--
Adrian Klaver
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  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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
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?

2) "Both the versions have PostGIS 2.5.1 installed and working"

How do you know it is working on the 11 version?


> 
> 
> 
> 
> 
> 
> 
>     -- 

> James Sewell,
> Chief Architect

-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Upgrade 96 -> 11

From
James Sewell
Date:


On Tue, 3 Sep 2019 at 7:41 am, Adrian Klaver <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. 

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
B) while the tables would be created from the schema dump files, they happen too late




>
>
>
>
>
>
>
>     --

> James Sewell,
> Chief Architect

--
Adrian Klaver
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  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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
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



Re: Upgrade 96 -> 11

From
James Sewell
Date:


On Tue, 3 Sep 2019 at 07:55, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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?

This needs the PostGIS types and tables to work - they don't exist as they were not created with CREATE EXTENSION, but rather are to be created later in the restore. 


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

Yes and no. Just the create extension.
 


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

Yes. It's only in the binary_upgrade mode I see issues (because the extension stuff isn't created with CREATE EXTENSION)
 

>
>
>
>
>      >
>      >
>      >
>      >
>      >
>      >
>      >
>      >     --

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


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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
On 9/2/19 4:57 PM, James Sewell wrote:
> 
> 

> 
>     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?
> 
> 
> This needs the PostGIS types and tables to work - they don't exist as 
> they were not created with CREATE EXTENSION, but rather are to be 
> created later in the restore.
> 
> 
> 
>      >
>      >     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?
> 
> 
> Yes and no. Just the create extension.
> 
> 
> 
>      > 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?
> 
> 
> Yes. It's only in the binary_upgrade mode I see issues (because the 
> extension stuff isn't created with CREATE EXTENSION)
> 

What is the pg_upgrade command you are using?



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Upgrade 96 -> 11

From
James Sewell
Date:


>     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?
>
>
> This needs the PostGIS types and tables to work - they don't exist as
> they were not created with CREATE EXTENSION, but rather are to be
> created later in the restore.
>
>
>
>      >
>      >     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?
>
>
> Yes and no. Just the create extension.
>
>
>
>      > 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?
>
>
> Yes. It's only in the binary_upgrade mode I see issues (because the
> extension stuff isn't created with CREATE EXTENSION)
>

What is the pg_upgrade command you are using?

pg_upgrade --link --username postgres

I have set PGBINOLD, PGBINNEW, PGDATAOLD, PGDATANEW correctly.
 



--
Adrian Klaver
adrian.klaver@aklaver.com


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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
On 9/2/19 5:20 PM, James Sewell wrote:
> 

>     What is the pg_upgrade command you are using?
> 
> 
> pg_upgrade --link --username postgres

Where are you in being able to?:
https://www.postgresql.org/docs/11/pgupgrade.html

16. Reverting to old cluster

...

If the --link option was used, the data files might be shared between 
the old and new cluster:

> 
> I have set PGBINOLD, PGBINNEW, PGDATAOLD, PGDATANEW correctly.
> 
> 
>

-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Upgrade 96 -> 11

From
James Sewell
Date:


On Tue, 3 Sep 2019 at 10:34, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/2/19 5:20 PM, James Sewell wrote:
>

>     What is the pg_upgrade command you are using?
>
>
> pg_upgrade --link --username postgres

Where are you in being able to?:
https://www.postgresql.org/docs/11/pgupgrade.html

16. Reverting to old cluster

...

If the --link option was used, the data files might be shared between
the old and new cluster:


It's still creating the schema elements when it fails, it hasn't started linking yet
 
>
> I have set PGBINOLD, PGBINNEW, PGDATAOLD, PGDATANEW correctly.
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com


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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
On 9/2/19 5:38 PM, James Sewell wrote:
> 
> 
> On Tue, 3 Sep 2019 at 10:34, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 9/2/19 5:20 PM, James Sewell wrote:
>      >
> 
>      >     What is the pg_upgrade command you are using?
>      >
>      >
>      > pg_upgrade --link --username postgres
> 
>     Where are you in being able to?:
>     https://www.postgresql.org/docs/11/pgupgrade.html
> 
>     16. Reverting to old cluster
> 
>     ...
> 
>     If the --link option was used, the data files might be shared between
>     the old and new cluster:
> 
> 
> It's still creating the schema elements when it fails, it hasn't started 
> linking yet

Alright at least you still a working 9.6 cluster .

Not sure where to go from here. Like you I am not sure how it can CREATE 
EXTENSION and not actually follow through on that. Especially with no 
errors for that operation. I'm going to have to think on this. Hopefully 
someone else has an idea on this and can chime in.

> 
>      >
>      > I have set PGBINOLD, PGBINNEW, PGDATAOLD, PGDATANEW correctly.
>      >
>      >
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> ------------------------------------------------------------------------
> 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



Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
On 9/2/19 5:52 PM, Adrian Klaver wrote:

>> It's still creating the schema elements when it fails, it hasn't 
>> started linking yet
> 
> Alright at least you still a working 9.6 cluster .
> 
> Not sure where to go from here. Like you I am not sure how it can CREATE 
> EXTENSION and not actually follow through on that. Especially with no 
> errors for that operation. I'm going to have to think on this. Hopefully 
> someone else has an idea on this and can chime in.

Aah. I don't have postgis installed, still:

pg_dump --binary-upgrade -s  -d production -U postgres > 
production_binary.sql

-- For binary upgrade, create an empty extension and insert objects into it
DROP EXTENSION IF EXISTS tablefunc;
SELECT pg_catalog.binary_upgrade_create_empty_extension('tablefunc', 
'public', true, '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[]);


Try the above on your schema and see what you get.


> 
>>
>>      >
>>      > I have set PGBINOLD, PGBINNEW, PGDATAOLD, PGDATANEW correctly.
>>     

-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Upgrade 96 -> 11

From
James Sewell
Date:


On Wed, 4 Sep 2019 at 5:47 am, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/2/19 5:52 PM, Adrian Klaver wrote:

>> It's still creating the schema elements when it fails, it hasn't
>> started linking yet
>
> Alright at least you still a working 9.6 cluster .
>
> Not sure where to go from here. Like you I am not sure how it can CREATE
> EXTENSION and not actually follow through on that. Especially with no
> errors for that operation. I'm going to have to think on this. Hopefully
> someone else has an idea on this and can chime in.

Aah. I don't have postgis installed, still:

pg_dump --binary-upgrade -s  -d production -U postgres >
production_binary.sql

-- For binary upgrade, create an empty extension and insert objects into it
DROP EXTENSION IF EXISTS tablefunc;
SELECT pg_catalog.binary_upgrade_create_empty_extension('tablefunc',
'public', true, '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[]);


Try the above on your schema and see what you get.

Yep - an empty extension. I think this logic is wrong. Creating an empty extension is fine and makes sense but extension owned relations should be created as the next step, not just at some time later.



>
>>
>>      >
>>      > I have set PGBINOLD, PGBINNEW, PGDATAOLD, PGDATANEW correctly.
>>     

--
Adrian Klaver
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  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.

Re: Upgrade 96 -> 11

From
Adrian Klaver
Date:
On 9/3/19 3:45 PM, James Sewell wrote:
> 
> 

> 
>     -- For binary upgrade, create an empty extension and insert objects
>     into it
>     DROP EXTENSION IF EXISTS tablefunc;
>     SELECT pg_catalog.binary_upgrade_create_empty_extension('tablefunc',
>     'public', true, '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[]);
> 
> 
>     Try the above on your schema and see what you get.
> 
> 
> Yep - an empty extension. I think this logic is wrong. Creating an empty 
> extension is fine and makes sense but extension owned relations should 
> be created as the next step, not just at some time later.
> 

So to be clear you ran pg_dump with  --binary-upgrade and the extension 
elements where created after the user table that tripped the error?



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Upgrade 96 -> 11

From
James Sewell
Date:
If anyone hits this it is an issue with using the Geography type with a non 4326 SRID on a table and pg_upgrade.

It should be fixed (the check dropped as it's something of a relic) in the next version of PostGIS. In the meantime you would have to patch it out yourself.


On Wed, 4 Sep 2019 at 10:03, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/3/19 3:45 PM, James Sewell wrote:
>
>

>
>     -- For binary upgrade, create an empty extension and insert objects
>     into it
>     DROP EXTENSION IF EXISTS tablefunc;
>     SELECT pg_catalog.binary_upgrade_create_empty_extension('tablefunc',
>     'public', true, '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[]);
>
>
>     Try the above on your schema and see what you get.
>
>
> Yep - an empty extension. I think this logic is wrong. Creating an empty
> extension is fine and makes sense but extension owned relations should
> be created as the next step, not just at some time later.
>

So to be clear you ran pg_dump with  --binary-upgrade and the extension
elements where created after the user table that tripped the error?



--
Adrian Klaver
adrian.klaver@aklaver.com


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.