PG9.1 migration to PG9.6, dump/restore issues

[Scot Kreienkamp]

Hi Everyone,

I am working on a migration from PG9.1 to PG9.6.  Hoping some people can chime 
in on my plans as I am running into some restore issues.

We are upgrading to a new version of PG and migrating to new hardware with RHEL 
7, so I am planning on doing a dump and restore to get moved to the new server. 
 My database is about 300 gigs, not huge but big enough that doing a single 
threaded dump with multi-threaded restore is going to take too much time for 
the window of opportunity I've been given.  I know I can use multi-threaded 
restore on PG9.6 using the custom or directory formats, but PG9.1 only supports 
single threaded dump.  To get around this I'm going to disable all database 
access to the PG9.1 databases, then use the PG9.6 tools to do a multi-threaded 
dump and then multi-threaded restore.

These are the commands I was using:
pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
--no-synchronized-snapshots
created $DATABASE
pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   --disable-triggers

Restore completes successfully, but I noticed that the schema permissions are 
missing, possibly others as well (Is this a known issue?).  So instead, I tried 
backing up and restoring the schema only as single threaded dump and restore, 
then dumping the data multi-threaded using the PG9.6 tools, then doing a 
multi-threaded data-only restore using PG9.6 tools into the already existing 
schema.

These are the commands I'm using now:
pg_dump -sh $OLDSERVER  $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql
pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
--no-synchronized-snapshots
createdb $DATABASE
psql -d $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql
pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   -a --disable-triggers


That seemed to work OK so far, but the missing schema permissions from my first 
try has me spooked.  Are there any problems with attempting this type of 
backup/restore?  Would I be better off using the commands from my first attempt 
and reapplying permissions?  Or is doing a single threaded dump my only option 
to get a good backup?  I have to be able to revert to the old server as this is 
production, so doing in place upgrades are not possible... the original server 
has to remain pristine.


Thanks!



Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162  | * 734-384-6403 | | * 7349151444 
| *  scot.kreienk...@la-z-boy.com
www.la-z-boy.com | 
facebook.com/lazboy
 | twitter.com/lazboy | 
youtube.com/lazboy

[cid:lzbVertical_hres.jpg]



This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: PG9.1 migration to PG9.6, dump/restore issues

[Adrian Klaver]

On 9/12/18 6:55 AM, Scot Kreienkamp wrote:

Hi Everyone,

I am working on a migration from PG9.1 to PG9.6.  Hoping some people can 
chime in on my plans as I am running into some restore issues.


We are upgrading to a new version of PG and migrating to new hardware 
with RHEL 7, so I am planning on doing a dump and restore to get moved 
to the new server.  My database is about 300 gigs, not huge but big 
enough that doing a single threaded dump with multi-threaded restore is 
going to take too much time for the window of opportunity I’ve been 
given.  I know I can use multi-threaded restore on PG9.6 using the 
custom or directory formats, but PG9.1 only supports single threaded 
dump. To get around this I’m going to disable all database access to the 
PG9.1 databases, then use the PG9.6 tools to do a multi-threaded dump 
and then multi-threaded restore.


These are the commands I was using:

pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
--no-synchronized-snapshots


created $DATABASE

pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   --disable-triggers

Restore completes successfully, but I noticed that the schema 
permissions are missing, possibly others as well (Is this a known 
issue?).  So instead, I tried backing up and restoring the schema only 


Did you see errors in the restore?

In particular about not finding roles(users) for the permissions?

I ask because I do not see in the above anything about dumping objects 
global to the cluster. That would include roles. I use:


pg_dumpall -g -f globals.sql

See:

https://www.postgresql.org/docs/10/static/app-pg-dumpall.html


as single threaded dump and restore, then dumping the data 
multi-threaded using the PG9.6 tools, then doing a multi-threaded 
data-only restore using PG9.6 tools into the already existing schema.


These are the commands I’m using now:

pg_dump -sh $OLDSERVER  $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql

pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
--no-synchronized-snapshots


createdb $DATABASE

psql –d $DATABASE –f $BACKUPPATH/$DATABASE.schema.sql

pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   -a 
--disable-triggers


