Upgrade 96 -> 11

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

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.

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

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.

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&gt;  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

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&gt; 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.

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

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.

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

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.

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

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

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.

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

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.

(
https://github.com/postgis/postgis/blob/svn-trunk/postgis/gserialized_typmod.c#L296
)

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.