partition by range or by list constraint check (was Re: literal vs dynamic partition constraint in plan execution)

[Luca Ferrari]

I've done a simple test case, and find out that probably the problem I
got was due to the partition schema I'm using.
I want a table to be partitioned by a timestamp field with a first
level partition by year, and a second level by month. Therefore, I did
a BY LIST partitioning, but that produces a wrong constraint check
when executing a query.
This is a reproducible example.

BEGIN;

CREATE TABLE root( pk int generated always as identity, v int, ts
timestamp default current_timestamp )
PARTITION BY LIST( extract( year from ts ) );

CREATE TABLE y2018
PARTITION OF root
FOR VALUES IN ( 2018 );

CREATE TABLE y2019
PARTITION OF root
FOR VALUES IN ( 2019 );

 ALTER TABLE y2018ADD CHECK( ts >= make_timestamp( 2018, 1, 1, 0, 0, 0 )
 AND ts <= make_timestamp( 2018, 12, 31, 23, 59, 59 ) );

 ALTER TABLE y2019ADD CHECK( ts >= make_timestamp( 2019, 1, 1, 0, 0, 0 )
 AND ts <= make_timestamp( 2019, 12, 31, 23, 59, 59 ) );

INSERT INTO root( v )
SELECT generate_series( 1, 100 ); -- same ts here

COMMIT;

Now if I try to explain a query with the current timestamp (which is
of course in 2019):

testdb=# explain select * from root where ts = current_timestamp;
QUERY PLAN
-
Append  (cost=0.00..75.59 rows=18 width=16)
->  Seq Scan on y2018  (cost=0.00..37.75 rows=9 width=16)
Filter: (ts = CURRENT_TIMESTAMP)
->  Seq Scan on y2019  (cost=0.00..37.75 rows=9 width=16)
Filter: (ts = CURRENT_TIMESTAMP)
(5 rows)

I got y2018 scanned too, which of course could not be the case since
y2018 cannot contain values that are equal to current_timestamp.
However, if I use a literal the query works fine:

testdb=# explain select * from root where ts = '2019-09-01 09:00:00.00';
QUERY PLAN
---
Append  (cost=0.00..33.17 rows=9 width=16)
->  Seq Scan on y2019  (cost=0.00..33.12 rows=9 width=16)
Filter: (ts = '2019-09-01 09:00:00'::timestamp without time zone)
(3 rows)


Now, if I change the partition schema using a range, the query works
fine with current_timestamp too:

CREATE TABLE root( pk int generated always as identity, v int, ts
timestamp default current_timestamp )
PARTITION BY RANGE( ts );

CREATE TABLE y2018
PARTITION OF root
FOR VALUES FROM ('2018-01-01 00:00:00.00')
TO ('2018-12-31 23:59:59.00');

CREATE TABLE y2019
PARTITION OF root
FOR VALUES FROM ('2019-01-01 00:00:00.00')
TO ('2019-12-31 23:59:59.00');


testdb=# explain select * from root where ts = current_timestamp;
 QUERY PLAN
-
 Append  (cost=0.00..75.59 rows=18 width=16)
   Subplans Removed: 1
   ->  Seq Scan on y2019  (cost=0.00..37.75 rows=9 width=16)
 Filter: (ts = CURRENT_TIMESTAMP)
(4 rows)



So my end with this is that:
- the list partitioning prevents the current_timestamp to be compared
against the list of possible values (extract year from
current_timestamp) and therefore the planner has no chance but to get
into all the tables, even if the constraints on the ts field
explicitly state some tables can be removed;
- in range partitioning, since the partition is built on the very
range of values, the planner gets the correct path.

I still don't get why using a literal in the first case can lead to a
"more correct" plan.
And I'm curious to know if there's a way to force constraints in the
list partitioning to make the planner really aware of tables that can
be excluded.

Luca

Re: Upgrade 96 -> 11

[Adrian Klaver]

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.kla...@aklaver.com

Re: partition by range or by list constraint check (was Re: literal vs dynamic partition constraint in plan execution)

[Tom Lane]

Luca Ferrari  writes:
> I've done a simple test case, and find out that probably the problem I
> got was due to the partition schema I'm using.
> I want a table to be partitioned by a timestamp field with a first
> level partition by year, and a second level by month. Therefore, I did
> a BY LIST partitioning,

> CREATE TABLE root( pk int generated always as identity, v int, ts
> timestamp default current_timestamp )
> PARTITION BY LIST( extract( year from ts ) );

No, that's not going to work, unless your queries all explicitly use
"extract( year from ts ) = something" in their WHERE clauses.  There
is nothing in the system that would derive a constraint like that
from a constraint that just mentions ts.

In your example, the partition routing logic was entirely ineffective
because of this, so you tried to make up for that by adding
CHECK constraints.  But pruning based on CHECK constraints is done
at planning time, so it can't do anything with run-time-mutable
values such as CURRENT_TIMESTAMP.

> CREATE TABLE root( pk int generated always as identity, v int, ts
> timestamp default current_timestamp )
> PARTITION BY RANGE( ts );

> CREATE TABLE y2018
> PARTITION OF root
> FOR VALUES FROM ('2018-01-01 00:00:00.00')
> TO ('2018-12-31 23:59:59.00');