That seemed to work OK so far, but the missing schema permissions from 
my first try has me spooked.  Are there any problems with attempting 
this type of backup/restore?  Would I be better off using the commands 
from my first attempt and reapplying permissions?  Or is doing a single 
threaded dump my only option to get a good backup?  I have to be able to 
revert to the old server as this is production, so doing in place 
upgrades are not possible… the original server has to remain pristine.


Thanks!

*Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate*
One La-Z-Boy Drive | Monroe, Michigan 48162  |( 734-384-6403| 
|)7349151444| * scot.kreienk...@la-z-boy.com 
www .la-z-boy.com  | 
facebook. com 
/ 
lazboy  | 
twitter.com/lazboy  | youtube.com/ 
lazboy 



This messageis intended onlyfor the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is 
exempt from disclosure under applicable laws.  If you are not the 
intended recipient, you are strictly prohibited from disseminating or 
distributing this information (other than to the intended recipient) or 
copying this information.  If you have received this communication in 
error, please notify us immediately by e-mail or by telephone at the 
above number. Thank you.





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

Re: PG9.1 migration to PG9.6, dump/restore issues

[Tom Lane]

Scot Kreienkamp  writes:
> Restore completes successfully, but I noticed that the schema
> permissions are missing, possibly others as well (Is this a known
> issue?).

If you're talking about custom modifications you made to the permissions
of the "public" schema in particular, then yeah, that won't be tracked
(IIRC, it will be with newer source server versions, but not 9.1).
Otherwise, no, that's not expected.  Would you provide more detail?

> These are the commands I'm using now:
> pg_dump -sh $OLDSERVER  $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql
> pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
> --no-synchronized-snapshots
> createdb $DATABASE
> psql -d $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql
> pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   -a --disable-triggers

Also note that this recipe does not copy "global" objects (users and
tablespaces), nor does it restore any database-level properties.
You'd need to use pg_dumpall to transfer those things automatically.
(Possibly "pg_dumpall -g" would be a good starting point here.)

regards, tom lane

Re: PG9.1 migration to PG9.6, dump/restore issues

[Ron]

On 09/12/2018 08:55 AM, Scot Kreienkamp wrote:


Hi Everyone,

I am working on a migration from PG9.1 to PG9.6.  Hoping some people can 
chime in on my plans as I am running into some restore issues.


We are upgrading to a new version of PG and migrating to new hardware with 
RHEL 7, so I am planning on doing a dump and restore to get moved to the 
new server.  My database is about 300 gigs, not huge but big enough that 
doing a single threaded dump with multi-threaded restore is going to take 
too much time for the window of opportunity I’ve been given.  I know I can 
use multi-threaded restore on PG9.6 using the custom or directory formats, 
but PG9.1 only supports single threaded dump.  To get around this I’m 
going to disable all database access to the PG9.1 databases, then use the 
PG9.6 tools to do a multi-threaded dump and then multi-threaded restore.


These are the commands I was using:

pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
--no-synchronized-snapshots


created $DATABASE

pg_restore -evj 4 -d $DATABASE $BACKUPPATH/$DATABASE   --disable-triggers




This is almost exactly what I did when migrating from 8.4 to 9.6. As Adrian 
Klaver mentioned, you need to dump the globals and then run that script on 
the new database.


No need to disable triggers, since it's "relevant only when performing a 
data-only restore", and you aren't doing a data-only restore.  Besides, 
pg_restore adds all that metadata -- including PKs, FKs, indexes, etc. to 
the db *after* the data is loaded.



--
Angular momentum makes the world go 'round.

Select rows when all all ids of its children records matches

[Arup Rakshit]

I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) 
... I want to get all posts which has tag id 1, 2 and 3 for example. How should 
I do this? I tried ALL, but it didn’t work.

Those tag ids comes from UI by users, so I am looking for generic approach.


Thanks,

Arup Rakshit
a...@zeit.io

Re: Select rows when all all ids of its children records matches

[Ron]

Maybe this:
select p.id, p.name
from posts p,
 posts_tags pt,
 tags t
where t.id in (1, 2, 3)
  and t.id = pt.tag_id
  and pt.post_id = p.id;


On 09/12/2018 10:23 AM, Arup Rakshit wrote:
I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, 
name) ... I want to get all posts which has tag id 1, 2 and 3 for example. 
How should I do this? I tried ALL, but it didn’t work.


Those tag ids comes from UI by users, so I am looking for generic approach.


Thanks,

Arup Rakshit
a...@zeit.io 





--
Angular momentum makes the world go 'round.

Re: Select rows when all all ids of its children records matches

[Arup Rakshit]

IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids 
)


Thanks,

Arup Rakshit
a...@zeit.io



> On 12-Sep-2018, at 8:58 PM, Ron  wrote:
> 
> Maybe this:
> select p.id, p.name
> from posts p,
>  posts_tags pt,
>  tags t
> where t.id in (1, 2, 3)
>   and t.id = pt.tag_id
>   and pt.post_id = p.id;
> 
> 
> On 09/12/2018 10:23 AM, Arup Rakshit wrote:
>> I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, 
>> name) ... I want to get all posts which has tag id 1, 2 and 3 for example. 
>> How should I do this? I tried ALL, but it didn’t work.
>> 
>> Those tag ids comes from UI by users, so I am looking for generic approach.
>> 
>> 
>> Thanks,
>> 
>> Arup Rakshit
>> a...@zeit.io 
>> 
>> 
>> 
> 
> -- 
> Angular momentum makes the world go 'round.

Re: Select rows when all all ids of its children records matches

[David G. Johnston]

On Wednesday, September 12, 2018, Arup Rakshit  wrote:

> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 (
> tag ids )
>

Build arrays and then use the “contains” operator.

David J.

Re: Select rows when all all ids of its children records matches

[Arup Rakshit]

Can you show me the SQL construction? Do I need to use `WITH`?


Thanks,

Arup Rakshit
a...@zeit.io



> On 12-Sep-2018, at 9:13 PM, David G. Johnston  
> wrote:
> 
> On Wednesday, September 12, 2018, Arup Rakshit  > wrote:
> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag 
> ids )
> 
> Build arrays and then use the “contains” operator.
> 
> David J. 

Re: Select rows when all all ids of its children records matches

[Alban Hertroys]

> On 12 Sep 2018, at 17:44, Arup Rakshit  wrote:
> 
> Can you show me the SQL construction? Do I need to use `WITH`?


An option is to create a bit-wise OR and SUM the grouped results. If the result 
of these 3 bits is 7, than the post matches all three tags.

select p.id, p.name
  from post p
  join post_tag pt on (pt.post = p.id)
  join tag t on (t.id = pt.tag)
 where t.id in (1, 2, 3)
 group by
  case t.id
   when 1 then 1
   when 2 then 2
   when 3 then 4
   else 0
  end
 having sum(case t.id
   when 1 then 1
   when 2 then 2
   when 3 then 4
   else 0
  end) = 7;

I used ints here for the bitwise OR, a bitstring would probably be neater.

Another approach is to aggregate the set of matching tags into an array using 
array_agg(). I think that's what David means. You could then check the length 
of the array to see if you have all 3 (or 4 or 5 or 9000).

>> On 12-Sep-2018, at 9:13 PM, David G. Johnston  
>> wrote:
>> 
>> On Wednesday, September 12, 2018, Arup Rakshit  wrote:
>> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag 
>> ids )
>> 
>> Build arrays and then use the “contains” operator.
>> 
>> David J. 
> 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Select rows when all all ids of its children records matches

[Arup Rakshit]

I tried :

WITH posts_tags_cte AS (
SELECT post_id, array_agg(tag_id) as tags
FROM posts_tags
WHERE tag_id in (1, 2)
GROUP BY post_id
)
SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = 
posts_tags_cte.post_id
WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.


Thanks,

Arup Rakshit
a...@zeit.io



> On 12-Sep-2018, at 9:14 PM, Arup Rakshit  wrote:
> 
> Can you show me the SQL construction? Do I need to use `WITH`?
> 
> 
> Thanks,
> 
> Arup Rakshit
> a...@zeit.io 
> 
> 
> 
>> On 12-Sep-2018, at 9:13 PM, David G. Johnston > > wrote:
>> 
>> On Wednesday, September 12, 2018, Arup Rakshit > > wrote:
>> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag 
>> ids )
>> 
>> Build arrays and then use the “contains” operator.
>> 
>> David J. 
> 