This is mostly the right way to do it; you forgot that range bounds
use "low <= variable < high" logic.  So the correct way to declare
the partitions is like

regression=# CREATE TABLE y2018
regression-# PARTITION OF root
regression-# FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE
regression=# CREATE TABLE y2019
PARTITION OF root
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE TABLE

That's easier and doesn't leave a gap between partitions.

This is already sufficient to allow partition routing based on
equality or simple inequality involving "ts", so you don't need
extra CHECK constraints.

> I still don't get why using a literal in the first case can lead to a
> "more correct" plan.

With a literal, plan-time pruning based on the CHECK constraints
was possible.

> And I'm curious to know if there's a way to force constraints in the
> list partitioning to make the planner really aware of tables that can
> be excluded.

No.  The short answer here is that your query WHERE clauses have to
be things that the planner or partition routing code can relate to
the partitioning rules.  In the case of LIST partitioning, that means
there had better be WHERE constraints on the values specified in
the LIST clause, not values that perhaps could be shown to be related
to those values given extensive knowledge about the behaviors of
certain functions.  By and large, the system doesn't have such
knowledge.

regards, tom lane

Posible off topic ? pgmodeler

[stan]

Some very helpful folks pointed me to pgmodeler recently.

I realize this list may not be the appropriate forum for discussing this, and
would welcome pointers to a more appropriate forum.

Having said that, this discussion may be a bit more generic.

I have seen a number of open source products (Amanda is a good example), where a
company sort of "takes over" the project, the company's bossiness plan is 
generally
that of making money on support. Most of these companies have a community 
edition,
which trails development of the product with their enhancements. U understand
that this model is acceptable to GPL licensed software.

The company supporting pgmodeler seems to have a different model. It looks like
they provide a version of the tool that requires a license key, with limited 
life
span. 

What I am trying to do, at the moment is get a complete understanding of their
bossiness model, regarding the source code for this project.

Thanks for any input on this.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Posible off topic ? pgmodeler

[Susan Hurst]

Hi Stan!

Don't know if you're interested in pgmodeler specifically or database 
modelers in general.  At work, we use postgresql a lot and have found 
that DBeaver, which is open source, does a decent job of creating ERD 
diagrams.  Personally, I don't like using DBeaver's interface for 
executing SQL statements but some of its other features are nice.


Sue

---

Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hu...@brookhurstdata.com
Mobile: 314-486-3261

On 2019-09-02 12:31, stan wrote:

Some very helpful folks pointed me to pgmodeler recently.

I realize this list may not be the appropriate forum for discussing 
this, and

would welcome pointers to a more appropriate forum.

Having said that, this discussion may be a bit more generic.

I have seen a number of open source products (Amanda is a good 
example), where a

company sort of "takes over" the project, the company's bossiness plan
is generally
that of making money on support. Most of these companies have a
community edition,
which trails development of the product with their enhancements. U 
understand

that this model is acceptable to GPL licensed software.

The company supporting pgmodeler seems to have a different model. It 
looks like

they provide a version of the tool that requires a license key, with
limited life
span.

What I am trying to do, at the moment is get a complete understanding 
of their

bossiness model, regarding the source code for this project.

Thanks for any input on this.

Re: Posible off topic ? pgmodeler

[Thiemo Kellner]

Quoting stan :

What I am trying to do, at the moment is get a complete  
understanding of their

bossiness model, regarding the source code for this project.

Thanks for any input on this.


To the best of my knowledge Raphael tries to fund its time on the  
development of pgmodeler with selling access keys to the binary  
package he distributes. https://pgmodeler.io/download states


"Purchase an access key and support this project. Pay using PayPal™ or  
Bitcoins and enjoy ready-to-use packages."


However, being versioned on github you could compile it yourself any  
time and I suppose at least major Linux distributions provide a  
package of a more or less recent version of it. Ubuntu does anyway. I  
am not aware of an other binary distributor but Raphael for Windows or  
Mac, but then again those are not my turf.


Kind regards Thiemo

--
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B


smime.p7s
Description: S/MIME Signature

Re: Upgrade 96 -> 11

[James Sewell]

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

[Adrian Klaver]

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.kla...@aklaver.com

Re: Upgrade 96 -> 11

[Adrian Klaver]

On 9/2/19 2:45 PM, James Sewell wrote:



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



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Upgrade 96 -> 11

[James Sewell]

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

[Adrian Klaver]

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.kla...@aklaver.com

Re: Upgrade 96 -> 11

[James Sewell]

>
>
>
> > 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.kla...@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

[James Sewell]

On Tue, 3 Sep 2019 at 07:55, Adrian Klaver 
wrote:

> On 9/2/19 2:45 PM, James Sewell wrote:
> >
> >
> > On Tue, 3 Sep 2019 at 7:41 am, Adrian Klaver  > > 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.kla...@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.
>
>
> --
> Adrian Klaver
> adrian.kla...@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

[Adrian Klaver]

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.kla...@aklaver.com

Re: Upgrade 96 -> 11

[James Sewell]

On Tue, 3 Sep 2019 at 10:34, Adrian Klaver 
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.kla...@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

[Adrian Klaver]

On 9/2/19 5:38 PM, James Sewell wrote:



On Tue, 3 Sep 2019 at 10:34, Adrian Klaver > 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.kla...@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.kla...@aklaver.com