Re: Select rows when all all ids of its children records matches

[Rob Sargent]

On 09/12/2018 10:08 AM, Arup Rakshit wrote:

I tried :

WITH posts_tags_cte AS (
        SELECT post_id, array_agg(tag_id) as tags
        FROM posts_tags
        WHERE tag_id in (1, 2)
        GROUP BY post_id
)
SELECT posts.id  FROM posts_tags_cte JOIN posts ON 
posts.id  = posts_tags_cte.post_id

WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.



Aren't you looking for cte.tags = array[1,2]?

Re: Select rows when all all ids of its children records matches

[Arup Rakshit]

Hi Rob,

I figured it out. thanks. It is giving correct data.

> Aren't you looking for cte.tags = array[1,2]?

posts_tags_cte has tags column, so I am using it.

Thanks,

Arup Rakshit
a...@zeit.io



> On 12-Sep-2018, at 9:47 PM, Rob Sargent  wrote:
> 
> 
> 
> On 09/12/2018 10:08 AM, Arup Rakshit wrote:
>> I tried :
>> 
>> WITH posts_tags_cte AS (
>> SELECT post_id, array_agg(tag_id) as tags
>> FROM posts_tags
>> WHERE tag_id in (1, 2)
>> GROUP BY post_id
>> )
>> SELECT posts.id  FROM posts_tags_cte JOIN posts ON 
>> posts.id  = posts_tags_cte.post_id
>> WHERE posts_tags_cte.tags @> array[1, 2]::int8[]
>> 
>> But it gives me all the posts.
>> 
>> 
> Aren't you looking for cte.tags = array[1,2]?
> 

RE: PG9.1 migration to PG9.6, dump/restore issues

[Scot Kreienkamp]

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  
Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com
> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Wednesday, September 12, 2018 10:40 AM
> To: Scot Kreienkamp 
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: PG9.1 migration to PG9.6, dump/restore issues
>
> Scot Kreienkamp  writes:
> > Restore completes successfully, but I noticed that the schema
> > permissions are missing, possibly others as well (Is this a known
> > issue?).
>
> If you're talking about custom modifications you made to the permissions
> of the "public" schema in particular, then yeah, that won't be tracked
> (IIRC, it will be with newer source server versions, but not 9.1).
> Otherwise, no, that's not expected.  Would you provide more detail?
>
Yes, it's permissions on the public schema.  They were completely empty.  I 
didn't check the other schemas as the very first thing I noticed was the 
permissions changed on the public schema, but I believe they were empty as well.

> > These are the commands I'm using now:
> > pg_dump -sh $OLDSERVER  $DATABASE -f
> $BACKUPPATH/$DATABASE.schema.sql
> > pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f
> $BACKUPPATH/DATABASE --no-synchronized-snapshots
> > createdb $DATABASE
> > psql -d $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql
> > pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   -a --disable-
> triggers
>
> Also note that this recipe does not copy "global" objects (users and
> tablespaces), nor does it restore any database-level properties.
> You'd need to use pg_dumpall to transfer those things automatically.
> (Possibly "pg_dumpall -g" would be a good starting point here.)
>
>   regards, tom lane

I notice the pg_dumpall -g doesn't bring over the schema permissions either.  
The only way I can get them to come over is pg_dumpall -s, which creates the 
databases and tables as well.  I could drop the databases and create empty ones 
to do the restore I guess, it would only take a few extra seconds.




This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

RE: PG9.1 migration to PG9.6, dump/restore issues

[Scot Kreienkamp]

> Did you see errors in the restore?
>
> In particular about not finding roles(users) for the permissions?
>
> I ask because I do not see in the above anything about dumping objects
> global to the cluster. That would include roles. I use:
>
> pg_dumpall -g -f globals.sql
>
> See:
>
> https://www.postgresql.org/docs/10/static/app-pg-dumpall.html
>

Nope, no errors in the restore that I could see.  As I as discussing with Tom 
Lane, the -g switch doesn't bring over schema permissions either.  I could use 
the -s switch and just re-create the databases as empty again after that's 
applied.  That brings over everything except data.


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  
Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

RE: PG9.1 migration to PG9.6, dump/restore issues

[Scot Kreienkamp]

Thanks Ron, glad to hear it worked and someone was successful at it.  I’m on 
the right path then.



Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Ron [mailto:ronljohnso...@gmail.com]
Sent: Wednesday, September 12, 2018 10:40 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: PG9.1 migration to PG9.6, dump/restore issues

On 09/12/2018 08:55 AM, Scot Kreienkamp wrote:

Hi Everyone,

I am working on a migration from PG9.1 to PG9.6.  Hoping some people can chime 
in on my plans as I am running into some restore issues.

We are upgrading to a new version of PG and migrating to new hardware with RHEL 
7, so I am planning on doing a dump and restore to get moved to the new server. 
 My database is about 300 gigs, not huge but big enough that doing a single 
threaded dump with multi-threaded restore is going to take too much time for 
the window of opportunity I’ve been given.  I know I can use multi-threaded 
restore on PG9.6 using the custom or directory formats, but PG9.1 only supports 
single threaded dump.  To get around this I’m going to disable all database 
access to the PG9.1 databases, then use the PG9.6 tools to do a multi-threaded 
dump and then multi-threaded restore.

These are the commands I was using:
pg_dump -vj 4 -F d -h $OLDSERVER  $DATABASE -f $BACKUPPATH/DATABASE 
--no-synchronized-snapshots
created $DATABASE
pg_restore -evj 4 -d $DATABASE  $BACKUPPATH/$DATABASE   --disable-triggers


This is almost exactly what I did when migrating from 8.4 to 9.6.  As Adrian 
Klaver mentioned, you need to dump the globals and then run that script on the 
new database.

No need to disable triggers, since it's "relevant only when performing a 
data-only restore", and you aren't doing a data-only restore.  Besides, 
pg_restore adds all that metadata -- including PKs, FKs, indexes, etc. to the 
db *after* the data is loaded.

--
Angular momentum makes the world go 'round.


This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: Code of Conduct plan

[Tom Lane]

I wrote:
> Stephen Frost  writes:
>> We seem to be a bit past that timeline...  Do we have any update on when
>> this will be moving forward?
>> Or did I miss something?

> Nope, you didn't.  Folks have been on holiday which made it hard to keep
> forward progress going, particularly with respect to selecting the initial
> committee members.  Now that Magnus is back on shore, I hope we can
> wrap it up quickly --- say by the end of August.

I apologize for the glacial slowness with which this has all been moving.
The core team has now agreed to some revisions to the draft CoC based on
the comments in this thread; see

https://wiki.postgresql.org/wiki/Code_of_Conduct

(That's the updated text, but you can use the diff tool on the page
history tab to see the changes from the previous draft.)

I think we are about ready to announce the initial membership of the
CoC committee, as well, but that should be a separate post.

regards, tom lane

constraint exclusion with a tsrange type

[Ben Chobot]

Hey everybody, I'm having trouble getting constraint exclusion to work on a 
table partitioned with a tsrange type. I've distilled it down to this:

create table t (
  id serial primary key,
  observed_window tsrange not null
);
create index t_window on t(observed_window);

create table p1 (like t including all);
alter table p1 add check ( tsrange('2018-1-1','2019-2-1') @> observed_window);
alter table p1 inherit t;
create table p2 (like t including all);
alter table p2 inherit t;
alter table p2 add check ( tsrange('2018-2-1','2019-3-1') @> observed_window);

# explain select * from t where tsrange('2018-1-5','2018-1-6') @> 
observed_window;
  QUERY PLAN
---
 Append  (cost=0.00..51.75 rows=13 width=36)
   ->  Seq Scan on t  (cost=0.00..0.00 rows=1 width=36)
 Filter: ('["2018-01-05 00:00:00","2018-01-06 00:00:00")'::tsrange @> 
observed_window)
   ->  Seq Scan on p1  (cost=0.00..25.88 rows=6 width=36)
 Filter: ('["2018-01-05 00:00:00","2018-01-06 00:00:00")'::tsrange @> 
observed_window)
   ->  Seq Scan on p2  (cost=0.00..25.88 rows=6 width=36)
 Filter: ('["2018-01-05 00:00:00","2018-01-06 00:00:00")'::tsrange @> 
observed_window)
(7 rows)


I would have expected that postgres could reason that, because I'm asking for 
an observed_window that fits within the tsrange (2018-1-5,2018-1-6), no matter 
what it was, it would not be found in p2. Obviously postgres says I'm wrong, 
but I don't know why? 

Constraint exclusion is set to "partition".

Re: PG9.1 migration to PG9.6, dump/restore issues

[Adrian Klaver]

On 9/12/18 11:28 AM, Scot Kreienkamp wrote:




Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  
Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Wednesday, September 12, 2018 10:40 AM
To: Scot Kreienkamp 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: PG9.1 migration to PG9.6, dump/restore issues

Scot Kreienkamp  writes:

Restore completes successfully, but I noticed that the schema
permissions are missing, possibly others as well (Is this a known
issue?).


If you're talking about custom modifications you made to the permissions
of the "public" schema in particular, then yeah, that won't be tracked
(IIRC, it will be with newer source server versions, but not 9.1).
Otherwise, no, that's not expected.  Would you provide more detail?


Yes, it's permissions on the public schema.  They were completely empty.  I 
didn't check the other schemas as the very first thing I noticed was the 
permissions changed on the public schema, but I believe they were empty as well.


In the 9.1 instance, using psql, what does:

\dn+ public

show.

Repeat for other schemas.


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

Re: PG9.1 migration to PG9.6, dump/restore issues

[Adrian Klaver]

On 9/12/18 11:30 AM, Scot Kreienkamp wrote:

Did you see errors in the restore?

In particular about not finding roles(users) for the permissions?

I ask because I do not see in the above anything about dumping objects
global to the cluster. That would include roles. I use:

pg_dumpall -g -f globals.sql

See:

https://www.postgresql.org/docs/10/static/app-pg-dumpall.html



Nope, no errors in the restore that I could see.  As I as discussing with Tom Lane, the -g switch doesn't bring over schema permissions either.  I could use the -s switch and 


Using pg_dumpall -g is not about permissions it is about transferring 
the roles that the permissions depend on. To put it another way roles 
are global to a cluster and permissions are tied to objects in a database.


just re-create the databases as empty again after that's applied.  That 
brings over everything except data.



Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  
Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.




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

Re: constraint exclusion with a tsrange type

[Tom Lane]

Ben Chobot  writes:
> Hey everybody, I'm having trouble getting constraint exclusion to work on a 
> table partitioned with a tsrange type. I've distilled it down to this:
> create table t (
>   id serial primary key,
>   observed_window tsrange not null
> );
> create index t_window on t(observed_window);

> create table p1 (like t including all);
> alter table p1 add check ( tsrange('2018-1-1','2019-2-1') @> observed_window);
> alter table p1 inherit t;
> create table p2 (like t including all);
> alter table p2 inherit t;
> alter table p2 add check ( tsrange('2018-2-1','2019-3-1') @> observed_window);

> # explain select * from t where tsrange('2018-1-5','2018-1-6') @> 
> observed_window;

Nope, sorry, there's no logic in there about ranges.  You'd have to
break this down into something involving simple timestamp comparison
operators for constraint exclusion to be able to prove anything.

Might be a reasonable future extension, perhaps...

regards, tom lane

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Wed, Sep 12, 2018 at 07:24:24AM +0900, Michael Paquier wrote:
> Good point.  Such things have bitten in the past.  Okay, then let's do
> something about sha2_openssl.c only on HEAD for now then, which I am
> fine to finish wrapping.

I was looking at trying to commit this patch, however more needs to be
done in terms of error handling, as the proposed patch would happily
crash if EVP_MD_CTX cannot be allocated (understand OOM) in
EVP_DigestInit_ex if I read the OpenSSL code correctly (see
crypto/evp/digest.c).  Our lives would be facilitated if it was possible
to use directly EVP_MD_CTX and EVP_MD_CTX_init so as no allocation is
done but that's not doable as of 1.0.2.
--
Michael


signature.asc
Description: PGP signature